This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 2, 2013 1:09 AM by bencol Go to original post RSS
  • 15. Re: Index Choice when 2 indexes have similar columns
    bencol Pro
    Currently Being Moderated
    That's what I thought, I need to look into the reasons why we have both indexes (not my database of design decision). Using the RefNo,TmStamp,RefTypeSeqNo index to look up on RefNo,TmStamp only does not make sense for this table: It is a link table between document locations and the document owner table, which can be one or more of many - client, transaction, invoice etc. The RefNo without the RefType is meaningless.

    Ben
  • 16. Re: Index Choice when 2 indexes have similar columns
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    bencol wrote:
     
    ---------------------------------------------------------------------------------------------
    | 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.
    
    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.
    I don't think you've said which version of Oracle, but it looks like 10gR2.

    Note that the second plan calculates the index cost and rows, and the table cost based on an estaimate of 14 rows - which the optimizer has derived through the value of distinct_keys for the index. Oracle can do this because the predicates you have are an exact match for the index it is using. However, when it calculates the table cardinality it "forgets" the index cardinalilty and applies the old "product of selectivity" algorithm to the two columns.

    For the first plan, though, the columns are NOT an exact match for the index definition, so Oracle uses the product of column selectivities to work out all the costs and cardinalities - this happens to give a much lower cardinality on the index range scan so even though the index is fatter the cost of lower, and it also gives a much lower cost to the table access. In this case the difference in actual work done is not enormous - it's basically the impact of the larger index range scan, though the different order in which you collect the table rows could have various side effects in a more complex query. (Obviously the difference in costs for this step could cause dramatic differences in execution plan for a more complex query.)

    In 11g (even release 1) the optimizer will use the distinct_keys values from the exact index in both sets of calculations, and it will follow through to the table cardinality using the same distinct_keys calculation. As a side effect of the upgrade you may find lots of execution plans changing because (following the given example) the table cardinality estaimtes may increase sgnificantly - a factor of nearly 5 in the example.


    Update: I'd forgotten that I'd written up the basic problem on my blog a few years ago: http://jonathanlewis.wordpress.com/2008/03/11/everything-changes/
    Regards
    Jonathan Lewis

    Edited by: Jonathan Lewis on Mar 30, 2013 9:02 AM
  • 17. Re: Index Choice when 2 indexes have similar columns
    bencol Pro
    Currently Being Moderated
    Centinul,

    In the past we have done testing that suggests that the Reftno,TmStamp,RefType index gives better performance than one on RefNo,RefType,TmStamp.

    When we update the optimizer_features_enable value (see below), we'll look again.

    Ben
  • 18. Re: Index Choice when 2 indexes have similar columns
    bencol Pro
    Currently Being Moderated
    Johnathan,

    Thank you for the reply. I posted a lot of information and forgot the basic - schoolboy error:
    SQL> select * from v$version;
    
    BANNER
    ________________________________________________________________________________
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    However:
     
    select value
    from   v$parameter
    where  name = 'optimizer_features_enable';
    
    VALUE
    ______________________________
    10.2.0.4
    
    SQL> alter session set optimizer_features_enable = '11.2.0.3';
    
    SQL> explain plan for
         select *
         from   RefTable
         where  RefTypeSeqNo = :1
         and    RefNo = :2
         /
    
    SQL> @?\rdbms\admin\utlxpls
    
    PLAN_TABLE_OUTPUT
    _____________________________________________________________________________________________
    Plan hash value: 3714852595
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |               |    14 |   588 |     8   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| REFTABLE      |    14 |   588 |     8   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | REFTABLE_CX02 |    14 |       |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("REFNO"=TO_NUMBER(:2) AND "REFTYPESEQNO"=TO_NUMBER(:1))
    So this does appear to be the cause. We did have many problems with less "good", but more accurate plan when we upgraded to 11.2, so we set the optimizer_features_enable. We have plan to upgrade the value of optimizer_features_enable, but almost everything is working fine at the moment.

    Thank you for pointing me towards the v10/11 differences.

    Thank you everyone for looking at this and for the pointers.

    Ben
1 2 Previous Next

Legend

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