CODEWITHSUNDEEP
oracle-databaseindexingfull-table-scan
Paweł P
Paweł P

Reputation: 33

Using index to speed up child <> parent query

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 ?

Upvotes: 1

Views: 91

Answers (2)

Ramki
Ramki

Reputation: 463

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.

Upvotes: 0

Janis Baiza
Janis Baiza

Reputation: 961

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

Upvotes: 1

Related Questions