12 Replies Latest reply: Jan 5, 2013 4:50 AM by Jonathan Lewis RSS

    Invisible index and cardinality

    883641
      Based on Richard Foote post regarding Invisible Indexes and the fact the optimizer still uses their statistics to calculate the correct cardinality

      http://richardfoote.wordpress.com/2008/11/20/visible-invisible-indexes-the-invisible-band/

      I've made a simple experiment :

      >


      HR > SET AUTOTRACE OFF
      HR >
      HR > DROP TABLE test_tbl PURGE ;
      DROP TABLE test_tbl PURGE
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist


      HR >
      HR > CREATE TABLE test_tbl (id NUMBER );

      Table created.

      HR >
      HR > CREATE INDEX test_tbl_ix ON test_tbl(id) INVISIBLE;

      Index created.

      HR >
      HR > BEGIN
      2 FOR i IN 1 .. 10000 LOOP
      3 INSERT INTO test_tbl VALUES (i);
      4 END LOOP;
      5 COMMIT;
      6 END;
      7 /

      PL/SQL procedure successfully completed.

      HR >
      HR > SET AUTOTRACE TRACEONLY EXPLAIN
      HR >
      HR > ALTER SESSION SET optimizer_dynamic_sampling = 0 ;

      Session altered.

      HR >
      HR > -- correct cardinality, I'm guessing it uses the index stats
      HR > SELECT * FROM test_tbl WHERE id = 9999;

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 602094504

      ------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
      |* 1 | TABLE ACCESS FULL| TEST_TBL | 1 | 13 | 2 (0)| 00:00:01 |
      ------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      1 - filter("ID"=9999)

      HR >
      HR > DROP INDEX test_tbl_ix ;

      Index dropped.

      HR >
      HR > -- wrong cardinality, since index stats no longer available
      HR > SELECT * FROM test_tbl WHERE id = 9999;

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 602094504

      ------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 16 | 208 | 7 (0)| 00:00:01 |
      |* 1 | TABLE ACCESS FULL| TEST_TBL | 16 | 208 | 7 (0)| 00:00:01 |
      ------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      1 - filter("ID"=9999)

      HR >
      HR > CREATE INDEX test_tbl_ix ON test_tbl(id) INVISIBLE;

      Index created.

      HR >
      HR > -- how come stats are still wrong ... ?
      HR > SELECT * FROM test_tbl WHERE id = 9999;

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 602094504

      ------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 16 | 208 | 7 (0)| 00:00:01 |
      |* 1 | TABLE ACCESS FULL| TEST_TBL | 16 | 208 | 7 (0)| 00:00:01 |
      ------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      1 - filter("ID"=9999)

      HR >
      As you can see on my first attempt the optimizer chose to use the index stats it, on the second attempt after I dropped and recreated the Index, the optimizer refuse to use these available stats.

      Could anyone explain me why ?

      Thank you.

      Edited by: user3646231 on Jan 3, 2013 12:35 AM
        • 1. Re: Invisible index and cardinality
          moreajays
          Hi,

          In both the scenario's i can see only change in plan is that its cost
          And its doing FTS in both means no index is being used
          If you mean to say that index stats being used which has changed the cost then it may not be true

          Let me know if i have catch your question

          Thanks,
          Ajay More
          http://moreajays.blogspot.com
          • 2. Re: Invisible index and cardinality
            883641
            Hi,

            On attempt 1, after I've created the Invisible Index, cardinality = 1 , cost = 2

            On attempt 2, after the Invisible Index was dropped - cardinality = 16, cost = 7

            On attempt 3, after I've recreated the Invisible Index, Cardinality is still 16, and cost is still 7. I expected to see here the same cost & cardinality as my first attempt
            • 3. Re: Invisible index and cardinality
              Jonathan Lewis
              As you can see on my first attempt the optimizer chose to use the index stats it, on the second attempt after I dropped and recreated the Index, the optimizer refuse to use these available stats.

              Could anyone explain me why ?
              Good work - but you still have to eliminate some negatives before you reach your conclusion.
              Change the index to VISIBLE and you'll probably see that the index is used but the cardinality is still reported as 16.

              When you create the index BEFORE populating the table, the index stats say "no data".
              When you create the index AFTER populating the table, the index stats are completely different.

              Without looking too closely at the 10053, I'd guess that the index stats override the table stats when they state explicitly that there is no data, but when the index stats say there is data then the optimizer uses the basic table estimates to work out the cardinality.

              Regards
              Jonathan Lewis
              • 4. Re: Invisible index and cardinality
                883641
                Hi Jonathan,

                This time according to your suggestment, on my first attempt I've created the Invisible Index AFTER the table was populated, so now in both cases the index was created after the table was populated. still u can see the same behavior

                >

                HR > DROP TABLE test_tbl PURGE ;

                Table dropped.

                HR >
                HR > CREATE TABLE test_tbl (id NUMBER );

                Table created.

                HR >
                HR > BEGIN
                2 FOR i IN 1 .. 10000 LOOP
                3 INSERT INTO test_tbl VALUES (i);
                4 END LOOP;
                5 COMMIT;
                6 END;
                7 /

                PL/SQL procedure successfully completed.

                HR >
                HR > CREATE INDEX test_tbl_ix ON test_tbl(id) INVISIBLE;

                Index created.

                HR >
                HR > SET AUTOTRACE TRACEONLY EXPLAIN
                HR >
                HR > ALTER SESSION SET optimizer_dynamic_sampling = 0 ;

                Session altered.

                HR >
                HR > -- correct cardinality, I'm guessing it uses the index stats
                HR > SELECT * FROM test_tbl WHERE id = 9999;

                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 602094504

                ------------------------------------------------------------------------------
                | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                ------------------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
                |* 1 | TABLE ACCESS FULL| TEST_TBL | 1 | 13 | 2 (0)| 00:00:01 |
                ------------------------------------------------------------------------------

                Predicate Information (identified by operation id):
                ---------------------------------------------------

                1 - filter("ID"=9999)

                HR >
                HR > DROP INDEX test_tbl_ix ;

                Index dropped.

                HR >
                HR > -- wrong cardinality, since index stats no longer available
                HR > SELECT * FROM test_tbl WHERE id = 9999;

                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 602094504

                ------------------------------------------------------------------------------
                | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                ------------------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 16 | 208 | 7 (0)| 00:00:01 |
                |* 1 | TABLE ACCESS FULL| TEST_TBL | 16 | 208 | 7 (0)| 00:00:01 |
                ------------------------------------------------------------------------------

                Predicate Information (identified by operation id):
                ---------------------------------------------------

                1 - filter("ID"=9999)

                HR >

                HR > CREATE INDEX test_tbl_ix ON test_tbl(id) INVISIBLE;

                Index created.

                HR >
                HR > -- how come stats are still wrong ... ?
                HR > SELECT * FROM test_tbl WHERE id = 9999;

                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 602094504

                ------------------------------------------------------------------------------
                | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                ------------------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 16 | 208 | 7 (0)| 00:00:01 |
                |* 1 | TABLE ACCESS FULL| TEST_TBL | 16 | 208 | 7 (0)| 00:00:01 |
                ------------------------------------------------------------------------------

                Predicate Information (identified by operation id):
                ---------------------------------------------------

                1 - filter("ID"=9999)

                HR >
                >

                Edited by: user3646231 on Jan 3, 2013 1:49 AM
                • 5. Re: Invisible index and cardinality
                  883641
                  It goes further than I thought, how come index create & drop - affects estimates on table cardinality ?

                  >

                  HR > SET AUTOTRACE OFF
                  HR >
                  HR > DROP TABLE test_tbl PURGE ;

                  Table dropped.

                  HR >
                  HR > CREATE TABLE test_tbl (id NUMBER , description varchar2(25) );

                  Table created.

                  HR >
                  HR > BEGIN
                  2 FOR i IN 1 .. 10000 LOOP
                  3 INSERT INTO test_tbl VALUES (i, 'value - '|| i);
                  4 END LOOP;
                  5 COMMIT;
                  6 END;
                  7 /

                  PL/SQL procedure successfully completed.

                  HR >
                  HR > ALTER SESSION SET optimizer_dynamic_sampling = 0 ;

                  Session altered.

                  HR >
                  HR > SET AUTOTRACE TRACEONLY EXPLAIN
                  HR >
                  HR > -- cardinality before create & drop
                  HR > SELECT * FROM test_tbl WHERE id = 9999;

                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 602094504

                  ------------------------------------------------------------------------------
                  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                  ------------------------------------------------------------------------------
                  | 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
                  |* 1 | TABLE ACCESS FULL| TEST_TBL | 1 | 27 | 2 (0)| 00:00:01 |
                  ------------------------------------------------------------------------------

                  Predicate Information (identified by operation id):
                  ---------------------------------------------------

                  1 - filter("ID"=9999)

                  HR >
                  HR > CREATE INDEX test_tbl_ix ON test_tbl(id) ;

                  Index created.

                  HR >
                  HR > DROP INDEX test_tbl_ix ;

                  Index dropped.

                  HR >
                  HR > -- cardinality after create & drop
                  HR > SELECT * FROM test_tbl WHERE id = 9999;

                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 602094504

                  ------------------------------------------------------------------------------
                  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                  ------------------------------------------------------------------------------
                  | 0 | SELECT STATEMENT | | 29 | 783 | 11 (0)| 00:00:01 |
                  |* 1 | TABLE ACCESS FULL| TEST_TBL | 29 | 783 | 11 (0)| 00:00:01 |
                  ------------------------------------------------------------------------------

                  Predicate Information (identified by operation id):
                  ---------------------------------------------------

                  1 - filter("ID"=9999)

                  HR >
                  >

                  Edited by: user3646231 on Jan 3, 2013 4:26 AM
                  • 6. Re: Invisible index and cardinality
                    Martin Preiss
                    only some wild guesses:
                    without the index creation I also get a initial cardinality of 1. When I create the index there is no cardinality change, but when I drop the index the cardinality is 16:
                    -- 8K blocksize
                    -- MSSM
                    DROP TABLE test_tbl PURGE ;
                    CREATE TABLE test_tbl (id NUMBER ) tablespace test_ts;
                    BEGIN
                     FOR i IN 1 .. 10000 LOOP
                     INSERT INTO test_tbl VALUES (i);
                     END LOOP;
                     COMMIT;
                     END;
                    /
                    
                    ALTER SESSION SET optimizer_dynamic_sampling = 0 ;
                    
                    explain plan for SELECT * FROM test_tbl WHERE id = 9999;
                    
                    select * from table(dbms_xplan.display);
                    
                    Plan hash value: 602094504
                    
                    ------------------------------------------------------------------------------
                    | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
                    |*  1 |  TABLE ACCESS FULL| TEST_TBL |     1 |    13 |     2   (0)| 00:00:01 |
                    ------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       1 - filter("ID"=9999)
                    
                    CREATE INDEX test_tbl_ix ON test_tbl(id) INVISIBLE;
                    
                    Plan hash value: 602094504
                    
                    ------------------------------------------------------------------------------
                    | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
                    |*  1 |  TABLE ACCESS FULL| TEST_TBL |     1 |    13 |     2   (0)| 00:00:01 |
                    ------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       1 - filter("ID"=9999)
                    
                    drop index test_tbl_ix;
                    
                    Plan hash value: 602094504
                    
                    ------------------------------------------------------------------------------
                    | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT  |          |    16 |   208 |     7   (0)| 00:00:01 |
                    |*  1 |  TABLE ACCESS FULL| TEST_TBL |    16 |   208 |     7   (0)| 00:00:01 |
                    ------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       1 - filter("ID"=9999)
                    Looking in the CBO trace I see the following lines:
                    -- before the index was removed
                    ***************************************
                    BASE STATISTICAL INFORMATION
                    ***********************
                    Table Stats::
                      Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
                        #Rows: 82  #Blks:  1  AvgRowLen:  100.00
                    Access path analysis for TEST_TBL
                    ***************************************
                    SINGLE TABLE ACCESS PATH 
                      Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
                      Table: TEST_TBL  Alias: TEST_TBL
                        Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
                      Access Path: TableScan
                        Cost:  2.00  Resp: 2.00  Degree: 0
                          Cost_io: 2.00  Cost_cpu: 23521
                          Resp_io: 2.00  Resp_cpu: 23521
                      Best:: AccessPath: TableScan
                             Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.82  Bytes: 0
                    
                    -- after the drop index
                    ***************************************
                    BASE STATISTICAL INFORMATION
                    ***********************
                    Table Stats::
                      Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
                        #Rows: 1634  #Blks:  20  AvgRowLen:  100.00
                    Access path analysis for TEST_TBL
                    ***************************************
                    SINGLE TABLE ACCESS PATH 
                      Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
                      Table: TEST_TBL  Alias: TEST_TBL
                        Card: Original: 1634.000000  Rounded: 16  Computed: 16.34  Non Adjusted: 16.34
                      Access Path: TableScan
                        Cost:  7.03  Resp: 7.03  Degree: 0
                          Cost_io: 7.00  Cost_cpu: 469229
                          Resp_io: 7.00  Resp_cpu: 469229
                      Best:: AccessPath: TableScan
                             Cost: 7.03  Degree: 1  Resp: 7.03  Card: 16.34  Bytes: 0
                    So I think the card 1 has nothing to do with the index statistics but is a result of the optimizers guess that there is only one block in the table. After the index is dropped the CBO calculates with #Blks: 20 and I assume that's a value from the header block of the segment:
                      Extent Header:: spare1: 0      spare2: 0      #extents: 3      #blocks: 23    
                                      last map  0x00000000  #maps: 0      offset: 4128  
                          Highwater::  0x02c00095  ext#: 2      blk#: 5      ext size: 8     
                      #blocks in seg. hdr's freelists: 5     
                      #blocks below: 20    
                      mapblk  0x00000000  offset: 2     
                                       Unlocked
                         Map Header:: next  0x00000000  #extents: 3    obj#: 93093  flag: 0x40000000
                      Extent Map
                    But I don't know why the CBO begins to take a look at the header block only after the drop index.

                    The calculation seems to use a fixed AvgRowLen (100) and accordingly a fixed number of rows per block (82). The cardinality for the complete table is 1634 and the selectivity of id = constant seems to be 1%.

                    Regards

                    Martin

                    Edited by: mpreiss on Jan 3, 2013 2:01 PM
                    • 7. Re: Invisible index and cardinality
                      883641
                      Hi Martin,

                      That's some very interesting results, but the mistery remains unsolved :)

                      Could anyone else offer some more insights regarding this topic ?
                      • 8. Re: Invisible index and cardinality
                        Martin Preiss
                        Hi,

                        some small additions:

                        the CBO trace (event 10053) seems to show that the index statistics of a non-unique index are not used to adjust the cardinality even if the index is created as VISIBLE. A unique index is a different case because now it's obvious that there is only one row for a given ID:
                        -- without index
                        ***************************************
                        BASE STATISTICAL INFORMATION
                        ***********************
                        Table Stats::
                          Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
                            #Rows: 82  #Blks:  1  AvgRowLen:  100.00
                        Access path analysis for TEST_TBL
                        ***************************************
                        SINGLE TABLE ACCESS PATH 
                          Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
                          Table: TEST_TBL  Alias: TEST_TBL
                            Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
                          Access Path: TableScan
                            Cost:  2.00  Resp: 2.00  Degree: 0
                              Cost_io: 2.00  Cost_cpu: 23521
                              Resp_io: 2.00  Resp_cpu: 23521
                          Best:: AccessPath: TableScan
                                 Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.82  Bytes: 0
                        
                        -- with non-unique index
                        ***************************************
                        BASE STATISTICAL INFORMATION
                        ***********************
                        Table Stats::
                          Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
                            #Rows: 82  #Blks:  1  AvgRowLen:  100.00
                        Index Stats::
                          Index: TEST_TBL_IX  Col#: 1
                            LVLS: 1  #LB: 21  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
                        Access path analysis for TEST_TBL
                        ***************************************
                        SINGLE TABLE ACCESS PATH 
                          Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
                          Table: TEST_TBL  Alias: TEST_TBL
                            Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
                          Access Path: TableScan
                            Cost:  2.00  Resp: 2.00  Degree: 0
                              Cost_io: 2.00  Cost_cpu: 23521
                              Resp_io: 2.00  Resp_cpu: 23521
                          Access Path: index (index (FFS))
                            Index: TEST_TBL_IX
                            resc_io: 12.00  resc_cpu: 1849550
                            ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
                          Access Path: index (FFS)
                            Cost:  12.00  Resp: 12.00  Degree: 1
                              Cost_io: 12.00  Cost_cpu: 1849550
                              Resp_io: 12.00  Resp_cpu: 1849550
                          Access Path: index (AllEqGuess)
                            Index: TEST_TBL_IX
                            resc_io: 1.00  resc_cpu: 15971
                            ix_sel: 0.004000  ix_sel_with_filters: 0.004000 
                            Cost: 1.00  Resp: 1.00  Degree: 1
                          Best:: AccessPath: IndexRange
                          Index: TEST_TBL_IX
                                 Cost: 1.00  Degree: 1  Resp: 1.00  Card: 0.82  Bytes: 0
                        
                        -- with unique index
                        ***************************************
                        BASE STATISTICAL INFORMATION
                        ***********************
                        Table Stats::
                          Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
                            #Rows: 82  #Blks:  1  AvgRowLen:  100.00
                        Index Stats::
                          Index: TEST_TBL_IX  Col#: 1
                            LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
                        ***************************************
                        1-ROW TABLES:  TEST_TBL[TEST_TBL]#0
                        Access path analysis for TEST_TBL
                        ***************************************
                        SINGLE TABLE ACCESS PATH 
                          Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
                          Table: TEST_TBL  Alias: TEST_TBL
                            Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
                          Access Path: TableScan
                            Cost:  2.00  Resp: 2.00  Degree: 0
                              Cost_io: 2.00  Cost_cpu: 23521
                              Resp_io: 2.00  Resp_cpu: 23521
                          Access Path: index (index (FFS))
                            Index: TEST_TBL_IX
                            resc_io: 12.00  resc_cpu: 1842429
                            ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
                          Access Path: index (FFS)
                            Cost:  12.00  Resp: 12.00  Degree: 1
                              Cost_io: 12.00  Cost_cpu: 1842429
                              Resp_io: 12.00  Resp_cpu: 1842429
                          Access Path: index (UniqueScan)
                            Index: TEST_TBL_IX
                            resc_io: 1.00  resc_cpu: 8171
                            ix_sel: 0.012195  ix_sel_with_filters: 0.012195 
                            Cost: 1.00  Resp: 1.00  Degree: 1
                          Access Path: index (AllEqUnique)
                            Index: TEST_TBL_IX
                            resc_io: 1.00  resc_cpu: 8171
                            ix_sel: 0.004000  ix_sel_with_filters: 0.004000 
                            Cost: 1.00  Resp: 1.00  Degree: 1
                         One row Card: 1.000000
                          Best:: AccessPath: IndexUnique
                          Index: TEST_TBL_IX
                                 Cost: 1.00  Degree: 1  Resp: 1.00  Card: 1.00  Bytes: 0
                        In the first two cases the cardinality ist 0.82; only case 3 brings Card: 1.00.

                        In order to change the costing it is also an option to add a NOT NULL Constraint
                        -- the same test scenario
                        explain plan for SELECT * FROM test_tbl WHERE id = 9999;
                        ------------------------------------------------------
                        | Id  | Operation         | Name     | Rows  | Bytes |
                        ------------------------------------------------------
                        |   0 | SELECT STATEMENT  |          |     1 |    13 |
                        |*  1 |  TABLE ACCESS FULL| TEST_TBL |     1 |    13 |
                        ------------------------------------------------------
                        
                        alter table test_tbl modify id not null;
                        
                        explain plan for SELECT * FROM test_tbl WHERE id = 9999;
                        ------------------------------------------------------
                        | Id  | Operation         | Name     | Rows  | Bytes |
                        ------------------------------------------------------
                        |   0 | SELECT STATEMENT  |          |    16 |   208 |
                        |*  1 |  TABLE ACCESS FULL| TEST_TBL |    16 |   208 |
                        ------------------------------------------------------
                        But the addition of a table comment (a very small DDL operation) is not enough to change the costing.

                        In the CBO trace and the block dump I don't see anything that explains why the CBO changes his mind after the DROP INDEX command.

                        Perhaps the answer could be found in volume 2 or 3 of Cost Based Oracle ...

                        Regards

                        Martin
                        • 9. Re: Invisible index and cardinality
                          Jonathan Lewis
                          user3646231 wrote:
                          Hi Jonathan,

                          This time according to your suggestment, on my first attempt I've created the Invisible Index AFTER the table was populated, so now in both cases the index was created after the table was populated. still u can see the same behavior
                          Good idea - this is rather interesting.
                          Some nice work from Martin Preiss as well.

                          I'm going to take a look and see if I can think of anything.
                          Did you quote a version number ? I'll be playing with 11.2.0.3

                          Regards
                          Jonathan Lewis
                          • 10. Re: Invisible index and cardinality
                            Jonathan Lewis
                            I think one of the key things is that some DDL will cause Oracle to flush and reload some of the dictionary cache information for the table - including the number of blocks, which can be derived from the table segment header. The problem is identifying which DDL.

                            "alter table test_tbl move" will do it, as you can see if you move the table after populating it.
                            alter index test_tbl_ix visible/invisible will do it, but create index won't
                            drop index test_tbl_ix will do it, as we have already seen

                            I think there must be some detail about how Oracle thinks that DDL may cause important statistics to change, and therefore modifies the dictionary cache entries - but whether the apparent inconsistencies are deliberate or accidental I can't yet decide.

                            Regards
                            Jonathan Lewis
                            • 11. Re: Invisible index and cardinality
                              883641
                              Jonathan you are correct, Index recreation flushes some of the dictionary cache information related to the table.
                              ALTER SYSTEM FLUSH shared_pool ; will cause the same behavior.

                              In both cases the Optimizer makes a guess, before flushing the shared pool and after. As you can see in the following example cardinality of 1 before flushing the shared pool is only a guess.

                              Now the only question is - based on what the Optimizer makes the guess ? and how flushing the shared pool affects it.


                              HR >
                              HR > ------------------------------------ Scenario A
                              HR >
                              HR > SET AUTOTRACE OFF
                              HR > ALTER SESSION SET optimizer_dynamic_sampling = 0 ;
                              
                              Session altered.
                              
                              HR >
                              HR > DROP TABLE numbers ;
                              
                              Table dropped.
                              
                              HR >
                              HR > CREATE TABLE numbers (id number) ;
                              
                              Table created.
                              
                              HR >
                              HR > BEGIN
                                2  FOR i IN 1..7 LOOP
                                3     FOR j IN 1..i LOOP
                                4     INSERT INTO numbers VALUES (i) ;
                                5     END LOOP ;
                                6  END LOOP ;
                                7  END ;
                                8  /
                              
                              PL/SQL procedure successfully completed.
                              
                              HR >
                              HR >
                              HR > SET LINES 200
                              HR >
                              HR > SELECT COUNT(*) FROM numbers WHERE id = 4 ;
                              
                                COUNT(*)
                              ----------
                                       4
                              
                              HR >
                              HR > SET AUTOTRACE TRACEONLY EXPLAIN
                              HR >
                              HR > SELECT * FROM numbers WHERE id = 4 ;
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 302370249
                              
                              -----------------------------------------------------------------------------
                              | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                              -----------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT  |         |     1 |    13 |     2   (0)| 00:00:01 |
                              |*  1 |  TABLE ACCESS FULL| NUMBERS |     1 |    13 |     2   (0)| 00:00:01 |
                              -----------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 1 - filter("ID"=4)
                              
                              HR >
                              HR > ALTER SYSTEM FLUSH SHARED_POOL ;
                              
                              System altered.
                              
                              HR >
                              HR > SELECT * FROM numbers WHERE id = 4 ;
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 302370249
                              
                              -----------------------------------------------------------------------------
                              | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                              -----------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT  |         |     4 |    52 |     3   (0)| 00:00:01 |
                              |*  1 |  TABLE ACCESS FULL| NUMBERS |     4 |    52 |     3   (0)| 00:00:01 |
                              -----------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 1 - filter("ID"=4)
                              
                              HR >
                              HR >
                              HR >
                              HR >
                              HR > --------------------------------------- Scenario B
                              HR >
                              HR >
                              HR > SET AUTOTRACE OFF
                              HR > ALTER SESSION SET optimizer_dynamic_sampling = 0 ;
                              
                              Session altered.
                              
                              HR >
                              HR > DROP TABLE numbers ;
                              
                              Table dropped.
                              
                              HR >
                              HR > CREATE TABLE numbers (id number) ;
                              
                              Table created.
                              
                              HR >
                              HR > BEGIN
                                2  FOR i IN 1..100 LOOP
                                3     FOR j IN 1..100 LOOP
                                4     INSERT INTO numbers VALUES (i) ;
                                5     END LOOP ;
                                6  END LOOP ;
                                7  END ;
                                8  /
                              
                              PL/SQL procedure successfully completed.
                              
                              HR >
                              HR >
                              HR > SET LINES 200
                              HR >
                              HR > SELECT COUNT(*) FROM numbers WHERE id = 1 ;
                              
                                COUNT(*)
                              ----------
                                     100
                              
                              HR >
                              HR > SET AUTOTRACE TRACEONLY EXPLAIN
                              HR >
                              HR > SELECT * FROM numbers WHERE id = 1 ;
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 302370249
                              
                              -----------------------------------------------------------------------------
                              | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                              -----------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT  |         |     1 |    13 |     2   (0)| 00:00:01 |
                              |*  1 |  TABLE ACCESS FULL| NUMBERS |     1 |    13 |     2   (0)| 00:00:01 |
                              -----------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 1 - filter("ID"=1)
                              
                              HR >
                              HR > ALTER SYSTEM FLUSH SHARED_POOL ;
                              
                              System altered.
                              
                              HR >
                              HR > SELECT * FROM numbers WHERE id = 1 ;
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 302370249
                              
                              -----------------------------------------------------------------------------
                              | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                              -----------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT  |         |    16 |   208 |     7   (0)| 00:00:01 |
                              |*  1 |  TABLE ACCESS FULL| NUMBERS |    16 |   208 |     7   (0)| 00:00:01 |
                              -----------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 1 - filter("ID"=1)
                              
                              HR >
                              HR >
                              HR >
                              • 12. Re: Invisible index and cardinality
                                Jonathan Lewis
                                user3646231 wrote:
                                Jonathan you are correct, Index recreation flushes some of the dictionary cache information related to the table.
                                ALTER SYSTEM FLUSH shared_pool ; will cause the same behavior.

                                In both cases the Optimizer makes a guess, before flushing the shared pool and after. As you can see in the following example cardinality of 1 before flushing the shared pool is only a guess.

                                Now the only question is - based on what the Optimizer makes the guess ? and how flushing the shared pool affects it.
                                I believe it looks at the extent map in the table segment header the first time it has to optimize an SQL statement that accesses the table. (In this case, when the first insert in your loop takes place, and later after you've dropped the index and do something new to the table.)

                                Here's a small sample of what's available in the header - I'd show the whole thing but I can't get the whole extent control block past "content not allowed" warning.
                                  
                                  Extent Control Header
                                  -----------------------------------------------------------------
                                  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 127   
                                                  last map  0x00000000  #maps: 0      offset: 4128  
                                      Highwater::  0x0180000b  ext#: 0      blk#: 1      ext size: 127   
                                  #blocks in seg. hdr's freelists: 1     
                                  #blocks below: 1     
                                Note particularly the "highwater"

                                Regards
                                Jonathan Lewis

                                Edited by: Jonathan Lewis on Jan 5, 2013 10:48 AM
                                Added (part of) extent control block