This discussion is archived
12 Replies Latest reply: Jan 5, 2013 2:50 AM by Jonathan Lewis RSS

Invisible index and cardinality

883641 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points