1

I have query similar to this:

select *
from table1
where status = 'ACTV'
and child_id <> parent_id

The problem is that this table is quite and large and Oracle is doing full table scan. I was trying to create an index (with status, child_id, parent_id columns) that would speed up this query but Oracle is not using this index even with hint.

Is there a way to speed up this query ?

Paweł P
  • 33
  • 3

2 Answers2

1

You can use index with function:

CREATE INDEX child_parent ON table1(DECODE(child_id,parent_id,1, 0))

And then use it in your select:

select *
from table1
where status = 'ACTV'
  and DECODE(child_id,parent_id,1, 0) = 0

Only cons for this solution - it will slow down insert and update operations a bit more than regular index. Also if potentially returnable record count is large Oracle can do table full scan

Janis Baiza
  • 951
  • 6
  • 15
  • Thank You for information. This table has already few indexes and using DECODE allows to do an INDEX SKIP SCAN with DECODE and status column as data filters. – Paweł P Apr 24 '15 at 13:52
0

In parent, child table : "child_id <> parent_id" is obvious right , it will always fetch 99% of data then full table scan is better approach. Index will be slower if you selecting more percentage of data.

if your application needs "child_id <> parent_id" always then you can create check constrain for the same. Then you may not need this where condition "child_id <> parent_id" any time.

Ramki
  • 453
  • 2
  • 7