Forum Stats

  • 3,767,754 Users
  • 2,252,713 Discussions
  • 7,874,327 Comments

Discussions

How to force the optimaizer to stop using an index ?

659979
659979 Member Posts: 4
edited Sep 21, 2008 4:36PM in SQL & PL/SQL
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.

Therfore I have 2 options:
1. drop the index - but this is too risky as if i will need to recreate it , it will last forever and will result in long application downtime which I can not afford.
The test and Dev environments does not contain ammounts of data like the prodicution so its hard to predict from these environments.

2. the second option is Modify the index columns statistics (which I have done) giving it low NDV (number of diftinct values) -- but sadly this did not work.
when cheking event 10053 I see a the line "Using prorated density: [ 1/(2*num_rows) ] of col #n as selectivity of out-of-range value pred" which I guess meens the High value of the column is lower then the value given it the query ( I do not run statistics every day , the current statistics for all columns (execpt this one ) suits best and I do not want to handle plan changes . also it will take a lot of time to gather table stats on daily basis.

So - to make a long story short - how do I force Oracle to avoid using an index but continue builing it and without dropping it?
I can not use the 'NO_INDEX' hint as it will requre change in many Applications.
I don need something like NO_INDEX hint on system level.

I am using Oracle 10g Enterprise edition.
the optimaized_mode is set to COST BASED.


Thanks in advance for any help resolving this complicated issue.

Regards,
Amit Zor.
ADBA.
Tagged:
«1

Answers

  • 337410
    337410 Member Posts: 483
    Hi,

    no_index

    please see this similar discussion 2739162
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    You can use NO_INDEX hint
    SQL>  create table t
      2   as
      3   select level no, rpad('*',100,'*') name
      4     from dual
      5  connect by level <= 100000
      6  /
    
    Table created.
    
    SQL> create index i on t(no)
      2  /
    
    Index created.
    
    SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
    
    PL/SQL procedure successfully completed.
    
    SQL> set autotrace traceonly explain
    
    SQL> select * from t where no = 25000
      2  /
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=105)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=105)
       2    1     INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=1 Card=1)
    
    
    
    SQL> select /*+ NO_INDEX(t i) */ * from t where no = 25000
      2  /
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=350 Card=1 Bytes=105)
       1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=350 Card=1 Bytes=105)
    Thanks,
    karthick.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Additionally you can upgrade to 11g and use [url http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema.htm#CNCPT1895]invisible indexes which does exactly what you want.

    Regards,
    Rob.
  • 221158
    221158 Member Posts: 154
    Disable the index.

    Christopher Soza
    Oracle BI DBA
    Orix Consultancy Services Ltd
    b: http://sozaman.blogspot.com
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    I hope we can disable only function based index using the DISABLE statement. Correct me if iam wrong.

    Thanks,
    Karthick.
  • 561093
    561093 Member Posts: 2,146
    Hi,

    The best method is to use "NO_INDEX" hint.

    Alternatively, you may try the following (not so good) method:
    SQL> drop table t purge;
    
    Table dropped.
    
    SQL> create table t ( a number);
    
    Table created.
    
    SQL> create index t_idx on t(a);
    
    Index created.
    
    SQL>
    SQL> insert into t select level from dual connect by level <=100;
    
    100 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    SQL> exec dbms_stats.gather_table_stats(user, 't', cascade => true, method_opt => 'for all columns size 1');
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> set autotrace traceonly exp
    SQL>
    SQL> select * from t where a = 1;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2946670127
    
    --------------------------------------------------------------------------
    | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |       |     1 |     3 |     1   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| T_IDX |     1 |     3 |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("A"=1)
    
    SQL>
    SQL> alter table t move;
    
    Table altered.
    
    SQL>
    SQL> select * from t where a = 1;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T    |     1 |     3 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("A"=1)
    
    SQL>
    SQL> set autotrace off
    SQL> select status from user_indexes where index_name = 'T_IDX';
    
    STATUS
    --------
    UNUSABLE
    
    SQL> alter index t_idx rebuild;
    
    Index altered.
    
    SQL>
    Regards

    Asif Momen
    http://momendba.blogspot.com
  • 659979
    659979 Member Posts: 4
    Hello all.

    Thanks for responding so quickly , very much appriciated.

    A few comments :
    1. regarding the use of "NO_INDEX" hint , as I mentioned , I will have to go over all application's queries (and there are many) and modify each one of them , this is a lot of work that I am traying to prevent on a quicker solution.

    2. Regarding the upgrade to 11g - might be a good solution but this is not posible as we do not plan to upgrade right now - and such upgrading have many other consequnces other then my issue.

    3.Regarding the disable solutions - if I understand correctly you mean to make the index unusbale - this will cause queires trying to run on the index to fail throwing the index unusable state error.



    any other possible solutions ?

    Regards,
    Amit Zor,
    ADBA
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    For #3, you can set SKIP_UNUSABLE_INDEXES to avoid getting an error if a query wants to use an unusable index. You can set this at the system level if you would like.

    How many distinct SQL statements are using the index? In theory, you could apply a SQL profile to each statement that included the NO_INDEX hint. That wouldn't require any code changes, but it could be a fair amount of work if there are a large number of distinct SQL statements involved.

    Justin
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Sep 16, 2008 8:16AM
    user3698066 wrote:
    Hello all.

    3.Regarding the disable solutions - if I understand correctly you mean to make the index unusbale - this will cause queires trying to run on the index to fail throwing the index unusable state error.
    You could overcome these errors by setting "skip_unusable_indexes = TRUE" if you're still on 9i. In 10g this is already the default setting. You can set this on instance level, or on session level e.g. using a logon trigger.

    But keep in mind that setting the index to unusable means that you need to rebuild the index at some point as it is no longer maintained. My understanding was that you want to avoid any rebuilding processes.

    If the SQL generated by your application is sharable (i.e. uses bind variables) then you could set the indexes to unusable on your non-production environment, run your application using the "create_stored_outlines" parameter to create stored outlines for the SQLs and transport the outlines then to your production environment, provided that the plans generated on your non-production environment do not differ too much from your production environment.

    You could try to get as close as possible to your production environment by importing the table/index statistics into your non-production environment.

    Generating SQL profiles as mentioned by Justin would be the preferable option since it is less intrusive than stored outlines but I'm not sure if you can automate the profile generation in a similar way.

    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/

    Edited by: Randolf Geist on Sep 16, 2008 2:14 PM

    Added the statistics related information
    Randolf Geist
  • 659979
    659979 Member Posts: 4
    Hello again,

    Justin , There are hundreds of distinct queries as the table contains many columns and all of them use the "problematic" column (date column) as it is partitioned by it and all indexes are local.
    (BTW - the statistics the optimaizer is using are global stats as the query runs on more then one date)

    I think the idea of importing the statistics from production to test might be a good idea as I can afford downtime on this environment and as I see there is no "magic" solution in which one can tell the optimaer to ignore an Index on system level and for all sessions.

    thank you very much for your time and help,
    If by any chance anybody comes up with a quicker solution , like changing some parameter or some kind of system trigger I will be very glad to here about it.


    Thanks.
    Regards,
    Amit zor.
This discussion has been closed.