1 2 Previous Next 17 Replies Latest reply: Aug 3, 2012 2:52 PM by Purvesh K RSS

    Optimizer choosing wrong object on unanalyzed table

    blama
      Hi,

      I'm on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi and noticed the following.
      When querying a table with many columns and with a 1-column PK for its rowcount, the DB uses the TABLE object and not the PK-INDEX-object.
      When you "ANALYZE TABLE x COMPUTE STATISTICS", the PK-INDEX-object is used afterwards.

      *In my opinion the PK-index (or more generally: The shortest-column unique index) should always be used for counting the rows as it contains exactly the same count of rows as the original object (because of the unique-property) and is always shorter and the number of read blocks is always equal or smaller compared to using the original table object.*

      h4. Please see the attached simple test case:
      CREATE TABLE x
      (
      a NUMBER,
      b NUMBER,
      CONSTRAINT x_pk PRIMARY KEY (a)
      ) ;
      INSERT INTO x
      (a, b)
      SELECT LEVEL AS a, LEVEL*2 AS b
      FROM DUAL
      CONNECT BY LEVEL <= 100000;
      COMMIT;
      DELETE PLAN_TABLE;
      EXPLAIN PLAN SET STATEMENT_ID = 'test_count_star_pk' FOR SELECT COUNT (*) FROM x;
      SELECT id, parent_id, position, OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, OPTIMIZER FROM PLAN_TABLE WHERE STATEMENT_ID = 'test_count_star_pk' ORDER BY id, parent_id, position;
      ANALYZE TABLE x COMPUTE STATISTICS;
      DELETE PLAN_TABLE;
      EXPLAIN PLAN SET STATEMENT_ID = 'test_count_star_pk' FOR SELECT COUNT (*) FROM x;
      SELECT id, parent_id, position, OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, OPTIMIZER FROM PLAN_TABLE WHERE STATEMENT_ID = 'test_count_star_pk' ORDER BY id, parent_id, position;
      DROP TABLE x;

      h4. Result (see bold rows):
      table X erstellt.
      100.000 Zeilen eingefügt.
      festgeschrieben.
      3 Zeilen gelöscht.
      plan SET erfolgreich.
      ID PARENT_ID POSITION OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE OPTIMIZER
      0 SELECT STATEMENT CHOOSE
      1 0 1 SORT AGGREGATE
      *2 1 1 TABLE ACCESS FULL X TABLE*

      table X analysiert.
      3 Zeilen gelöscht.
      plan SET erfolgreich.
      ID PARENT_ID POSITION OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE OPTIMIZER
      0 193 SELECT STATEMENT CHOOSE
      1 0 1 SORT AGGREGATE
      *2 1 1 INDEX FULL SCAN X_PK INDEX (UNIQUE) ANALYZED*

      table X gelöscht.

      What's your opinion on this?
        • 1. Re: Optimizer choosing wrong object on unanalyzed table
          Solomon Yakobson
          First of all, stop using ANALYZE and start using DBMS_STATS. Secondly, I can't reproduce your results:
          SQL> select * from v$version
            2  /
          
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
          PL/SQL Release 10.2.0.4.0 - Production
          CORE    10.2.0.4.0      Production
          TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
          NLSRTL Version 10.2.0.4.0 - Production
          
          SQL> CREATE TABLE x
            2  (
            3  a NUMBER,
            4  b NUMBER,
            5  CONSTRAINT x_pk PRIMARY KEY (a)
            6  ) ;
          
          Table created.
          
          SQL> INSERT INTO x
            2  (a, b)
            3  SELECT LEVEL AS a, LEVEL*2 AS b
            4  FROM DUAL
            5  CONNECT BY LEVEL <= 100000;
          
          100000 rows created.
          
          SQL> COMMIT;
          
          Commit complete.
          
          SQL> explain plan for
            2  SELECT COUNT (*) FROM x;
          
          Explained.
          
          SQL> @?\rdbms\admin\utlxpls
          
          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
          Plan hash value: 2071710178
          
          ----------------------------------------------------------------------
          | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
          ----------------------------------------------------------------------
          |   0 | SELECT STATEMENT      |      |     1 |    55   (0)| 00:00:01 |
          |   1 |  SORT AGGREGATE       |      |     1 |            |          |
          |   2 |   INDEX FAST FULL SCAN| X_PK |   100K|    55   (0)| 00:00:01 |
          ----------------------------------------------------------------------
          
          Note
          
          PLAN_TABLE_OUTPUT
          --------------------------------------------------------------------------------
          -----
             - dynamic sampling used for this statement
          
          13 rows selected.
          
          SQL> 
          SY.
          • 2. Re: Optimizer choosing wrong object on unanalyzed table
            blama
            Hi Solomon,

            thanks for the answer and the hint on ANALYZE/dbms_stats. I just tried it on 11g r2 and can confirm your results. I don't know why it doesn't work on 10g r2. As I'm not admin I can't test further there.
            I continued with 11g r2. Please see how the optimizer uses the "x" table-object instead of the "one_col_udx" index-object after dropping the PK:

            h3. Code:
            SELECT * FROM v$version;
            SET AUTOTRACE OFF
            SET ECHO ON
            CREATE TABLE x (
                  a NUMBER
                  , b NUMBER
                  , c NUMBER
                  , CONSTRAINT one_col_udx UNIQUE(a)
                  , CONSTRAINT two_col_udx UNIQUE(b, a)
                  , CONSTRAINT three_col_pk PRIMARY KEY(c, b, a)
            );
            INSERT INTO x (a, b, c)
            SELECT LEVEL AS a, LEVEL * 2 AS b, LEVEL * 3 AS c
            FROM DUAL CONNECT BY LEVEL <= 100000;
            DELETE plan_table;
            COMMIT;
            SET AUTOTRACE ON EXPLAIN STATISTICS
            
            SELECT COUNT( *) FROM x;
            BEGIN
            dbms_stats.gather_table_stats(
            ownname => user,
            tabname => 'x',
            estimate_percent => dbms_stats.auto_sample_size);
            END;
            / 
            ALTER TABLE x DROP CONSTRAINT three_col_pk;
            SELECT COUNT( *) FROM x;
            DROP TABLE x;
            h3. Result:
            ? SELECT * FROM v$version
            BANNER                                                                         
            --------------------------------------------------------------------------------
            Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
            PL/SQL Release 11.2.0.2.0 - Production                                           
            CORE     11.2.0.2.0     Production                                                         
            TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
            NLSRTL Version 11.2.0.2.0 - Production                                           
            
            ? set AUTOTRACE OFF
            Autotrace deaktiviert
            ? CREATE TABLE x (
                  a NUMBER
                  , b NUMBER
                  , c NUMBER
                  , CONSTRAINT one_col_udx UNIQUE(a)
                  , CONSTRAINT two_col_udx UNIQUE(b, a)
                  , CONSTRAINT three_col_pk PRIMARY KEY(c, b, a)
            )
            table X erstellt.
            ? INSERT INTO x (a, b, c)
            SELECT LEVEL AS a, LEVEL * 2 AS b, LEVEL * 3 AS c
            FROM DUAL CONNECT BY LEVEL <= 100000
            100.000 Zeilen eingefügt.
            ? DELETE plan_table
            6 Zeilen gelöscht.
            ? COMMIT
            festgeschrieben.
            ? set AUTOTRACE ON EXPLAIN STATISTICS
            Autotrace aktiviert
            Zeigt nur den Ausführungsplan an.
            ? SELECT COUNT( *) FROM x
            COUNT(*)
            --------
              100000 
            
            Plan hash value: 3458248754
             
            -----------------------------------------------------------------------------
            | Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
            -----------------------------------------------------------------------------
            |   0 | SELECT STATEMENT      |             |     1 |    68   (0)| 00:00:01 |
            |   1 |  SORT AGGREGATE       |             |     1 |            |          |
            |   2 |   INDEX FAST FULL SCAN| ONE_COL_UDX | 94285 |    68   (0)| 00:00:01 |
            -----------------------------------------------------------------------------
             
            Note
            -----
               - dynamic sampling used for this statement (level=2)
            
            ? BEGIN
            dbms_stats.gather_table_stats(
            ownname => user,
            tabname => 'x',
            estimate_percent => dbms_stats.auto_sample_size);
            END;
            anonymer Block abgeschlossen
            ? ALTER TABLE x DROP CONSTRAINT three_col_pk
            table X geändert.
            ? SELECT COUNT( *) FROM x
            COUNT(*)
            --------
              100000 
            
            Plan hash value: 989401810
             
            -------------------------------------------------------------------
            | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
            -------------------------------------------------------------------
            |   0 | SELECT STATEMENT   |      |     1 |   103   (1)| 00:00:02 |
            |   1 |  SORT AGGREGATE    |      |     1 |            |          |
            |   2 |   TABLE ACCESS FULL| X    |   100K|   103   (1)| 00:00:02 |
            -------------------------------------------------------------------
            
            ? DROP TABLE x
            table X gelöscht.
            IMO it should continue to use the "one_col_udx" udx-object.

            Edited by: blama on 03.08.2012 16:32
            • 3. Re: Optimizer choosing wrong object on unanalyzed table
              Purvesh K
              Another different Behaviour:
              SET AUTOTRACE ON;
              
              CREATE TABLE x (
                    a NUMBER
                    , b NUMBER
                    , c NUMBER
                    , CONSTRAINT one_col_udx UNIQUE(a)
                    , CONSTRAINT two_col_udx UNIQUE(b, a)
                    , CONSTRAINT three_col_pk PRIMARY KEY(c, b, a)
              );
              
              INSERT INTO x (a, b, c)
              SELECT LEVEL AS a, LEVEL * 2 AS b, LEVEL * 3 AS c
              FROM DUAL CONNECT BY LEVEL <= 100000;
              
              
              COMMIT;
               
              SELECT COUNT( *) FROM x;
              
              BEGIN
              dbms_stats.gather_table_stats(
              ownname => user,
              tabname => 'x',
              estimate_percent => dbms_stats.auto_sample_size);
              END;
              / 
              
              ALTER TABLE x DROP CONSTRAINT three_col_pk;
              
              SELECT COUNT(1) FROM x;
              
              COUNT(1)
              --------
                100000 
              
              Elapsed: 00:00:00.015
              Plan hash value: 3458248754
               
              -----------------------------------------------------------------------------
              | Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------
              |   0 | SELECT STATEMENT      |             |     1 |    53   (2)| 00:00:01 |
              |   1 |  SORT AGGREGATE       |             |     1 |            |          |
              |   2 |   INDEX FAST FULL SCAN| ONE_COL_UDX |   100K|    53   (2)| 00:00:01 |
              -----------------------------------------------------------------------------
              Oracle Version:
              >
              Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
              • 4. Re: Optimizer choosing wrong object on unanalyzed table
                blama
                Hi Purvesh,

                that is what I'd expect here as well. Strange.

                h2. EDIT: rest is wrong, see follow-up
                Also see this table definition:
                CREATE TABLE x (
                      a NUMBER 
                      , b NUMBER 
                      , c NUMBER NOT NULL --CHANGED
                      , CONSTRAINT one_col_udx UNIQUE(a)
                      , CONSTRAINT two_col_udx UNIQUE(b, c) --CHANGED
                      , CONSTRAINT three_col_pk PRIMARY KEY(c, b, a)
                );
                With one column set to NOT NULL (even if it is the 2nd column in the udx), the CBO chooses the udx-object over the table-object (drop PK first).

                I'd still say here that it should use the one_col_udx-object as just going through it row-by-row and counting should give the correct result.

                Edited by: blama on 03.08.2012 17:06

                Edited by: blama on 03.08.2012 18:04
                • 5. Re: Optimizer choosing wrong object on unanalyzed table
                  blama
                  Hi,

                  were are getting to the solution:
                  The behaviour of choosing the table over the one-column-udx-object is correct because the column was allowed to be NULL.
                  That way it is not included in the one-column-udx-object and counting over it does not work (see size of one-column-udx-object!). While the PK was in place, the column was NOT NULL. After dropping the PK the column can be NULL again. So it is not usable for counting from a CBO-point-of-view, even though all values were NOT NULL.

                  h3. See following script:
                  SELECT * FROM v$version;
                  SET AUTOTRACE OFF
                  SET ECHO ON
                  PURGE RECYCLEBIN;
                  CREATE TABLE x (
                        a NUMBER 
                        , b NUMBER 
                        , c NUMBER
                        , d NUMBER
                        , CONSTRAINT one_col_udx UNIQUE(a)
                        , CONSTRAINT two_col_udx UNIQUE(b, c)
                        , CONSTRAINT d_col_always_null_udx UNIQUE(d)
                        , CONSTRAINT three_col_pk PRIMARY KEY (c, b, a)
                  );
                  INSERT INTO x (a, b, c)
                  SELECT LEVEL AS a, LEVEL * 2 AS b, LEVEL * 3 AS c
                  FROM DUAL CONNECT BY LEVEL <= 200000;
                  DELETE plan_table;
                  COMMIT;
                  SET AUTOTRACE ON EXPLAIN STATISTICS
                  
                  SELECT COUNT(*) FROM x;
                  BEGIN
                  dbms_stats.gather_table_stats(
                  ownname => user,
                  tabname => 'x',
                  estimate_percent => dbms_stats.auto_sample_size);
                  END;
                  / 
                  SELECT COUNT(*) FROM x;
                  SELECT SEGMENT_NAME, bytes FROM user_segments ORDER BY SEGMENT_NAME;
                  ALTER TABLE x DROP CONSTRAINT three_col_pk;
                  SELECT COUNT(*) FROM x;
                  ALTER TABLE x MODIFY a NUMBER NOT NULL;
                  SELECT COUNT(*) FROM x;
                  DROP TABLE x;
                  h3. Result:
                  ? SELECT * FROM v$version
                  BANNER                                                                         
                  --------------------------------------------------------------------------------
                  Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
                  PL/SQL Release 11.2.0.2.0 - Production                                           
                  CORE     11.2.0.2.0     Production                                                         
                  TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
                  NLSRTL Version 11.2.0.2.0 - Production                                           
                  
                  ? set AUTOTRACE OFF
                  Autotrace deaktiviert
                  ? PURGE RECYCLEBIN
                  PURGE RECYCLEBIN
                  ? CREATE TABLE x (
                        a NUMBER 
                        , b NUMBER 
                        , c NUMBER
                        , d NUMBER
                        , CONSTRAINT one_col_udx UNIQUE(a)
                        , CONSTRAINT two_col_udx UNIQUE(b, c)
                        , CONSTRAINT d_col_always_null_udx UNIQUE(d)
                        , CONSTRAINT three_col_pk PRIMARY KEY (c, b, a)
                  )
                  table X erstellt.
                  ? INSERT INTO x (a, b, c)
                  SELECT LEVEL AS a, LEVEL * 2 AS b, LEVEL * 3 AS c
                  FROM DUAL CONNECT BY LEVEL <= 200000
                  200.000 Zeilen eingefügt.
                  ? DELETE plan_table
                  9 Zeilen gelöscht.
                  ? COMMIT
                  festgeschrieben.
                  ? set AUTOTRACE ON EXPLAIN STATISTICS
                  Autotrace aktiviert
                  Zeigt nur den Ausführungsplan an.
                  ? SELECT COUNT(*) FROM x
                  COUNT(*)
                  --------
                    200000 
                  
                  Plan hash value: 3458248754
                   
                  -----------------------------------------------------------------------------
                  | Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
                  -----------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT      |             |     1 |   136   (0)| 00:00:02 |
                  |   1 |  SORT AGGREGATE       |             |     1 |            |          |
                  |   2 |   INDEX FAST FULL SCAN| ONE_COL_UDX |   230K|   136   (0)| 00:00:02 |
                  -----------------------------------------------------------------------------
                   
                  Note
                  -----
                     - dynamic sampling used for this statement (level=2)
                  
                  ? BEGIN
                  dbms_stats.gather_table_stats(
                  ownname => user,
                  tabname => 'x',
                  estimate_percent => dbms_stats.auto_sample_size);
                  END;
                  anonymer Block abgeschlossen
                  ? SELECT COUNT(*) FROM x
                  COUNT(*)
                  --------
                    200000 
                  
                  Plan hash value: 3458248754
                   
                  -----------------------------------------------------------------------------
                  | Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
                  -----------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT      |             |     1 |   104   (1)| 00:00:02 |
                  |   1 |  SORT AGGREGATE       |             |     1 |            |          |
                  |   2 |   INDEX FAST FULL SCAN| ONE_COL_UDX |   200K|   104   (1)| 00:00:02 |
                  -----------------------------------------------------------------------------
                  
                  ? SELECT SEGMENT_NAME, bytes FROM user_segments ORDER BY SEGMENT_NAME
                  SEGMENT_NAME                                                                      BYTES
                  --------------------------------------------------------------------------------- -----
                  D_COL_ALWAYS_NULL_UDX                                                             65536 
                  ONE_COL_UDX                                                                       4194304 
                  THREE_COL_PK                                                                      6291456 
                  TWO_COL_UDX                                                                       5242880 
                  X                                                                                 5242880 
                  
                  ? ALTER TABLE x DROP CONSTRAINT three_col_pk
                  table X geändert.
                  ? SELECT COUNT(*) FROM x
                  COUNT(*)
                  --------
                    200000 
                  
                  Plan hash value: 989401810
                   
                  -------------------------------------------------------------------
                  | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
                  -------------------------------------------------------------------
                  |   0 | SELECT STATEMENT   |      |     1 |   172   (2)| 00:00:03 |
                  |   1 |  SORT AGGREGATE    |      |     1 |            |          |
                  |   2 |   TABLE ACCESS FULL| X    |   200K|   172   (2)| 00:00:03 |
                  -------------------------------------------------------------------
                  
                  ? ALTER TABLE x MODIFY a NUMBER NOT NULL
                  table X geändert.
                  ? SELECT COUNT(*) FROM x
                  COUNT(*)
                  --------
                    200000 
                  
                  Plan hash value: 3458248754
                   
                  -----------------------------------------------------------------------------
                  | Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
                  -----------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT      |             |     1 |   104   (1)| 00:00:02 |
                  |   1 |  SORT AGGREGATE       |             |     1 |            |          |
                  |   2 |   INDEX FAST FULL SCAN| ONE_COL_UDX |   200K|   104   (1)| 00:00:02 |
                  -----------------------------------------------------------------------------
                  
                  ? DROP TABLE x
                  table X gelöscht.
                  So it uses the UDX when the column is NOT NULL (because if one column of the UDX is NOT NULL, every row has to be included in the UDX), but uses the x-table-object when the UDX does not include at least one NOT NULL column.

                  h2. The question is now:
                  Why does ORACLE use the UDX in Purvesh's example?
                  • 6. Re: Optimizer choosing wrong object on unanalyzed table
                    Mustafa KALAYCI
                    I think, Purvesh has missed something in his example. this could be happened if you declared NOT NULL for column explicitly. if you do that, dropping primary key constraint will not take not null property from column.
                    drop table x;
                    CREATE TABLE x (
                          a NUMBER NOT NULL /*****HERE IT IS, ADDING A NOT NULL*****/
                          , b NUMBER
                          , c NUMBER
                          , CONSTRAINT one_col_udx UNIQUE(a)
                          , CONSTRAINT two_col_udx UNIQUE(b, a)
                          , CONSTRAINT three_col_pk PRIMARY KEY(c, b, a)
                    );
                     
                    INSERT INTO x (a, b, c)
                    SELECT LEVEL AS a, LEVEL * 2 AS b, LEVEL * 3 AS c
                    FROM DUAL CONNECT BY LEVEL <= 100000;
                     
                     
                    COMMIT;
                     
                    SELECT COUNT( *) FROM x;
                     
                    BEGIN
                    dbms_stats.gather_table_stats(
                    ownname => user,
                    tabname => 'x',
                    estimate_percent => dbms_stats.auto_sample_size);
                    END;
                    / 
                     
                    ALTER TABLE x DROP CONSTRAINT three_col_pk;
                     
                    SELECT COUNT(1) FROM x;
                    
                    ------------------------------------------------------------------------
                    | Id  | Operation        | Name        | Rows  | Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT |             |     1 |    28   (0)| 00:00:01 |
                    |   1 |  SORT AGGREGATE  |             |     1 |            |          |
                    |   2 |   INDEX FULL SCAN| ONE_COL_UDX |   100K|    28   (0)| 00:00:01 |
                    ------------------------------------------------------------------------
                     
                    • 7. Re: Optimizer choosing wrong object on unanalyzed table
                      blama
                      Hi Mustafa,

                      possible, but he included this CREATE, so we have to wait for his answer. If he used NOT NULL, everything is solved. Purvesh?
                      • 8. Re: Optimizer choosing wrong object on unanalyzed table
                        Purvesh K
                        I used the same script as I posted in my post. No modifications.

                        Just used Auto Trace on instead of explain plan.
                        • 9. Re: Optimizer choosing wrong object on unanalyzed table
                          John Spencer
                          I think either a misinterpretation of something, or that there is something serioulsy wrong with 11.2.0.2 express edition, and my bet would be on the former.

                          I cannot reproduce on 11.2.0.3 Enterprise.

                          If the plan is actually accurate, then inserting a null value into column a (which should be possible if eveything is as posted) will result in an incorrect count.

                          John
                          • 10. Re: Optimizer choosing wrong object on unanalyzed table
                            Mustafa KALAYCI
                            Purvesh, don't get me wrong, It was just an idea about why it happens. maybe something about old cursor execution plan or something I don't know but it is not possible. can you run the same code in your db with a completely new table name ?
                            • 11. Re: Optimizer choosing wrong object on unanalyzed table
                              blama
                              I'm with John on this one.
                              Purvesh, could you insert an additional (NULL, 1, 2) row in your table after removing the PK and count again as well as print the plan?
                              Result should either be: one_col_udx-object + 100000 rows (bad, because wrong)
                              Or: x-table-object and 100001 rows
                              Impossible: one_col_udx-object + 100001 rows (impossible, because a reference to the physical row with a:NULL shouldn't be in the one_col_udx-object)
                              • 12. Re: Optimizer choosing wrong object on unanalyzed table
                                Purvesh K
                                I am not getting you Wrong, Friend. :)

                                Below is the test case again executed:
                                set timing on;
                                SET AUTOTRACE ON;
                                
                                drop table x;
                                
                                 
                                CREATE TABLE t (
                                      a NUMBER
                                      , b NUMBER
                                      , c NUMBER
                                      , CONSTRAINT one_col_udx UNIQUE(a)
                                      , CONSTRAINT two_col_udx UNIQUE(b, a)
                                      , CONSTRAINT three_col_pk PRIMARY KEY(c, b, a)
                                );
                                 
                                INSERT INTO t (a, b, c)
                                SELECT LEVEL AS a, LEVEL * 2 AS b, LEVEL * 3 AS c
                                FROM DUAL CONNECT BY LEVEL <= 100000;
                                 
                                 
                                COMMIT;
                                 
                                SELECT COUNT( *) FROM t;
                                
                                COUNT(*)
                                --------
                                  100000 
                                
                                Elapsed: 00:00:00.015
                                Plan hash value: 3458248754
                                 
                                -----------------------------------------------------------------------------
                                | Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
                                -----------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT      |             |     1 |    53   (2)| 00:00:01 |
                                |   1 |  SORT AGGREGATE       |             |     1 |            |          |
                                |   2 |   INDEX FAST FULL SCAN| ONE_COL_UDX |   100K|    53   (2)| 00:00:01 |
                                -----------------------------------------------------------------------------
                                
                                   Statistics
                                -----------------------------------------------------------
                                               5  user calls
                                               0  physical read total multi block requests
                                               0  physical read total bytes
                                               0  cell physical IO interconnect bytes
                                               0  commit cleanout failures: block lost
                                               0  IMU commits
                                               0  IMU Flushes
                                               0  IMU contention
                                               0  IMU bind flushes
                                               0  IMU mbu flush
                                
                                ALTER TABLE T DROP CONSTRAINT three_col_pk;
                                
                                SELECT COUNT( *) FROM t;
                                
                                COUNT(*)
                                --------
                                  100000 
                                
                                Elapsed: 00:00:00.016
                                Plan hash value: 2966233522
                                 
                                -----------------------------------------------------------------------------
                                | Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
                                -----------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT      |             |     1 |    53   (2)| 00:00:01 |
                                |   1 |  SORT AGGREGATE       |             |     1 |            |          |
                                |   2 |   INDEX FAST FULL SCAN| ONE_COL_UDX |   100K|    53   (2)| 00:00:01 |
                                -----------------------------------------------------------------------------
                                
                                   Statistics
                                -----------------------------------------------------------
                                               5  user calls
                                               0  physical read total multi block requests
                                               0  physical read total bytes
                                               0  cell physical IO interconnect bytes
                                               0  commit cleanout failures: block lost
                                               0  IMU commits
                                               0  IMU Flushes
                                               0  IMU contention
                                               0  IMU bind flushes
                                               0  IMU mbu flush
                                I do notice that the Composite Primary Key, three_col_pk, is never used by the CBO; using on the Unique Key on Col1.
                                • 13. Re: Optimizer choosing wrong object on unanalyzed table
                                  John Spencer
                                  Purvesh:

                                  I'm not surprised that one_call_udx is used with the PK in place since a is declared not null because it is part of the PK. The one_col_udx index would obviously be the smallest of the indexes. However, I find it amazing that it uses it after dropping the PK.

                                  I am also a little surprised that the plan hash value changes without the PK in place. The first plan has a hash of 3458248754 while the second has 2966233522. Perhaps ther is something funky going on with the autotrace. Do you get the same results using the utlxpln script?

                                  John
                                  • 14. Re: Optimizer choosing wrong object on unanalyzed table
                                    blama
                                    Hi Purvesh,

                                    This is correct as long as the PK is in place, because the PK forces the a-column to have a value in every row.
                                    Because of this the CB can use either the a-UDX or the a-b-c-PK for counting. As the a-UDX uses less space and therefore less blocks, counting on it is done faster. Because it is faster, the CBO chooses it.

                                    But if you remove the PK it is not guaranteed that every row in the table will be in the a-UDX, so therefore counting the table-rows on it is not possible. Could you insert the additional row an see what the CBO does then?

                                    Thanks,
                                    Blama
                                    1 2 Previous Next