>> This is the point I see really funny as my guess of the Oracle approach >> is >> as follows: >> >> 1) Oracle knows that the index scan may miss some records due to NULLs >> 2) Oracle scans BOTH indexes to avoid this and joins the result >> >> 3) This approach is fine but doesn't work if one of indexes is empty >> (something like full outer join will be required in this case) >What do you mean by empty? There isn't rows at all on it? Then your >theory isn't right because both columns RADP_RRPR_ID and RADP_RRPR_ID1 >has at least some rows with not null values.
A better formulation of 3) would be:
3) This approach is fine but doesn't work if at least on of the indexes is on a nullable column.
> |* 4 | HASH JOIN | | 1 | 14 | > | > | 5 | INDEX FAST FULL SCAN| IX_RADP_RRPR_ID | 1 | 14 | > 4 | > | 6 | INDEX FAST FULL SCAN| IX_RADP_RRPR_ID1 | 1 | 14 | > 4 |
The (inner) hash joins process only records that are scanned in both row sources. I.e. rows with NULL in at least one of the indexed columns are not processed.
> |* 3 | VIEW | index$_join$_002 | 1 | 14 | > 4 |
It seams that the cause of your problem is a wrong dealing with index on nullable column in an index join. The are some bugs on metalink on this topic, the workaround is particularly set _INDEX_JOIN_ENABLED to false.
A other interesting point is why the CBO prefers index fast full scan over index range scan. I don't know the formula for cost estimation for index FFS but I can imagine that the reason is similar to the classical "why is my index not used?" problem:
* wrong estimation of the selectivity of the column and/or * high db_file_multiblock_read_count
Regards,
Jaromir
-- -- Original Message -- -- From: "Gints Plivna" <gints.plivna@(protected)> To: "jaromir nemec" <jaromir@(protected)> Cc: <jkstill@(protected)>; "oracle-l" <oracle-l@(protected)> Sent: Friday, July 14, 2006 5:52 PM Subject: Re: Smells like oracle bug?
