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!

Non-value added indexes

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Jan 2, 2018.

  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi All,

    I got a list of the INDEXES from DBA Team which are non value added to the Database with the following conditions

    Quote:

    1:Low selectivity indexes.
    2:The number of distinct key values is less than 10 and the number of rows in table in greater than 100000.
    3:All these normal(Balances -Tree )indexes.

    They are asking the App Team to review these and to drop those indexes.
    But when I analyzed those indexes some of the columns are using in the where conditions of the so many queries.
    After applying the HINT of those indexes also the cost of query was decreasing.

    Here my confusion was,if we really drop those indexes, will it increase the performance?
    or
    Do we need to convert those as BITMAP as they have low cardinality ?

    Please help me to understand the concept behind this .

    Thanks
    Revathi.T
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,599
    Likes Received:
    363
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "Here my confusion was,if we really drop those indexes, will it increase the performance?"

    Maybe, maybe not. Without seeing a 10053 trace on queries that COULD use one or more of those indexes it's impossible to say since the optimizer may be using the index statistics for path elimination; dropping the indexes could drastically alter execution paths for the worse.

    Your DBA team has more research to do before asking to drop indexes.
     
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi David

    Thanks for your in puts .As per your comments in this scenario also (LOW Cardinality) we can't say that existing NORMAL INDEX are no more use full.

    They need more analysis for this .

    But one more point if we have more indexes on one table m one column it self , then there may be possibility to decrease the performance while performing the DML operation on that table.?
    Thanks
    Revathi.T
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,599
    Likes Received:
    363
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Normally one creates indexes with thought as to how they will be used; it's probably a good idea to evaluate which index is best for a given column and retain that index. With later releases of Oracle different index scanning methods are available which makes the antiquated process of having multiple indexes on the same columns although in a different order unnecessary (for example index1 on (a,b,c), index2 on (b,a,c), etc). And sometimes it's worth the possible change in plans to drop an index that doesn't provide any benefit or one that duplicates an indexed column set in whole or in part; each index adds overhead to inserts, updates and deletes and the less work Oracle needs to do to maintain indexes shows up in better performance overall.

    This is not a simple topic to address; again it needs much more work before any recommendations can be made and 'low cardinality' isn't valid criteria in my opinion.