1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Access for composite indexes not complete

Discussion in 'SQL PL/SQL' started by hrbkey, Apr 9, 2009.

  1. hrbkey

    hrbkey Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I am having a problem with getting the full use of composite indexes.

    This is a very large OLTP database with about 1 billion rows partitoned by day (about 4 million a day) A typical query includes several range queries. The columns typically contain very large number of distinct fields and are histogramed. Several composite indexes have been built and the statistics are up to date. Predominantly one table is queried against and 75 columns are returning for each of the 100 to 100k records.

    A typical composite index will include for example (min_value, max_value) called INDEX_MIN_MAX. A typical query will include in the where clause (min_value >= 2900.0) and (max_value <= 3000.0);


    In the explain plan my composite index will usually be used but in the predicate information the access will show (min_value >= 2900.0) with a filter (max_value <=3000.0). I would expect both min_value and max_value expression to be used in the access. Because of this access statement, the number of rows estimated and accessed is much larger than it should be if it used both values.

    I appreciate any thoughts.
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Can you post the full explain plan and tkprof output if any ?

    Edit: Also post the query.
     
  3. hrbkey

    hrbkey Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Unfortunately this database is on a closed network so I can't get cut and paste the information in.
    I also apologize for the generic nature of the information. Barring any typos..


    The sql is as follows:
    Code (Text):

    SELECT i1.min_value, i1.max_value, i1.var3, i1.var4, i1.var74, i1.var75
      FROM db1.table1 i1
     WHERE (i1.min_value >= 2900.0)
       AND (i1.max_value <= 3000.0)
       AND (i1.x_min >= 1238716800 AND i1.x_max <= 1239148799)
       AND ROWNUM <= 30000;
     
    The explain plan is as follows
    Code (Text):

    ID -Operation------------------Name------------Rows------Bytes----Cost------(%CPU)
    ==================================================================
    0 Select Statement-------------------------------30K----3955K----287K(1)--00:14:34
    1 -Count Stopkey
    2 --PX Coordinator
    3 --- PX Send QC (Random)--:TQ10000 ------1340K---172M---287K(1)---00:14:34
    4 ----Count Stopkey    
    5 -----Filter
    6 -----PX Partition Range All
    7 ------Table Access by----------------------------1340K-----172M---287K(1)---00:14:34
    ---------Local index Row Id
    8 ------- Index Range Scan--INDEX_MIN_MAX---10M -------------6359(2)--00:00:20

    Predicate info
    --------------

    1 Filter(Rownum <=30K)

    2 Filter(TO_Number(SYS_context('LBAC$0_LAB',LBC$Minlabel'))<= TO_Number(SYS_Context('LBAC$0_LAB',LBC$Maxlabel'))

    4 Filter(Rownum <=30K) 

    5 Filter(TO_Number(SYS_context('LBAC$0_LAB',LBC$Minlabel'))<= TO_Number(SYS_Context('LBAC$0_LAB',LBC$Maxlabel'))

    7 Filter ("x_min" >= 1238716800 and "x_max" <= 1239148799 and "SECLAB">=TO_NUMBER(SYS_context('LBAC$0_LAB',LBC$Minlabel')) AND
      "SECLAB"<=TO_NUMBER(SYS_context('LBAC$0_LAB',LBC$Maxlabel')) and TO_Number("SECLAB">=TO_NUMBER(SYS_context('LBAC$0_LABELS',TO_CHAR("SECLAB")) >=0)

    8 Access ("min_value" >= 2900.0 and min_value is NOT NULL)
      Filter("max_value <= 3000.0)
     
    Of note: the actual count for (min_value >= 2900.0 and max_value <= 3000.0) is 863K. Actual count of (min_value >= 2900.0) is 58Million.

    The cluster factor for INDEX_MIN_MAX = 2.5 million.
     
  4. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    This is what you intend to change

    There isn't many ways to change this. Oracle does a lot of permutation and combination for deciding the access path and chooses what seems best to it ( A 10056 trace will show you this)
    What you can do here is to partition the table on the said column and restrict the io to the needed partitions only.
     
  5. hrbkey

    hrbkey Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the advice.

    Unfortunately, there are several other pairs of columns that are used in other composite indexes and are also often used in the where clause. Since its not using both columns in the access, the estimated number of rows is much too large. Because of this, often the optimizer is picking the wrong index. I'll look into the 10056 trace to see if it provides any other clues.
     
  6. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    In case, the optimizer is picking up the wrong index, you can try out the index hint.
    Do post the tkprof output of the 10056 trace you are going to take.
     
  7. hrbkey

    hrbkey Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I found a work around that helps in many query options, but not all my query options. For the example that I provided, if I change my where clause to include:
    Code (SQL):
    (min_value BETWEEN 2900.0 AND 3000.0 AND max_value BETWEEN 2900.0 AND 3000.0)
    , I get the desired results that I want from the composite access.

    However, I also I have an second option for users to query
    Code (SQL):
    (min_value <= 3000.0 AND max_value >=2900.0)
    . As opposed to the previous query in which both value needed to fall in the 2900 to 3000.0 range. The second query option returns any pair of values that overlap with the provide range. So I'm still looking for a improvement to my second query option.

    Below I've included the trace info for the where clause that I included earlier. I am not confident that I am providing you the information you asked.


    For the the non-recursive statement

    Thanks for any insights you may have.
     
  8. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Tell me, Are you getting any marked improvement in the response time from the workaround you have applied ?
     
  9. hrbkey

    hrbkey Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    My results are mixed depending on the query, but I'm still investigating.

    Prior to this change the optimizer's number of rows estimates were too large. Because of this, it would only use one of the composite columns in its access and estimate. This often caused the optimizer to pick the wrong index to use.

    Now with this change, the optimizer's estimate is much too small. Again often this causes the optimizer to pick the wrong index. I can't understand why the estimate is incorrect. If I do an actual count on exactly what is in the access, the value returned is much larger. (i.e. the estimate is 20K rows the actual is 660k rows.) If I want the optimizer to pick the best index(s) to use, it appears that I should not use composite indexes for range queries like I showed previously. The optimizer estimates for the single index are good. When the correct index is used, the performance is much better.

    I can't use hints effectively, other than experimenting, because the application allows users to select up to 30 different columns to query on. The best index is very dependent on which parameters are selected and what values are used.
     
  10. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    for this,


    you can try with this
    See if this is any better