Re: Why would an index not be used if specified as a hint in a query?

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Sat, 12 Feb 2005 21:14:02 -0000
Message-ID: <420e7191$0$19164$cc9e4d1f@news-text.dial.pipex.com>

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1108235973.485860_at_yasure...
> Niall Litchfield wrote:
>
>> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
>> news:1108185875.191030_at_yasure...
>>
>>>Craig & Co. wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>Running Oracle 8.1.7.4 on Solaris 2.8.
>>>>
>>>>Running explain plan, and then a query (I don't have a copy to show at
>>>>this
>>>>point), apparently
>>>>the index is not used.
>>>>
>>>>I assume it is because the optimiser thinks the index is not required
>>>>even
>>>>though it is specified.
>>>>
>>>>Cheers
>>>>Craig.
>>>
>>>A reasonable presumption.
>>>
>>>On the other hand it could be lack of statistics for the optimizer or
>>>any one of a number of things. Small matters like the number of rows
>>>in the table, the applicability of the index to the query, the
>>>cardinality, and the percentage of rows to be returned could also be
>>>affecting the outcome.
>>
>>
>> A reasonable presumption indeed, but an incorrect one - assuming I
>> understand 'not required' correctly - equally only one of your factors
>> should be relevant.
>>
>> Using an INDEX hint *WILL* cause the index to be used *IF IT CAN BE*
>
> Let's not turn into a testosterone contest but the OP wrote: "... the
> index is not required even though it is specified." which indicates to me
> that a hint was tried. Otherwise how could you "specify" an index?

I too assumed that we were talking about hints. Unfortunately I pressed send instead of save as draft when my daughter came to suggest that it was breakfast time not computer time. Thus you got a half finished all capitalized post that doesn't explain anything. Not my finest hour.

It was the word 'required' that got me wondering. I read the original question (like your comments about cardinality etc) as meaning that Oracle had decided to 'ignore' the hint and use a different path. Oracle won't ignore valid, syntactically correct hints (otherwise really what would be the point of them?)

So reasons that Oracle might choose to not use a specified index

The syntax is wrong - I nearly always go wrong by specifying the table name instead of the alias in hints. We'd obviously need DDL and the query to verify this.
The index cannot actually be used to return correct results (null values and b*tree indexes for example).
The index cannot be used because the leading column isn't specified in the where clause (though skip scan may change this).

> Assuming, thereupon, that the index hint was used and ignored by Oracle,
> it may be that the index was not appropriate for the query ... for
> example an incorrect column order ... it may be that the OP's query was
> for 85% of the rows in the table ... or, I think, the other items I
> mentioned. If under those conditions they are incorrect I'd appreciate
> knowing why.

The other items that you mentioned, the proportion of rows (or rather blocks) expected by the optimiser to be visited to satisfy the query for example, are excellent reasons for the CBO not to use an index in the absence of a hint - i.e. there is a plan that looks as if it will do less work than the indexed access path. If there is a valid index hint though you are telling the optimizer to use that index if at all possible and regardless of cost. It will therefore use it if it can.

> But given that we've not seen the SQL, not seen the explain plan, and
> not seen the DDL for the table or index(es) I'd say we were both
> shooting in the dark. I would, though, like to know why my statements
> would be incorrect if, as I did, it is assumed a hint was used.

I cannot offer reasons why the index isn't used in this particular case, but in general it will either be that the hint is invalid or the index cannot actually be used to satisfy the query. As it happens we had a very similar question almost exactly 2 years ago http://tinyurl.com/55rgo is the reference.

I do have a little example I just cooked up on nullability, in this case the index would be chosen by the CBO if it could be used, however it would be quite common in the initial stages to wonder why my index on owner was being ignored and to try hinting it.

SQL> conn /
Connected.
SQL> create table t1 as select * from all_objects;

Table created.

SQL> desc t1;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

note the nullable columns

SQL> create index idx1
2 on t1(owner);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=> true,estimate_percen
t=>10);

PL/SQL procedure successfully completed.

get some stats

SQL> explain plan
  2 for
  3 select owner,count(*)
  4 from t1
  5 group by owner;

Explained.

SQL> set lines 120
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

Plan hash value: 1744937475

|   0 | SELECT STATEMENT   |      |    21 |   126 |   153   (4)| 00:00:02 |
|   1 |  SORT GROUP BY     |      |    21 |   126 |   153   (4)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T1   | 47940 |   280K|   149   (1)| 00:00:02 |
---------------------------------------------------------------------------

9 rows selected.

here you might be thinking shouldn't it just use the index on owner? but the hint is 'ignored' because I can't use the index to answer the query.

SQL> explain plan
  2 for
  3 select /*+ INDEX(T1 IDX1) */ owner,count(*)   4 from t1
  5 group by owner;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

Plan hash value: 1744937475

|   0 | SELECT STATEMENT   |      |    21 |   126 |   153   (4)| 00:00:02 |
|   1 |  SORT GROUP BY     |      |    21 |   126 |   153   (4)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T1   | 47940 |   280K|   149   (1)| 00:00:02 |
---------------------------------------------------------------------------

9 rows selected.

add one of those pesky constraints that we don't need 'because we do all that in the app

SQL> alter table t1
2 modify owner not null;

Table altered.

SQL> explain plan
  2 for
  3 select /*+ INDEX(T1 IDX1) */ owner,count(*)   4 from t1
  5 group by owner;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

Plan hash value: 206034737

|   0 | SELECT STATEMENT     |      |    21 |   126 |   112   (1)| 00:00:02 
|
|   1 |  SORT GROUP BY NOSORT|      |    21 |   126 |   112   (1)| 00:00:02 
|
|   2 |   INDEX FULL SCAN    | IDX1 | 47940 |   280K|   112   (1)| 00:00:02 
|
-----------------------------------------------------------------------------

9 rows selected.

and to show that invalid hints get ignored but valid ones get used even when there is a better plan (i.e the cardinality, proportion or rows etc is not taken into account

SQL> explain plan
  2 for
  3 select object_name from t1
  4 WHERE OWNER IN ('SYS','SYSTEM','CTXSYS'); Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

Plan hash value: 1931397137

| 0 | SELECT STATEMENT | | 6256 | 189K| 150 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T1 | 6256 | 189K| 150 (2)| 00:00:02 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter("OWNER"='CTXSYS' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')

13 rows selected.

first I mistype my hint to try and get the index used

SQL> EXPLAIN
2 PLAN FOR
3 SELECT /*+ INDEX(T IDX1) */ OBJECT_NAME FROM T1 4 WHERE OWNER IN ('SYS','SYSTEM','CTXSYS'); Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

Plan hash value: 1931397137

| 0 | SELECT STATEMENT | | 6256 | 189K| 150 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T1 | 6256 | 189K| 150 (2)| 00:00:02 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter("OWNER"='CTXSYS' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')

13 rows selected.

next I get the hint right and even though I'm returning nearly all rows and so the FTS is better
the index gets used

SQL> EXPLAIN PLAN
2 FOR
3 SELECT /*+ INDEX(T1 IDX1) */ OBJECT_NAME FROM T1 4 WHERE OWNER IN ('SYS','SYSTEM','CTXSYS'); Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

Plan hash value: 328661726

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| 
Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |  6256 |   189K|   181   (0)| 
00:00:03 |
|   1 |  INLIST ITERATOR             |      |       |       |            |

|
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 6256 | 189K| 181 (0)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | IDX1 | 6256 | | 16 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

3 - access("OWNER"='CTXSYS' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')

15 rows selected.

SQL>

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

Received on Sat Feb 12 2005 - 15:14:02 CST