Here’s a script to create a table, with index, and collect stats on it. Once I’ve collected stats I’ve checked the execution plan to discover that a hint has been ignored (for a well-known reason):
create table t2 as select mod(rownum,200) n1, mod(rownum,200) n2, rpad(rownum,180) v1 from all_objects where rownum <= 3000 ; create index t2_i1 on t2(n1); begin dbms_stats.gather_table_stats( user, 't2', method_opt => 'for all columns size 1' ); end; / explain plan for select /*+ index(t2) */ n1 from t2 where n2 = 45 ; select * from table(dbms_xplan.display); ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 120 | 15 | |* 1 | TABLE ACCESS FULL| T2 | 15 | 120 | 15 | ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N2"=45)
Of course we don’t expect the optimizer to use the index because we didn’t declare n1 to be not null, so there may be rows in the table which do not appear in the index. The only option the optimizer has for getting the right answer is to use a full tablescan. So the question is this – how come Oracle will obey the hint in the following SQL statement:
explain plan for select /*+ leading (t2 t1) index(t2) index(t1) use_nl(t1) */ t2.n1, t1.n2 from t2 t2, t2 t1 where t2.n2 = 45 and t2.n1 = t1.n1 ; select * from table(dbms_xplan.display); ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 225 | 3600 | 3248 | | 1 | NESTED LOOPS | | 225 | 3600 | 3248 | | 2 | NESTED LOOPS | | 225 | 3600 | 3248 | |* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 15 | 120 | 3008 | | 4 | INDEX FULL SCAN | T2_I1 | 3000 | | 8 | |* 5 | INDEX RANGE SCAN | T2_I1 | 15 | | 1 | | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 15 | 120 | 16 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T2"."N2"=45) 5 - access("T2"."N1"="T1"."N1")
I ran this on 11.2.0.4, but it does the same on earlier versions.
Update:
This was clearly too easy – posted at 18:04, answered correctly at 18:21. At some point in it’s evolution the optimizer acquired a rule that allowed it to infer unwritten “is not null” predicates from the join predicate.
Hi,
Maybe because with equi-join you are eliminating the null values and the optimizer can jump using the index.
Regards,
Ivica
Comment by Ivica Arsov — August 21, 2014 @ 6:21 pm BST Aug 21,2014 |
Hi,
Because it is obvious to CBO that the predicate t2.n1 = t1.n1 will filter those rows out where n1 is null and which are therefore not in the index.
Turning the statement to an outer join leads to full table scan again.
Regards,
Andrej
Comment by Andrej — August 21, 2014 @ 6:24 pm BST Aug 21,2014 |
Correct answer – bonus points (not that there any prizes) for the outer join observations.
I said that earlier versions did the same – but it’s not ALL earlier versions, so can anyone answer the question: “which version was the first version where this inference appeared ?”
Comment by Jonathan Lewis — August 21, 2014 @ 7:57 pm BST Aug 21,2014 |
I don’t know which version was the first with this optimization, but i know about a couple parameters:
And, as far as i remember, since 11.2 oracle has similar optimization, but for antijoins – null aware antijoin.
Comment by Sayan Malakshinov — August 21, 2014 @ 8:34 pm BST Aug 21,2014 |
Good idea – but there are versions of Oracle where the parameter is true but the feature doesn’t work, and versions of Oracle prior to the fix you supplied where the feature does work.
In passing, the null-aware anti-join appeared in 11.1.0.6.
Comment by Jonathan Lewis — August 21, 2014 @ 8:46 pm BST Aug 21,2014 |
Hi, According to optimizer_features_enable the feature ‘_optim_enhance_nnull_detection’ appeared in 8.1.5 – description being ‘TRUE to enable index [fast] full scan more often’
Comment by @FranckPachot — August 21, 2014 @ 9:53 pm BST Aug 21,2014
Franck,
I guess you worked backwards through the legal values for optimizer_features_enable until you hit a value (8.1.4) where _optim_enhance_nnull_detection changed to false. Unfortunately even the terminal release of 8i (8.1.7.4) doesn’t implement this null inference, so I don’t think that that parameter applies.
Comment by Jonathan Lewis — August 21, 2014 @ 10:34 pm BST Aug 21,2014