30.05.2014 06:11, s3057043@... wrote:

> CREATE INDEX IX_TA BLE2_EXPECTED ON TABLE2 (TABLE1ID, VALIDFROM, VALIDTO);
> COMMIT;
>
> -- SIMPLE QUERY
>
> SELECT *
> FROM TABLE2 T
> WHERE T.TABLE1ID BETWEEN 1000 AND 1999
> AND T.VALIDFROM <= CURRENT_DATE
> AND T.VALIDTO >= CURRENT_DATE
>
> The simple query uses the following PLAN causing 3000 indexed reads
> (according to both monitoring tables and DBWorkbench)
> PLAN (T INDEX (FK_TABLE2_TABLE1))
>
> Why would it not use the following plan?
> PLAN (T INDEX (IX_TABLE2_EXPECTED))
>
> I would have expected 1000 indexed reads
>
> Even if I force this plan it still performs 3000 reads indicating that
> it doesn't take advantage of the ValidFrom and ValidTo information in
> the index.
>
> Am I misunderstanding something?

For any compound index {A, B, C}, segments B and C can be used only if A
is compared for equality, and C can be used only if both A and B are
compared for equality.

If your predicate would look like:

WHERE T.TABLE1ID = 1000
AND T.VALIDFROM <= CURRENT_DATE
AND T.VALIDTO >= CURRENT_DATE

then segments {TABLE1ID, VALIDFROM} could be used. If the predicate
would look like this:

WHERE T.TABLE1ID = 1000
AND T.VALIDFROM = CURRENT_DATE
AND T.VALIDTO >= CURRENT_DATE

then all three segments could be used. But in your query only the first
segment can be used and in this case it's cheaper to scan a smaller
single-segment index rather than a fat three-segment one.


Dmitry