Unexpected Index Used
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Unexpected Index Used

  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Unexpected Index Used

    I am having some trouble with a query running in Oracle 11. An index was created to improve the performance of the query. However the database seems to be determined to use the primary key index.

    Some of the table structure.

    FOO_BAR_TABLE
    InstanceKey varchar2(255),
    CreateDateTime date,
    ObjectClass varchar2(96),
    AssignedTo varchar2(128),
    AssignmentElapsedTime number(18),
    ObjectData BLOB

    NDX_PK on FOO_BAR_TABLE(InstanceKey)

    NDX_EXTRACT on FOO_BAR_TABLE(CreateDateTime, InstanceKey)

    The query executing is a 3rd-party generated query based on a couple of parameters and the NDX_EXTRACT index was created to help the query. Since it is 3rd-party generated there is not an easy way to add hints to recommend the NDX_EXTRACT index.

    select count(InstanceKey)
    from FOO_BAR_TABLE
    where InstanceKey like 'HISTORY-FOO-BAR%'
    and CreateDateTime >= to_date('01/21/2013 12:00:00 am', 'mm/dd/yyyy hh:mi:ss am')
    and CreateDateTime <= to_date('01/22/2013 11:59:59 pm', 'mm/dd/yyyy hh:mi:ss am')
    and InstanceKey like 'HISTORY-FOO-BAR FOO-BAR-OBJECT PFIX%'

    However when the explain plan is request on this query the NDX_PK is determined the best option. One additional note, the table currently has little over 34 million records.

    I would like to understand the following.
    Why is the database consistently recommending the NDX_PK index instead of the NDX_EXTRACT index?
    Is there something in the database that is missing or needs to be done in order to change the recommendation?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jasoncollum View Post
    I am having some trouble with a query running in Oracle 11. An index was created to improve the performance of the query. However the database seems to be determined to use the primary key index.

    Some of the table structure.

    FOO_BAR_TABLE
    InstanceKey varchar2(255),
    CreateDateTime date,
    ObjectClass varchar2(96),
    AssignedTo varchar2(128),
    AssignmentElapsedTime number(18),
    ObjectData BLOB

    NDX_PK on FOO_BAR_TABLE(InstanceKey)

    NDX_EXTRACT on FOO_BAR_TABLE(CreateDateTime, InstanceKey)

    The query executing is a 3rd-party generated query based on a couple of parameters and the NDX_EXTRACT index was created to help the query. Since it is 3rd-party generated there is not an easy way to add hints to recommend the NDX_EXTRACT index.

    select count(InstanceKey)
    from FOO_BAR_TABLE
    where InstanceKey like 'HISTORY-FOO-BAR%'
    and CreateDateTime >= to_date('01/21/2013 12:00:00 am', 'mm/dd/yyyy hh:mi:ss am')
    and CreateDateTime <= to_date('01/22/2013 11:59:59 pm', 'mm/dd/yyyy hh:mi:ss am')
    and InstanceKey like 'HISTORY-FOO-BAR FOO-BAR-OBJECT PFIX%'

    However when the explain plan is request on this query the NDX_PK is determined the best option. One additional note, the table currently has little over 34 million records.

    I would like to understand the following.
    Why is the database consistently recommending the NDX_PK index instead of the NDX_EXTRACT index?
    Is there something in the database that is missing or needs to be done in order to change the recommendation?
    Couple of questions:

    1- Why two "InstanceKey like..." conditions on the predicate?
    2- Have you gathered statistics on NDX_EXTRACT index?
    3- Are there fresh statistics for the FOO_BAR_TABLE?
    4- Have you tried an index only on CreateDateTime column?
    5- How many rows out of the 34M rows are expected to be counted?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2013
    Posts
    1
    please post formatted EXPLAIN PLAN

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    "Since it is 3rd-party generated there is not an easy way to add hints to recommend the NDX_EXTRACT index."

    you can run it in sqlplus with and without the hint to see what the difference is in time and cost. Specifically consistent gets and physical reads. You should do set timing on and set autotrace on to get time and explain plan. Once you understand which is faster you will be closer to understanding why Oracle is using the index that it is using. You should also look at stats as PAVB suggested. It might be that your index is less efficient, or that there is something about the way the query is written that prevents Oracle from using the index that you want.
    this space intentionally left blank

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