Oracle Scratchpad

August 21, 2014

Quiz night

Filed under: CBO,Indexing,NULL,Oracle,Troubleshooting,Tuning — Jonathan Lewis @ 6:05 pm GMT Aug 21,2014

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.

 

 

 

7 Comments »

  1. 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 GMT Aug 21,2014 | Reply

  2. 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 GMT Aug 21,2014 | Reply

  3. 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 GMT Aug 21,2014 | Reply

    • I don’t know which version was the first with this optimization, but i know about a couple parameters:

      SQL> @param_ nnull
      
      NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
      ---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
      _optim_enhance_nnull_detection           TRUE         TRUE         boolean      TRUE to enable index [fast] full scan more often
      
      SQL> @fix_control.sql "is not null"
      
          BUGNO VALUE SQL_FEATURE                         DESCRIPTION                                                                      OPTIMIZER_      EVENT IS_DEFAULT
      --------- ----- ----------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------
        4386734     1 QKSFM_CBO_4386734                   f()=f() => f() is not null, selectivity for f() is not null                      11.1.0.6            0          1
      
      

      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 GMT Aug 21,2014 | Reply

      • 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 GMT Aug 21,2014 | Reply

        • 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 GMT 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 GMT Aug 21,2014


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers