how to use Primary index efficiently


how to use Primary index efficiently


Here i have a situation that

In my table PI is defined on combination of 3 columns Col A, Col B, Col C, but in my query we are using where condition on only one column Col A, Here the optimizer is not taking the advantage of PI, bz the PI is not matching, and my query is taking more time to execute, plz give me some tuning tips..

one more question, if i want to use combination of 3 columns (Col A,Col B, Col C) in my where condition what is the syntax


Re: how to use Primary index efficiently

Is PI is unique or Non-unique ? If it is non-unique you can define Partition PI on col a, depending on the data values in col a and some other considerations

Re: how to use Primary index efficiently

hi satish,

You can add one more columns in your WHERE clause in this fashion.

SELECT * FROM TABLE1 WHERE (COL1,COL2,COL3..etc) in (select distinct (COLX1, COLX2, COLX3...etc) from table2)

SELECT * FROM TABLE1 WHERE (COL1,COL2,COL3..etc) = (val1, val2, val3...)

3. SELECT * FROM TABLE1 where COL1=val1 and COL2 = val2 and COL3 = val3 etc...

here queries 2 and 3 are equivalent.

Re: how to use Primary index efficiently


Your query will not the advantage of UPI because the hash will be different for col1 and the combination of col1,col2 and col3.
So if you want to take that advantage you have to use col2 and col3 in where clause.

Or you can try and check below suggestions :

1) Define PPI on col1 if that contained any range based data
2) collect stats on col1

Also if can poast some sample data of col1, col2 and col3 then further can be suggest.
Also post the row count and time taken by queries.