Single Table Hash Clusters

Mar 16, 2013

I created a single table hash cluster like this :

create tablespace mssm datafile 'c:appmssm01.dbf' size 100m
segment space management manual;

create cluster hash_cluster_4k
( id number(2) )
size 8192 single table hash is id hashkeys 4 tablespace mssm;

-- Created a table in cluster with row size such that only one record fits one block and inserted 5 records each with a distinct key value

CREATE TABLE hash_cluster_tab_8k
( id number(2) ,
txt1 char(2000),
txt2 char(2000),
txt3 char(2000)
)
CLUSTER hash_cluster_8k( id );
[code]....

If I issue the same query after creating unique index on hash_cluster_tab(id), the execution plan shows hash access and single I/O (cr = 1).Does it mean that to have single I/o in a single table hash cluster, we have to create unique index? Won't it create additional overhead of maintaining an index?

What is the second I/O needed for in case unique index is absent?

View 25 Replies


ADVERTISEMENT

Real Application Clusters :: One Node Or Clusterware Single Instance Failover?

Aug 21, 2012

I am trying to design a database consolidation high-availability cluster for Oracle 11g R2 64-bit Enterprise Edition (X86-64) on Oracle Linux 6.x UEK, using Oracle 11.2.0.3 (the latest as of Aug 2012).

We don't need RAC Multi-Node now or in foreseeable future because none of the databases we run break the capacity of a single node. Likewise, we don't need to use Oracle VM to virtualise the database instances.

We plan to use SGA and PGA memory management to run multiple instances on same hardware operating on a single Linux 64-bit O/S image.Does it sound ok so far?

Two or three of 4-socket, 40-core Intel 64-bit servers with 512GB of RAM each (relatively cheap at today's HW commodity prices) will be sufficient to run all Oracle databases we have on Linux 64-bit.So the two HA options that I know of are:

(1) use Oracle Clusterware/Grid/ASM to provide for instance failover
(2) use Oracle RAC One Node on top of Clusterware/Grid/ASM

As I understand it RAC One Node is significantly more expensive than the "free" Oracle Clusterware/ASM/Grid (since we own Oracle 11.2.0.3 Enterprise Licences already). So why should my employer pay for RAC One Node licence given they already own Single Instance Fail-Over and Restart protection from Clusterware/Grid/ASM ?

I also read that Data Guard 11.2 may not be supported with RAC One Node on 11.2? True? Will same Data Guard 11.2 work with a Single-Instance Failover running on Clusterware/Grid/ASM ?

-Who is running RAC One Node? Why?
-Who is running Single Instance Failover with Clusterware? Why?
-Who is using Data Guard with either of the above?

View 4 Replies View Related

Real Application Clusters :: Convert 10g Single Instance (uses ASM) Database To RAC - Reconfigure Fails?

Oct 5, 2012

I have 11gR2 GI installed on two nodes. I am trying to convert a 10g single instance (uses ASM) database to RAC and getting this error. I am trying using 12c as well as manually by using rconfig.

[main] [21:19:15:145] [ASMInstance.initialize:135] First record =[Ljava.lang.String;@958bb8
java.lang.NullPointerException
at oracle.sysman.assistants.rconfig.engine.ASMInstance.initialize(ASMInstance.java:153)
at oracle.sysman.assistants.rconfig.engine.Step.execute(Step.java:283)

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Hash Partition Table

Jun 27, 2010

I am facing a problem in fetching / updating records from a customer details table having around 20 million records. The table contains around 30 fields with 'MOBILE_NO' as primary key. most of the queries are having 'mobile_no' in where clause .I am planning to hash partition that table using mobile_no column as there is no other column available which can be used for partition.

clarify whether creating hash partition on such key would increase performance of data extraction as I have read on net that hash partitioning is not effective for performance tuning.

View 5 Replies View Related

SQL & PL/SQL :: Hash Partition On Fact Table

Sep 27, 2010

I created the 32 hash partition on a fact table. Based on hash parititon technique it should evenly distribute data accross the different partition.But when i analyze the table and check the distribution its not at all even.

P_Name Tabspace num_Rows
SYS_P21TBS_TBS0
SYS_P22TBS_TBS0
SYS_P23TBS_TBS0

[Code]....

View 5 Replies View Related

Use Range-hash Partitioning Of A Large Dimension Table

Apr 12, 2013

At moment we use range-hash partitioning of a large dimension table (dimension model warehouse) table with 2 levels - range partitioned on columns only available at bottom level of hierarchy - date and issue_id.

Result is a partition with null value - assume would get a null partition in large fact table if was partitioned with reference to the large dimension.Large fact table similarly partitioned date range-hash local bitmap indexes

Suggested to use would get automatic partition-wise joins if used reference partitioningWould have thought would get that with range-hash on both dimension.

View 3 Replies View Related

Server Administration :: Partition Exchange In Hash Partitioned Table

Mar 13, 2012

While trying partition exchange feature of Oracle with 2 hash partitioned tables, I come to know that I can't directly exchange partitions between 2 partitioned tables

I have two hash partitioned tables , so to move partition data from one table to another will include-

1) Exchange from partitioned table to non-partitioned table.
2) exchange from non-partitioned table to new partitioned table.

But I am not sure in which hash partition my data will go in new partitioned table (data need to be moved has single key value on basis of which tables are partitioned),

View 2 Replies View Related

SQL & PL/SQL :: Add Range Sub-Partition To Hash Partition Table

Jan 2, 2013

Can I add range sub partition to a hash partition table. Example like this.

CREATE TABLE test
(
test_id VARCHAR2(10 ) ,
test_TYPE VARCHAR2(5) ,
CREATE_DATE date
)
partition by hash (test_id, test_type)
Partitions 3
SUBPARTITION BY RANGE (CREATE_DATE);

When Tried, I am getting syntax error as invalid option.

View 8 Replies View Related

Convert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?

Sep 10, 2012

RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?

If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?

View 7 Replies View Related

Hash Value Generation

Aug 21, 2012

I have a question.

When and Where HASH VALUE for a given SQL statement get generated?

View 5 Replies View Related

SQL & PL/SQL :: How Hash Is Computed

May 22, 2013

Is it true that the SQL hash is computed on the SQL text only ?

View 4 Replies View Related

SQL & PL/SQL :: What Is Hash Join

Jul 12, 2012

what is Hash join?how it is different from inner join?what is the sign used for inner join?(eg: like the (+) sign used for outer join)?

View 1 Replies View Related

PL/SQL :: How To Use Hash Join

Jan 13, 2013

If i have a table T1 and a table T2. Table T1 is having 100 rows and table T2 is having 20 rows. When performing a Hash join ,which table should be used to make the hash table ,the larger one or the smaller one and why ?IF the data set is too small for considerations then please conser table T1 with 10 million of rows and table T2 with 1 million of rows.

View 11 Replies View Related

Performance Tuning :: Different SQL ID For Same HASH-VALUE?

Nov 2, 2012

I see one of my SQL's which is ran by the user on a 10.2.0.3 database changing its SQL_ID after some runs even if the query is not changed a bit! However the HASH VALUE for this query remains the same.

how a same query can have different SQL_ID's but same HASH_VALUE?

Note: Statistics are not modified on the base tables of this query.

View 10 Replies View Related

Hash Group By Improvement?

Aug 7, 2013

I see that one of my queries from an application time is spending most of its time in the hash group by. I'm running Oracle 11g with a quarter rack exadata appliance. Is there a better way to run or design this table? query:

SELECT COUNT(*)
FROM (
        SELECT "DDTMDAY", "MRKTNM", "BSMNM", "BSCNM", "CLNM", "CSCDNM", "BTSID", "SECTSEQID", "BNDID", "FAID", SUM("VATTCNT"), SUM("VMBLORGCNT"), SUM("VMBLTERCNT"), SUM("VSILENTRETRYCNT"), SUM("VCUSTBLKCNT"), SUM("VAXSFCNT"), SUM("VCEBLKCNT"), SUM("VWCDBLKCNT"), SUM("VT1BHLBLKCNT"), SUM("VPWRBLKCNT"), SUM("VNONBTSEQBLKCNT"), SUM("VSFULCALLCNT"), SUM("VDRPCALLCNT"),

[code]...

View 13 Replies View Related

SQL & PL/SQL :: New Values To Come In A Single Row Of Table

Oct 12, 2010

Below is my trigger:

CREATE OR REPLACE
TRIGGER AR_BANDS_TEST
BEFORE INSERT OR DELETE OR UPDATE ON FEE_BANDS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
[code]...

I want the old and new values to come in a single row of the table while inserting in trigger like below:

OLD_FEE_RU OLD_FEE_BANDS_ID OLD_RATE_NUMBER OLD_RATE_PERCENT NEW_FEE_RU NEW_FEE_BANDS_ID NEW_RATE_NU
---------- ---------------- --------------- ---------------- ---------- ---------------- -----------
TESTALL 1 0 1 TESTALL 1 0

View 7 Replies View Related

SQL & PL/SQL :: Max Columns In Single Table

Jan 24, 2011

Is there any limitations for the number of column defined inside the table?

View 3 Replies View Related

Combining Hash Cluster And Indexes

Oct 28, 2010

A basic select and group by query I am optimising for my Database course has returned results that indicate it will perform better on a clustered index when returning a smaller number of rows (5% of the largest table) and on a hash clustered index when returning higher volumes (50% and 80%). I understand that it is possible to use more than one index type on a table to improve performance, but I am struggling to understand how I might establish a hash cluster and a cluster on the same table? and then use hints to drive the query down one access path or the other.

Site admin - this site is a very useful resource.

View 5 Replies View Related

SQL & PL/SQL :: Hash Join And Nested Loops

Mar 16, 2011

What is the difference between Hash join and Nested Loops in pl / sql?

View 1 Replies View Related

SQL & PL/SQL :: What Is Hash Join And Sort Merge

Jun 12, 2012

I tried to search on google for "Hash Join" And "Sort Merge". But unfortunatly i am unable to understand that articles. "Hash join" And "Sort Merge".

View 3 Replies View Related

Hash Partition On Primary Key Columns?

Jul 18, 2012

I have a existing non partition table with more than 100 million records,planning to re design using Hash partition.This table doesn't has any range column to do range partitioning.

Table has 40 columns with a Primary Key on two columns (guest_sales_Id ,Version Flag). guest_sales_Id is unique for entire table but with anopther column version Flag declared as Primary key.(Version Falg will have only two distinct values in entire table)

If i do hash partition,do i need to declare on two columns which are declared ad Primary key ?If i use only guest_sales_id to declare hash prtition any issues ?

View 2 Replies View Related

Loading Single Column Into Table?

Nov 16, 2012

I am trying to load a single column into a Table. Below is the ctl file:

OPTIONS (ERRORS=10000)
LOAD DATA
TRUNCATE
INTO TABLE "ECOMM"."CC_ECOMM"
TRAILING NULLCOLS
(EMAIL_ID)

Calling loader:

sqlldr silent=ALL userid=username/password@DB control=/home/DEV/control/CC_ECOMM.CTL log=/home/DEV/log/Unsubs.log bad=/home/DEV/log/Unsubs.bad discard=/home/DEV/log/Unsubs.dis data=/home/DEV/files/CC_ECOMM.TXT

Though the count in the Table after load is the same as the input file, they are all NULL.

I tried adding FIELDS TERMINATED BY X'A' for new line and also FIELDS TERMINATED BY X'D' for carriage return. Both times bad file was created and the records that were loaded were again NULL.

The input file has a list of emails:
iatraveler2008@aol.com
iaz65@aol.com
2blue2brown@comcast.net
2c3mwilson@embarqmail.com
abigailolschan@comcast.net
imisskoco@aol.com

I tried FIELDS TERMINATED BY X'10' and FIELDS TERMINATED BY X'13' too for new line and carriage return respectively. This time there were no bad file created, but the Table has Null values.

Log File contents:

Number to load: ALL
Number to skip: 0
Errors allowed: 10000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK, ERRORS and DISCARDS

[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Create Single Table Cluster

Mar 6, 2010

I have one table called posh, which has about 50,000 ids (millions of rows) so far. It is currently at about 50 gb. So each series of rows for a particular id is about 1 mb.

CREATE CLUSTER posh_cluster (id int)
SIZE 2M
STORAGE (initial 2000K next 2000K)
PARALLEL 4;
CREATE INDEX posh_cluster_index ON CLUSTER posh_cluster;

[code].......

According to the Oracle documentation size is "the average cluster key size", I am not really sure what that means. Is this the 1mb in posh table space I am using for each series of rows for an id, or the space used for the index? Secondly, the storage parameter for 'CREATE CLUSTER' should be what?

View 11 Replies View Related

SQL & PL/SQL :: Copy Single Column From One Table To Another?

May 27, 2010

How can i copy a single column from one table to another. Table 1 has a column with data in it, table 2 has the column but is empty, i want to copy data for a single column from table1 to table 2. By the way these table have multiple columns.

View 8 Replies View Related

SQL & PL/SQL :: To Insert 1 Single Row In A Table And Then Exit From The Same

Apr 30, 2010

I am running 3 for loops in continuation. These loops retrieve me 63 values. I need to insert 1 single row in a table and then exit from the same. Moreover when i run the same loop again I need to insert 2nd value fo the second row in the table. The table is having a unique key which does not allow me to load redundant data.

View 2 Replies View Related

SQL & PL/SQL :: How To Convert A Single Ragged Table

Dec 11, 2011

how to convert a single 'ragged' table like:

MAIN_KEY SUB_KEY DATA
ABC 1 A
ABC 2 B
ABC 3 C
DEF 1 X

where:

MAIN_KEY and DATA are indeterminate, and
SUB_KEY = 1 .. n [always starts with 1 for a given MAIN_KEYD]

into:

MAIN_KEY SUB_KEY DATA
ABC 1 A
ABC 2 B
ABC 3 C
ABC 4 {null}
ABC 5 {null}
DEF 1 X
DEF 2 {null}
DEF 3 {null}
DEF 4 {null}
DEF 5 {null}

in the case where n = 5?

View 6 Replies View Related

SQL & PL/SQL :: Update Single Column In X Table

Sep 2, 2011

i need a query for update..the logic is

i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.

View 2 Replies View Related

Create A Table Without Any Single Column In It?

Nov 15, 2012

Is it possible to create a table without any single column in it?

View 1 Replies View Related

PL/SQL :: Merge Statement On Single Table

Oct 29, 2012

{code{

i want to apply merge stmt on single table.

CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');

[Code]....

My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record

View 3 Replies View Related

Security :: How To Decrypt DBMS_CRYPTO.HASH Encrypted Value

Mar 27, 2013

note we need to decrypt the data which is encrypted using dbms_crypto.hash algorithm. it is possible to decrypt dbms_crypt.hash

eg

SQL> select DBMS_CRYPTO.hash(utl_raw.cast_to_raw('Foo'), 3) FROM dual;
DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('FOO'),3)
--------------------------------------------------------------------------------
201A6B3053CC1422D2C3670B62616221D2290929

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved