Use of Index on Partitioned Table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Use of Index on Partitioned Table

  1. #1
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152

    Use of Index on Partitioned Table

    DB version 9.2.0.4.0 on Sun Solaris

    I have a table BALANCE which partitioned on a date column and has other local indexes which start with the date column e.g.

    Index1:
    Date_col
    Reason_code
    ...

    I do a simple query such as:

    select * from balance where date_col = '31-mar-06'

    and the query plan shows it is using Index1. This is extremely inefficient: since the table is partitioned on date_col, it is obviously much more effective just to do a full table scan of the partition than use an index. If I add a FULL hint to query, performance improves dramatically.

    To get stats on the tables, I used the dbms_stats package:

    Code:
    begin 
    dbms_stats.gather_table_stats( 
    OWNNAME => 'XXX', 
    TABNAME =>'BALANCE', 
    PARTNAME =>'XXX',
    METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1',
    ESTIMATE_PERCENT => 20,
    GRANULARITY => 'ALL',
    CASCADE => TRUE );
    end;
    It looks as if DBMS_STATS is not really understanding that the table is partitioned and is not coming up with plans which utilize the partitioning properly. Is there any way to get better stats and plans ?

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Use histograms..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    I thought I already was. Isn't that what the "FOR ALL INDEXES COLUMNS" does ?

  4. #4
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    I just noticed it using SIZE 1 on the FOR ALL COLUMNS. It was a script which I someone else wrote. I'll try it with a larger size and see if that helps.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    use 254..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How many values of date_col do you have per partition? Not as many as 254, I expect.

    How about just dropping the index?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    If it was up to me, most of the indexes on this table would either go or be radically altered. Unfortunately, it isn't and there is no way I am going to be able to pursuade the powers that be to take the risk of dropping any indexes.

    There weren't many values of the date_col on this table, so I retried with a size of 5. But then I discovered something really weird. If I do my query as:

    Code:
    SELECT * FROM BALANCE
    WHERE  REASON_CODE='O'
    AND DATE_COL='31-mar-06'
    it does indeed change the plan to full table scan. But if I have it as:

    Code:
    SELECT * FROM BALANCE
    WHERE  REASON_CODE='O'
    AND  DATE_COL=TO_DATE('2006/03/31', 'YYYY/MM/DD')
    it goes back to index range scan ! Unfortunately, the format I have to use is the second one because the query is being issued by a third party product which isn't able to pass a date in Oracle format.
    I can only think that having to to_date confuses the optimizer and it can't associate it correctly with a partition.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    is that a range part or list part?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    It's a range partition.

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    Scorby,

    Interesting.. I'm guessing you are using or at least the index in this case is a local partitioned index.

    How are you getting the "range scan".. Yes a range scan happens, and is reported through sqlplus using "Set autotrace on". However running an "explain plan for" SQL and then querrying the plan table will get you all the details.
    In my tests (similar to yours) I found that the explain plan indicated partition elimination was occuring and the index was selected to access the table.

    Now if the index has low cardinality, then it may be best to drop it.

    To alter the execution plan you could utilize Stored Outlines.
    check the docs for "database performance tuning"

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width