This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 2, 2013 1:09 AM by bencol RSS

Index Choice when 2 indexes have similar columns

bencol Pro
Currently Being Moderated
Long post, in summary: I have a table with 2 indexes, one on col1, col2 and the other on col1,col3,col2. If I run a query supplying values for col1 and col2 I would expect the 2 column index to be used, but it isn't. I'm guessing that the optimiser is basing its decision on the avg_data_blocks_per_key value because the table access cost differs in each query. But it is not (fully?) taking into account how this will change when one of the index columns is excluded:

It is possible to add an index hint in the code, but I do not like doing this as a matter of course, and when I do not understand what the optimiser is doing.

Now follows the details of the table and indexes.

user_indexes, showing expected differences in AVG_DATA_BLOCKS_PER_KEY
Note stats recently gathered on CX02 to see if this makes a difference, I am currently getting more temp space to do this on CX03, after this I expect the leaf_blocks & distinct_keys to be ~50% higher on CX03. The differences are pretty much what I'd expect though, CX03 is nearly unique and both the clustering factors make sense.
INDEX_NAME                    : REFTABLE_CX02               REFTABLE_CX03
INDEX_TYPE                    : NORMAL                      NORMAL
TABLE_OWNER                   : ACTD00                      ACTD00
TABLE_NAME                    : REFTABLE                    REFTABLE
TABLE_TYPE                    : TABLE                       TABLE
UNIQUENESS                    : NONUNIQUE                   NONUNIQUE
COMPRESSION                   : DISABLED                    DISABLED
PREFIX_LENGTH                 :                             
TABLESPACE_NAME               : ACT_INDEXES_X128M           ACT_INDEXES_X128M
INI_TRANS                     : 2                           2
MAX_TRANS                     : 255                         255
INITIAL_EXTENT                : 134217728                   134217728
NEXT_EXTENT                   : 134217728                   134217728
MIN_EXTENTS                   : 1                           1
MAX_EXTENTS                   : 2147483645                  2147483645
PCT_INCREASE                  : 0                           0
PCT_THRESHOLD                 :                             
INCLUDE_COLUMN                :                             
FREELISTS                     :                             
FREELIST_GROUPS               :                             
PCT_FREE                      : 10                          10
LOGGING                       : YES                         YES
BLEVEL                        : 3                           3
LEAF_BLOCKS                   : 6175698                     6159664
DISTINCT_KEYS                 : 76747169                    926267839
"AVG_LEAF_BLOCKS_PER_KEY       : 1                           1"
"AVG_DATA_BLOCKS_PER_KEY       : 10                          1"
CLUSTERING_FACTOR             : 773025434                   661852333
STATUS                        : VALID                       VALID
NUM_ROWS                      : 1508335135                  1054996402
SAMPLE_SIZE                   : 1508335135                  1054996402
LAST_ANALYZED                 : 27/03/2013 15:08:56         18/01/2012 02:01:11
DEGREE                        : 1                           1
INSTANCES                     : 1                           1
PARTITIONED                   : NO                          NO
TEMPORARY                     : N                           N
GENERATED                     : N                           N
SECONDARY                     : N                           N
BUFFER_POOL                   : DEFAULT                     DEFAULT
FLASH_CACHE                   : DEFAULT                     DEFAULT
CELL_FLASH_CACHE              : DEFAULT                     DEFAULT
USER_STATS                    : NO                          NO
DURATION                      :                             
PCT_DIRECT_ACCESS             :                             
ITYP_OWNER                    :                             
ITYP_NAME                     :                             
PARAMETERS                    :                             
GLOBAL_STATS                  : YES                         YES
DOMIDX_STATUS                 :                             
DOMIDX_OPSTATUS               :                             
FUNCIDX_STATUS                :                             
JOIN_INDEX                    : NO                          NO
IOT_REDUNDANT_PKEY_ELIM       : NO                          NO
DROPPED                       : NO                          NO
VISIBILITY                    : VISIBLE                     VISIBLE
DOMIDX_MANAGEMENT             :                             
SEGMENT_CREATED               : YES                         YES
Table & Index columns :
SQL> select * from user_ind_columns where index_name in('REFTABLE_CX02','REFTABLE_CX03') order by 1,4;

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                    COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
______________________________ ______________________________ ______________________________ _______________ _____________ ___________ ____
REFTABLE_CX02                  REFTABLE                       REFNO                                        1            22           0 ASC
REFTABLE_CX02                  REFTABLE                       REFTYPESEQNO                                 2            22           0 ASC
REFTABLE_CX03                  REFTABLE                       REFNO                                        1            22           0 ASC
REFTABLE_CX03                  REFTABLE                       TMSTAMP                                      2             7           0 ASC
REFTABLE_CX03                  REFTABLE                       REFTYPESEQNO                                 3            22           0 ASC

SQL> desc reftable
Name                                                              Null?    Type
----------------------------------------------------------------- -------- ------------
REFSEQNO                                                          NOT NULL NUMBER(10)
ACTIVITYSEQNO                                                     NOT NULL NUMBER(10)
REFTYPESEQNO                                                      NOT NULL NUMBER(10)
REFNO                                                             NOT NULL NUMBER(10)
HIDEIND                                                           NOT NULL NUMBER(10)
USID                                                              NOT NULL VARCHAR2(16)
TMSTAMP                                                           NOT NULL DATE
Plan with no hints, uses "wrong" index as the cost is "too low"
I also do not understand the access on both columns and the filter on the second.
SQL> explain plan for
  2  select *
  3  from   RefTable
  4  where  RefTypeSeqNo = :1
  5  and    RefNo = :2;

Explained.

SQL> @?\rdbms\admin\utlxpls
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |   126 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| REFTABLE      |     3 |   126 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | REFTABLE_CX03 |     3 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("REFNO"=TO_NUMBER(:2) AND "REFTYPESEQNO"=TO_NUMBER(:1))
       filter("REFTYPESEQNO"=TO_NUMBER(:1))
15 rows selected.
Plan with hint, which should be better
SQL> explain plan for
  2  select /*+INDEX (RefTable REFTABLE_CX02) */
  3         *
  4  from   RefTable
  5  where  RefTypeSeqNo = :1
  6  and    RefNo = :2;
Explained.
SQL> @?\rdbms\admin\utlxpls
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |   126 |    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| REFTABLE      |     3 |   126 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | REFTABLE_CX02 |    14 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("REFNO"=TO_NUMBER(:2) AND "REFTYPESEQNO"=TO_NUMBER(:1))

14 rows selected.
with actual queries
Firstly on wrong index - higher Logical reads
SQL> set autotrace traceonly explain statistics
SQL> select *
  2  from   RefTable
  3  where  RefTypeSeqNo = 0
  4  and    RefNo = 57748;

629 rows selected.

Statistics
__________________________________________________________
          8  recursive calls
          0  db block gets
       1156  consistent gets
        588  physical reads
      32828  redo size
      32709  bytes sent via SQL*Net to client
        811  bytes received via SQL*Net from client
         43  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        629  rows processed
Then using the hint:
SQL> select /*+INDEX (RefTable REFTABLE_CX02) */
  2         *
  3  from   RefTable
  4  where  RefTypeSeqNo = 0
  5  and    RefNo = 57748;

629 rows selected.

Statistics
__________________________________________________________
          0  recursive calls
          0  db block gets
        633  consistent gets
          0  physical reads
          0  redo size
      32709  bytes sent via SQL*Net to client
        811  bytes received via SQL*Net from client
         43  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        629  rows processed
Plans are the same as the predicted ones (above), so I have not included them here.

Thank you for your time,

Ben
  • 1. Re: Index Choice when 2 indexes have similar columns
    Centinul Guru
    Currently Being Moderated
    Out of curiosity can you run the the first (unhinted SQL) statement again and print the statistics please? I noticed that it had physical reads, while the second one didn't and the second one likely benefited on the caching of the first query so there isn't an accurate comparison between the two.

    Also depending on your Oracle version it may be more appropriate to do the following:
    SET SERVEROUTPUT OFF;
    
    SELECT /*+gather_plan_statistics*/
           *
    FROM   reftable
    WHERE  reftypeseqno = 0
    AND    refno        = 57748
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));
    Also, do you have any queries that ever use all three columns defined in the larger index?
  • 2. Re: Index Choice when 2 indexes have similar columns
    rp0428 Guru
    Currently Being Moderated
    One thing I noticed is that the index it selected has not been analyzed for over a year: 18/01/2012 02:01:11

    Can you bring the stats up to date and check the plans again?
  • 3. Re: Index Choice when 2 indexes have similar columns
    Iordan Iotzov Expert
    Currently Being Moderated
    This is a simple query, but the expected cardinality (3) is quite different from the actual cardinality (629). Why? Do you have skewed data/histograms? Are RefTypeSeqNo and RefNo correlated?

    Notice that the CBO expects to scan 3 records from REFTABLE_CX03, but 14 in REFTABLE_CX02. Very counterintuitive - REFTABLE_CX03 is “fatter” than REFTABLE_CX02?!? That discrepancy may explain the difference in the cost.

    I would recommend running 10053 trace.

    Iordan Iotzov
    http://iiotzov.wordpress.com/

    Edited by: Iordan Iotzov on Mar 27, 2013 12:13 PM
  • 4. Re: Index Choice when 2 indexes have similar columns
    rp0428 Guru
    Currently Being Moderated
    >
    I would recommend running 10053 trace.
    >
    Please clarify if you want OP to run the trace before or after updating the 2nd index stats. Those stats are a year old, unlike the first index stats which are current.
  • 5. Re: Index Choice when 2 indexes have similar columns
    Iordan Iotzov Expert
    Currently Being Moderated
    The stats for REFTABLE_CX03 were gathered when the table was approx. 70% of what it is now, so fresh stats gathering would be beneficial.
    It is possible that stats gathering does not fix the issue , in which case 10053 would be helpful.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 6. Re: Index Choice when 2 indexes have similar columns
    rp0428 Guru
    Currently Being Moderated
    >
    The stats for REFTABLE_CX03 were gathered when the table was approx. 70% of what it is now, so fresh stats gathering would be beneficial.
    It is possible that stats gathering does not fix the issue , in which case 10053 would be helpful.
    >
    I'm not questioning the use of the trace; just wanting to know if you prefer to see the trace using the existing stats or after current stats are generated.

    Operationally OP needs to use current stats but if you thought the trace might show something interesting for the existing stats OP has to do the trace first or the opportunity will be lost.
  • 7. Re: Index Choice when 2 indexes have similar columns
    Mohamed Houri Pro
    Currently Being Moderated
    First of all, I want to remember that the clustering factor(CF) represents an important information in the choice of the corresponding index. The index cost is
    Index cost = blevel 
                             + ceiling (leaf_blocks * effective index selectivity)                         
                             + ceiling (clustering_factor * effective table selectivity)
    In your case the clustering factor of REFTABLE_CX03 (661,852,333) is better than that of REFTABLE_CX02 (773,025,434)

    Second, these two indexes are not similar (as your post title is suggesting) as far as the second column of the first index is placed at the third position in the second index. This columns position plays an important role in the choice of the best index that matches to your query where clause which concerns the first leading two columns of REFTABLE_CX02 index
    where  RefTypeSeqNo = :1
    and    RefNo = :2;
    It’s seems clearly that REFTABLE_CX02 index is the adequate index to be used.

    According to what you have posted so far these two indexes have the same cost (cost = 4). In such cases the alphabetic order of the index might matters. But even in that case of alphabetic order REFTABLE_CX03 name comes after the REFTABLE_CX02 and should not have been selected.

    The best you can do is to post the plan taken from the memory (as already suggested by Centinul) for both queries together with their predicate part in order to see what access/filter operation are done on the indexes. The explain plan might sometimes lies (as all input bind variables are considered of a varchar2 type; by the way did you see the implicit TO_NUMBER conversion there?)

    Best regards
    Mohamed Houri
    www.hourim.wordpress.com
  • 8. Re: Index Choice when 2 indexes have similar columns
    bencol Pro
    Currently Being Moderated
    Centinul,

    It does run again without Physial reads, and logical reads go down, but this is just one example, out of 4k executions/hour. I'd still like CX03 to be used.

    This is the tracing and index details with the new stats, showing 0 physical reads for both and slightly fewer logical reads for my prefered index:

    And there are queries that use the TmStamp column, so CX02 is used.
    SQL> SELECT /*+gather_plan_statistics*/
      2         *
      3  FROM   reftable
      4  WHERE  reftypeseqno = 0
      5  AND    refno        = 57748
      6  ;
    
    629 rows selected.
    
    Execution Plan
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |               |     3 |   126 |     7   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| REFTABLE      |     3 |   126 |     7   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | REFTABLE_CX03 |     3 |       |     4   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("REFNO"=57748 AND "REFTYPESEQNO"=0)
           filter("REFTYPESEQNO"=0)
    
    Statistics
    __________________________________________________________
              0  recursive calls
              0  db block gets
            664  consistent gets
              0  physical reads
              0  redo size
          23332  bytes sent via SQL*Net to client
            811  bytes received via SQL*Net from client
             43  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            629  rows processed
    
    SQL> SELECT /*+gather_plan_statistics
      2            index(reftable REFTABLE_CX02)
      3         */
      4         *
      5  FROM   reftable
      6  WHERE  reftypeseqno = 0
      7  AND    refno        = 57748
      8  ;
    
    629 rows selected.
    
    Execution Plan
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |               |     3 |   126 |    15   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| REFTABLE      |     3 |   126 |    15   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | REFTABLE_CX02 |    14 |       |     4   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("REFNO"=57748 AND "REFTYPESEQNO"=0)
    
    Statistics
    __________________________________________________________
              0  recursive calls
              0  db block gets
            633  consistent gets
              0  physical reads
              0  redo size
          24940  bytes sent via SQL*Net to client
            811  bytes received via SQL*Net from client
             43  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            629  rows processed
    
    INDEX_NAME                    : REFTABLE_CX02           REFTABLE_CX03
    INDEX_TYPE                    : NORMAL                  NORMAL
    TABLE_OWNER                   : ACTD00                  ACTD00
    TABLE_NAME                    : REFTABLE                REFTABLE
    TABLE_TYPE                    : TABLE                   TABLE
    UNIQUENESS                    : NONUNIQUE               NONUNIQUE
    COMPRESSION                   : DISABLED                DISABLED
    PREFIX_LENGTH                 :
    TABLESPACE_NAME               : ACT_INDEXES_X128M       ACT_INDEXES_X128M
    INI_TRANS                     : 2                       2
    MAX_TRANS                     : 255                     255
    INITIAL_EXTENT                : 134217728               134217728
    NEXT_EXTENT                   : 134217728               134217728
    MIN_EXTENTS                   : 1                       1
    MAX_EXTENTS                   : 2147483645              2147483645
    PCT_INCREASE                  : 0                       0
    PCT_THRESHOLD                 :
    INCLUDE_COLUMN                :
    FREELISTS                     :
    FREELIST_GROUPS               :
    PCT_FREE                      : 10                      10
    LOGGING                       : YES                     YES
    BLEVEL                        : 3                       3
    LEAF_BLOCKS                   : 6175698                 9134810
    DISTINCT_KEYS                 : 76747169                1079885910
    AVG_LEAF_BLOCKS_PER_KEY       : 1                       1
    AVG_DATA_BLOCKS_PER_KEY       : 10                      1
    CLUSTERING_FACTOR             : 773025434               877810376
    STATUS                        : VALID                   VALID
    NUM_ROWS                      : 1508335135              1508684450
    SAMPLE_SIZE                   : 1508335135              754342225
    LAST_ANALYZED                 : 27/03/2013 15:08:56     27/03/2013 18:30:12
    DEGREE                        : 1                       1
    INSTANCES                     : 1                       1
    PARTITIONED                   : NO                      NO
    TEMPORARY                     : N                       N
    GENERATED                     : N                       N
    SECONDARY                     : N                       N
    BUFFER_POOL                   : DEFAULT                 DEFAULT
    FLASH_CACHE                   : DEFAULT                 DEFAULT
    CELL_FLASH_CACHE              : DEFAULT                 DEFAULT
    USER_STATS                    : NO                      NO
    DURATION                      :
    PCT_DIRECT_ACCESS             :
    ITYP_OWNER                    :
    ITYP_NAME                     :
    PARAMETERS                    :
    GLOBAL_STATS                  : YES                     YES
    DOMIDX_STATUS                 :
    DOMIDX_OPSTATUS               :
    FUNCIDX_STATUS                :
    JOIN_INDEX                    : NO                      NO
    IOT_REDUNDANT_PKEY_ELIM       : NO                      NO
    DROPPED                       : NO                      NO
    VISIBILITY                    : VISIBLE                 VISIBLE
    DOMIDX_MANAGEMENT             :
    SEGMENT_CREATED               : YES                     YES
  • 9. Re: Index Choice when 2 indexes have similar columns
    bencol Pro
    Currently Being Moderated
    rp0428,

    Stats gathered on both indexes, with similar results, see above.
  • 10. Re: Index Choice when 2 indexes have similar columns
    bencol Pro
    Currently Being Moderated
    Iordan,

    Having trouble getting the 10053 trace, I can generate 10046 tracing, using this method: http://www.oracle-developer.net/display.php?id=516 but get errors on the 10053/

    I will look into this with my DBAs

    The new stats are not changing the behaviour.

    Ben
  • 11. Re: Index Choice when 2 indexes have similar columns
    Centinul Guru
    Currently Being Moderated
    It looks like in this case both indexes performed nearly identically (664 logical reads versus 663).

    My recommendation would be to look at the combinations of the different WHERE clauses for these columns and build the indexes based on those needs. You're more than welcome to post those iterations here and someone should be able to suggest a good index layout.
  • 12. Re: Index Choice when 2 indexes have similar columns
    bencol Pro
    Currently Being Moderated
    Centinul,

    I need to run this for a larger range of values - the case I chose was just a random one. There are queries on RefNo, RefTypeSeqNo and RefNo, RefTypeSeqNo, TmStamp. There are probably about the same number of each, but the query on RefNo,RefType is showing up in the AWR report as a top event by Elapsed Time and averaging 1s per execution. The queries including the TmStamp are not showing up at all.

    I will run a longer test using a hint on the two column index, where only two columns are in the query.

    Thanks,

    Ben
  • 13. Re: Index Choice when 2 indexes have similar columns
    rp0428 Guru
    Currently Being Moderated
    >
    It does run again without Physial reads, and logical reads go down, but this is just one example, out of 4k executions/hour. I'd still like CX03 to be used.

    This is the tracing and index details with the new stats, showing 0 physical reads for both and slightly fewer logical reads for my prefered index:

    And there are queries that use the TmStamp column, so CX02 is used.
    >
    What? Please make up your mind.

    You now say you'd 'like CX03 to be used' but your original post shows it IS being used and you were complaining about that; you said you wanted CX02 to be used instead.

    And now you say 'there are queries that use the TmStamp column, so CX02 is used'. Then why does the code you posted show that 'TmStamp' is used in CX03? Why the switch? Do you have two different systems and the indexes are reversed on the two systems?

    Those new plans still show Oracle expects it only needs 3 rows from the CX03 index (this time that is the FIRST plan you posted, before it was the second plan). But it thinks it needs 14 rows from the CX02 index.

    What are the row counts that match the query predicate?
    WHERE  reftypeseqno = 0
      5  AND    refno        = 57748
    How many rows have 'reftypeseqno = 0'?

    How many rows have 'refno = 57748'?

    How many rows have the combination of the two values?
  • 14. Re: Index Choice when 2 indexes have similar columns
    Centinul Guru
    Currently Being Moderated
    bencol wrote:
    There are queries on RefNo, RefTypeSeqNo and RefNo, RefTypeSeqNo, TmStamp
    In that case I'd recommend a single index: (RefNo, RefTypeSeqNo, TmStamp)

    This should be able to satisfy both types of queries. Personally I'd need to see an analysis done (using a variety of production style queries) with TKPROF to prove that two indexes would be beneficial.
1 2 Previous Next

Legend

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