CODEWITHSUNDEEP
performanceoracle-databaseindexing
Vijay Nalawade
Vijay Nalawade

Reputation: 71

Oracle index creation strategies

Can any body help to to solve following performance issue.

select 1
from table_1 a
     ,table_2 b
     ,table_3 c
where c.trx_date between sysdate-12 and sysdate-12
and  c.trx_id=b.trx_id
and  c.type_id=a.type_id;

Here I need to create index on table_3 on column trx_date because this is main filter to sql query.

Now my question is does index requires other column which is used on join query with other table.

for above query does i need to create index with column trx_date ,trx_id,type_id

or index on only trx_date will solve performance issue?

Regards, Vijay Nalawade

Upvotes: 0

Views: 39

Answers (1)

neshkeev
neshkeev

Reputation: 6486

You definitely have to create indexes on the columns you use for foreign keys, it helps you to avoid deadlocks.

I have nothing to say about trx_date because I don't know about your table structure and table contents. It make sense to create indexes if you want to extract 0-20% of your rows (the exact percent number depends on the record volume) in other cases oracle will perform full scan. The optimizer will evaluate how many blocks it needs to read from the filesystem into the cache and choose the way with the least number, If during full scan it reads fewer blocks oracle will do full scan

Upvotes: 2

Related Questions