| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Smells like oracle bug?
> >> SQL> select 'x' from dual where exists (
> >> 2 select /*+ index_ffs (blahh idx1 idx2) */ 'z' from blahh where
> >> 3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1))
> >> 4 /
> Yes, in this case it is an evident bug, but the support will probably answer
> (as Jared and other noted): workaround - remove the hint.
>
> But the original statement differs from your test case:
Yes I know it, the plan differs, the only remaining thing is - the
result is incorrect ;)
The problem with my created testcase with table blahh is that I don't
know how to enforce index fast full scan on both indexes and then do
hash join on the results. As you can see I'v tried with index_ffs hint
and the result was buggy and I'v got index full scan only on one
index, which to my mind is complete insanity because both columns are
defined as null, and at least both indexes should be checked.
> >Execution Plan
>
> >----------------------------------------------------------
>
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82)
> > 1 0 FILTER
> > 2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
> > 3 1 VIEW OF 'index$_join$_002' (Cost=4 Card=1 Bytes=14)
> > 4 3 HASH JOIN
> > 5 4 INDEX (FAST FULL SCAN) OF 'IX_RADP_RRPR_ID' (NON-UNI
> > QUE) (Cost=4 Card=1 Bytes=14)
> > 6 4 INDEX (FAST FULL SCAN) OF 'IX_RADP_RRPR_ID1' (NON-UN
> > IQUE) (Cost=4 Card=1 Bytes=14)
>
> 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.
QL> select count(*) from <tablename> where RADP_RRPR_ID IS NOT NULL;
COUNT(*)
11
QL> select count(*) from <tablename> where RADP_RRPR_ID1 IS NOT NULL;
COUNT(*)
7
> Please provide the complete explain plan (inclusive the access and filter
> predicates) to check this theory.
>
OK here is dbms_xplan result
SQL> explain plan for
2 select 'x' from dual where exists (
3 SELECT 'z'
4 FROM IRA_ADOPCIJAS
5 WHERE (Radp_Rrpr_Id = 10000003254 AND Radp_Rrpr_Id1 IS NULL) OR
6 (Radp_Rrpr_Id IS NULL AND Radp_Rrpr_Id1 = 10000003254))
7 /
Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
| 0 | SELECT STATEMENT | | 82 | | 2 | |* 1 | FILTER | | | | | | 2 | TABLE ACCESS FULL | DUAL | 82 | | 2 | |* 3 | VIEW | index$_join$_002 | 1 | 14 | 4 | |* 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 | -----------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter( EXISTS (SELECT /*+ */ 0 FROM (SELECT /*+ */
"indexjoin$_alias$_004"."RADP_RRPR_ID1"
"RADP_RRPR_ID1","indexjoin$_alias$_004"."RADP_RRPR_ID1"
"RADP_RRPR_ID1","indexjoin$_alias$_003"."RADP_RRPR_ID"
"RADP_RRPR_ID","indexjoin$_alias$_003"."RADP_RRPR_ID"
"RADP_RRPR_ID1","indexjoin$_alias$_003".ROWID "rowid" FROM
"CMIS_DB"."IRA_ADOPCIJAS"
"indexjoin$_alias$_004","CMIS_DB"."IRA_ADOPCIJAS"
"indexjoin$_alias$_003" WHERE
"indexjoin$_alias$_004".ROWID="indexjoin$_alias
$_003".ROWID) "IRA_ADOPCIJAS" WHERE
"IRA_ADOPCIJAS"."RADP_RRPR_ID"=1000000325
4 AND "IRA_ADOPCIJAS"."RADP_RRPR_ID1" IS NULL OR
"IRA_ADOPCIJAS"."RADP_RRPR_ID" IS NULL AND
"IRA_ADOPCIJAS"."RADP_RRPR_ID1"=10000003254))
3 - filter("IRA_ADOPCIJAS"."RADP_RRPR_ID"=10000003254 AND
"IRA_ADOPCIJAS"."RADP_RRPR_ID1" IS NULL OR
"IRA_ADOPCIJAS"."RADP_RRPR_ID" IS
NULL AND "IRA_ADOPCIJAS"."RADP_RRPR_ID1"=10000003254)
4 - access("indexjoin$_alias$_004".ROWID="indexjoin$_alias$_003".ROWID)
Note: cpu costing is off
34 rows selected.
BUT the same info from v$sql_plan at least differs in predicate info:
1 select
2 substr(id||LPAD(' ',2*DEPTH)||Operation||' '||Options||' '||
object_Name, 1, 47) X_Plan,
3 cardinality,io_cost,cost,optimizer--, access_predicates,
filter_predicates
4 from v$sql_plan
5* where hash_value = 90297928
SQL> /
X_PLAN CARDINALITY IO_COST
COST OPTIMIZER
----------------------------------------------- ----------- ----------
---------- -------------
2 TABLE ACCESS FULL DUAL 82 2
2
3 VIEW 1
4
4 HASH JOIN 1
5 INDEX FAST FULL SCAN IX_RADP_RRPR_ID 1 5
5 ANALYZED
6 INDEX FAST FULL SCAN IX_RADP_RRPR_ID1 1 5
5 ANALYZED
1 select id, 'AP: ' || access_predicates, 'FP: ' || filter_predicates
2 from v$sql_plan where hash_value = 90297928
3* and (access_predicates is not null or filter_predicates is not null)
SQL>
SQL> /
ID
1
AP:
FP: IS NOT NULL
3
AP:
FP: "RADP_RRPR_ID"=10000003254 AND "RADP_RRPR_ID1" IS NULL OR "RADP_RRPR_ID" IS
NULL AND "RADP_RRPR_ID1"=10000003254
4
AP: ROWID=ROWID
FP:
> Some additional remarks:
>
> What are the costs of an index range scan with predicate id1 = 10000003254?
>
> I assume it is one of the two possibilities:
>
> A) either more then 4 or
> B) there is some setting that prevent the bitmap conversion
Hmmm which one could prevent bitmap conversions? I'm not sure, because
at least on the same db but with different user I can get them, see
more below.
SQL> select 'x' from dual where exists (
2 SELECT /*+ index (IRA_ADOPCIJAS IX_RADP_RRPR_ID IX_RADP_RRPR_ID1) */'z'
3 FROM IRA_ADOPCIJAS
4 WHERE (Radp_Rrpr_Id = 10000003254 AND Radp_Rrpr_Id1 IS NULL) OR
5 (Radp_Rrpr_Id IS NULL AND Radp_Rrpr_Id1 = 10000003254))
6 /
'
-
x
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82) 1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
3 1 CONCATENATION
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'IRA_ADOPCIJAS' (Cost
=2 Card=1 Bytes=14)
5 4 INDEX (RANGE SCAN) OF 'IX_RADP_RRPR_ID' (NON-UNIQUE)
(Cost=1 Card=1)
6 3 TABLE ACCESS (BY INDEX ROWID) OF 'IRA_ADOPCIJAS' (Cost
=2 Card=1 Bytes=14)
7 6 INDEX (RANGE SCAN) OF 'IX_RADP_RRPR_ID' (NON-UNIQUE)
(Cost=1 Card=1)
> This is the plan I receive in 9.2.0.5.0:
Now I'v found even more interesting behaviour with my test table
blahh. Just after creation and first row insert but without any
analyze I'v got:
SQL> select 'x' from dual where exists (
2 select 'z' from blahh where
3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1))
4 /
'
-
x
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' 3 1 CONCATENATION 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'BLAHH' 5 4 INDEX (RANGE SCAN) OF 'IDX2' (NON-UNIQUE) 6 3 TABLE ACCESS (BY INDEX ROWID) OF 'BLAHH' 7 6 INDEX (RANGE SCAN) OF 'IDX1' (NON-UNIQUE)
With index_ffs but without any analyze I'v got:
SQL> ed
Wrote file afiedt.buf
1 select 'x' from dual where exists (
2 select /*+ index_ffs (blahh idx1 idx2) */ 'z' from blahh where
3* (id1 = 1 and id2 is null) or (id1 is null and id2 = 1))
SQL> /
'
-
x
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82) 1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'BLAHH' (Cost=3 Card=1
Bytes=26)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP OR
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'IDX1' (NON-UNIQUE) (Cost=
1 Card=82)
8 5 BITMAP CONVERSION (FROM ROWIDS)
9 8 INDEX (RANGE SCAN) OF 'IDX2' (NON-UNIQUE) (Cost=
1 Card=82)
After analyzing just table everything is OK as in previous statement: SQL> exec dbms_stats.gather_table_stats(user, 'BLAHH');
PL/SQL procedure successfully completed.
SQL> select 'x' from dual where exists ( 2 select /*+ index_ffs (blahh idx1 idx2) */ 'z' from blahh where 3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1)) 4 /
'
-
x
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82) 1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'BLAHH' (Cost=45 Card=1
Bytes=3)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP OR
6 5 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'IDX1' (NON-UNIQUE) (Cost=
25 Card=1)
8 5 BITMAP CONVERSION (FROM ROWIDS)
9 8 INDEX (RANGE SCAN) OF 'IDX2' (NON-UNIQUE) (Cost=
1 Card=1)
BUT after analyzing with cascade = true everything get's mad: SQL> exec dbms_stats.gather_table_stats(user, 'BLAHH', cascade=>TRUE)
PL/SQL procedure successfully completed.
SQL> select 'x' from dual where exists ( 2 select /*+ index_ffs (blahh idx1 idx2) */ 'z' from blahh where 3 (id1 = 1 and id2 is null) or (id1 is null and id2 = 1)) 4 /
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82) 1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'BLAHH' (Cost=1 Card=1
Bytes=3)
4 3 INDEX (FULL SCAN) OF 'IDX2' (NON-UNIQUE)
So probably something is bad with index statistics collection.
> Secondly this behavior is probably fixed in 10.2.0.1, at least I can't
> trigger the wrong plan using the INDES_FFS hint.
Yea me too.
Gints Plivna
http://www.gplivna.eu/
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 14 2006 - 10:52:49 CDT
 |
 |