Forum Stats

  • 3,784,122 Users
  • 2,254,894 Discussions
  • 7,880,697 Comments

Discussions

How to force the optimaizer to stop using an index ?

2»

Answers

  • 660101
    660101 Member Posts: 2
    In my opinion the best way to change optimizer execution plan is to use hints. You can use /*FULL (table)*/ or maybe you need index fast full scan /*INDEX_FFS (table)*/. If that index is bad for all of the queries you should drop it and create better index if cardinality is less than 1% use bitmap index. If you have for one date more than 100 record think about bitmap index. But if your table is partitioned by that column maybe you don't need index at that column at all, especially if you partition that table on column for example on monthly period and in your query you have in where clause monthly period.

    Regards
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    user3698066 wrote:
    Hello,
    I have an index on a very large table and I want to see how the applications running on this DB handles a situation in which this index does not exists.

    2. the second option is Modify the index columns statistics (which I have done)
    I'm still wondering if there is a way of modifying the statistics of the index in such a way so that it is not used by the optimizer.

    Since the main contributors of the formula for an simple b-tree index access path are the height of index, the number of leaf blocks and for the table access the clustering factor, may be you could come up with all those values bumped up to very large values for that particular index.

    Unfortunately the only component of the formula that is not multiplied by a factor is the height of the index, which is sanity checked (at least in 10.2) when set manually and is limited to a (already quite unreasonable) 255, so you can't set it greater than 255, but the remaining attributes can be set more or less to whatever you want.

    So you could come up with an manual modification of the index statistics like this. Note that I don't modify the NUM_ROWS and DISTINCT_KEYS values in an attempt to limit the risk of unwanted side effects of this modification.
    begin dbms_stats.set_index_stats(
    ownname=>'<index_owner>,
    indname=>'<index_name>', 
    indlevel=>255,
    numlblks=>1000000000,
    clstfct=>1000000000,
    avglblk=>1000000000/<num_distinct>,
    avgdblk=>1000000000); 
    end;
    /
    In a quick test I was successfully able to prevent a very selective index access path using these settings, but depending on your remaining statistics and the access paths chosen by the optimizer this might not work for you, at least not in all possible cases, e.g. an index unique scan won't cost more than 256 even using these settings because only a single leaf block by definition has to be visited in this case.

    Of course I haven't checked thoroughly what other side effects this statistics fudging does have, so use at your own risk resp. test it in your non-production environment before even thinking about applying it to an production system.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 659979
    659979 Member Posts: 4
    Hi Rendolf,
    Thank you very much for your solution.
    I am happy to say that it worked !!!

    I set the index Blevel to 255 and the optimizer stopped using it.
    Now I can chec then index usage for a couple of weeks and once confirmed that no application use it I will be able to drop it.


    Thanks again.
    Amit
This discussion has been closed.