This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Aug 3, 2012 12:52 PM by Purvesh K RSS

Optimizer choosing wrong object on unanalyzed table

blama Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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