Re: Smells like oracle bug?

Hi Gints,

>> 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_at_gmail.com> To: "jaromir nemec" <jaromir_at_db-nemec.com> Cc: <jkstill_at_gmail.com>; "oracle-l" <oracle-l_at_freelists.org> Sent: Friday, July 14, 2006 5:52 PM Subject: Re: Smells like oracle bug?

--
http://www.freelists.org/webpage/oracle-l