CODEWITHSUNDEEP
oracle-databaseselectoptimization
user2639553
user2639553

Reputation: 13

Oracle simple Select query optimization

I have below simple dynamic select query 

Select RELATIONSHIP 
  from DIME_MASTER 
 WHERE CIN=? AND SSN=? AND ACCOUNT_NUMBER=?

The table has 1,083,701 records. This query takes 11 to 12 secs to execute which is expensive. DIME_MASTER table has ACCOUNT, CARD_NUMBER INDEXES. Please help me to optimize this query so that query execution time is under fraction of second.

Upvotes: 1

Views: 1600

Answers (2)

krokodilko
krokodilko

Reputation: 36127

Look at the predicate information:

--------------------------------------
 1 - filter(TO_NUMBER("DIME_MASTER"."SSN")=226550956 
            AND TO_NUMBER("DIME_MASTER"."ACCOUNT_NUMBER")=4425050005218650 
            AND TO_NUMBER("DIME_MASTER"."CIN")=00335093464)

The type of your columns is NVARCHAR, but parameters in the query are NUMBERs.
Oracle must cast numbers to strings, but it is sometimes not very smart in casting.
Oracles and fortune-tellers are not always right ;)

These casts prevents the query from using indices.

Rewrite the query using explicit conversion into:

Select RELATIONSHIP 
  from DIME_MASTER 
 WHERE CIN=to_char(?) AND SSN=to_char(?) AND ACCOUNT_NUMBER=to_char(?)

then run this command:

exec dbms_stats.gather_table_stats( user, 'DIME_MASTER' );

and run the query and show us a new explain plan.

Would you please do not paste explain plans here, they are unreadable,
please use pastebin instead, and paste only links here, thank you.

Look at this simple example, it shows why you need explicit casts:

CREATE TABLE "DIME_MASTER" ( 
  "ACCOUNT_NUMBER" NVARCHAR2(16)
);
insert into dime_master
select round( dbms_random.value( 1, 100000 )) from dual
connect by level <= 100000;
commit;
create index dime_master_acc_ix on dime_master( account_number );

explain plan for select * from dime_master
where account_number = 123;

select * from table( dbms_xplan.display );

Plan hash value: 1551952897

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    54 |    70   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DIME_MASTER |     3 |    54 |    70   (3)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("ACCOUNT_NUMBER")=123)




explain plan for select * from dime_master
where account_number = to_char( 123 );

select * from table( dbms_xplan.display );
Plan hash value: 3367829596

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     3 |    54 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| DIME_MASTER_ACC_IX |     3 |    54 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ACCOUNT_NUMBER"=U'123')

Upvotes: 1

haki
haki

Reputation: 9779

Depending on the cardinality of the columns (Total rows / unique values ) - you can create bitmap indexes on each column. Bitmap indexes are very usefull for and / or operations.

Rule of thumb says that a bitmap index is useful for cardinality of more then 10%.

create bitmap index DIME_MASTER_CIN_BIX on DIME_MASTER (CIN);

Upvotes: 0

Related Questions