CODEWITHSUNDEEP
sqloracle11g
zhuguowei
zhuguowei

Reputation: 8497

Why used index but the sql is still slow

There is a table ORG_HLD_INFO and there is an index of it: "ORG_HLD_INFO" ("HLD_UNI_CODE", "ISVALID", "ORG_UNI_CODE"). Now execute below query is slow, need 3.26 seconds(and all rows fetched is 466)

select HLD_UNI_CODE ,ORG_UNI_CODE
from ORG_HLD_INFO
where ISVALID=1 and HLD_UNI_CODE in (30004536568,...)

why is so slow? it should use the index, and all the fields of select in the index so no need to query the table row.

p.s. the total count of the table is: 109102083 and below is the explain plan

enter image description here

Upvotes: 1

Views: 46

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

You said in comments that your IN clause may have around 100 values.

In this case an index in usually useless.

Let's have a look at a simplified version of your query

select HLD_UNI_CODE ,ORG_UNI_CODE
from ORG_HLD_INFO
where ISVALID=1 and HLD_UNI_CODE in (3, 4, 5)

This is equivalent to 

select HLD_UNI_CODE ,ORG_UNI_CODE
from ORG_HLD_INFO
where ISVALID=1 and 
    (HLD_UNI_CODE = 3 OR HLD_UNI_CODE = 4 OR HLD_UNI_CODE = 5)

This is equivalent to 

select HLD_UNI_CODE ,ORG_UNI_CODE
from ORG_HLD_INFO
where ISVALID=1 and HLD_UNI_CODE = 3

UNION ALL

select HLD_UNI_CODE ,ORG_UNI_CODE
from ORG_HLD_INFO
where ISVALID=1 and HLD_UNI_CODE = 4

UNION ALL

select HLD_UNI_CODE ,ORG_UNI_CODE
from ORG_HLD_INFO
where ISVALID=1 and HLD_UNI_CODE = 5

Here we can use UNION ALL, because we include HLD_UNI_CODE in the selected columns. If it wasn't we may need to use UNION.

Anyway, the point is that each of the individual simple queries like 

select HLD_UNI_CODE ,ORG_UNI_CODE
from ORG_HLD_INFO
where ISVALID=1 and HLD_UNI_CODE = 5

can use the index. Use, in the sense that engine will seek in the index to find only the needed rows instead of scanning all rows.

In some databases an optimizer may be smart enough to rewrite the IN/ OR query into an UNION of simpler index seeks (if there are only few values in the IN). I don't know if Oracle's optimizer can do this kind of transformation at all.

But, when you have hundreds of such simpler queries it quickly becomes too expensive to do all of these seeks and then put them together, so optimizer opts into scanning the whole table; and scanning the table (or the index) still means reading all 109102083 rows and applying the filter to each row.

You can see in the plan RANGE SCAN, not seek, which essentially means reading all rows. And you can see Predicate (filter) with a bunch of ORs.

You can try to rewrite your query into hundred unions and check if it runs faster, but the query may become too complicated to parse. So, even if you try to do this transformation manually it may not be feasible, unless you find some trick, like run hundred of simple queries in a loop and dump intermediate results into a temp table.

Upvotes: 1

Related Questions