An example of where the optimizer should ignore a hint

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>
About these ads

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

  1. 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.

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

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

    ?

  3. Dom Brooks says:

    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.

  4. seems we need a hint /*+SKIP_UNUSABLE_HINTS */ :-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 75 other followers

%d bloggers like this: