Re: Smells like oracle bug?
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <gints.plivna@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 16 Jul 2006 00:01:56 +0200
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@xxxxxxxxx>
To: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
Cc: <jkstill@xxxxxxxxx>; "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, July 14, 2006 5:52 PM
Subject: Re: Smells like oracle bug?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
