An example of where the optimizer should ignore a hint

March 13, 2014 15 Comments

Here’s an example of where the optimizer really should ignore a hint.

I’m testing an implementation of deliberately unusable index partitions – i.e. some unusable index partitions, some usable.

This is 11.2.0.3 so whilst there are enhancements in 12c, they’re no use here.

SQL> create table t1
  2  (col1 number)
  3  partition by list(col1)
  4  (partition p0 values(0),
  5   partition pdef values(default));

Table created.

SQL> create index i1 on t1 (col1) local unusable;

Index created.

SQL> alter index i1 rebuild partition pdef;

Index altered.

SQL> select * from t1;

no rows selected

SQL> select * from t1 partition (p0);

no rows selected

SQL> select /*+ index(t1 i1) */ * from t1 partition (p0);

no rows selected

SQL> select * from t1 where col1 = 0;

no rows selected

SQL> select /*+ index(t1 i1) */ * from t1 where col1 = 0;
select /*+ index(t1 i1) */ * from t1 where col1 = 0
*
ERROR at line 1:
ORA-01502: index 'I1' or partition of such index is in unusable state

SQL>

Filed under 11gR2, bug, cbo, indexes, partitioning

15 Responses to An example of where the optimizer should ignore a hint

  1. Matthias Rogel says:
    March 13, 2014 at 6:59 pm

    I beg to disagree.
    That’s definitely a user error.
    You are using a bad hint ( cmp. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1197547100346966797 )

    Never use bad hints.

    Reply
  2. Matthias Rogel says:
    March 13, 2014 at 8:02 pm

    Would you say that my C-Compiler should ignore this fault:

    main()
{
   char *programmersfault = 0;
   *programmersfault = "Dear C-Compiler, please ignore this fault";
}

    ?

    Reply
  3. Dom Brooks says:
    May 16, 2014 at 11:08 am

    Not a reply that that gives me a nice feeling.
    In my opinion, the expected behaviour is not appropriate.

    Development confirmed that this is an expected behavior as per Bug 6414957 – SKIP_UNUSABLE_INDEXES IS IGNORED IF INDEX HINT IS SPECIFIED .

    Details :
    ———

    SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with unusable indexes or index partitions. If a SQL statement uses a hint that forces the usage of an unusable index, then this hint takes precedence
    over initialization parameter settings, including SKIP_UNUSABLE_INDEXES. If the optimizer chooses an unusable index, then an ORA-01502 error will result.

    Reply
  5. Matthias Rogel says:
    May 16, 2014 at 5:11 pm

    seems we need a hint /*+SKIP_UNUSABLE_HINTS */ 🙂

    Reply

  6. Pingback: Reluctant Local Index | OraStory

Leave a comment