Niall Litchfield wrote:
> "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
>
> 1. 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.
> 2. The index cannot actually be used to return correct results (null values
> and b*tree indexes for example).
> 3. 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
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ---------------------------------------------------------------------------
> | 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
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ---------------------------------------------------------------------------
> | 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
>
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
> -----------------------------------------------------------------------------
> | 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
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> --------------------------------------------------------------------------
> | 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
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> --------------------------------------------------------------------------
> | 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>
Thanks.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Feb 12 2005 - 17:13:39 CST
