Re: Continued Thread: Why would an index ignore a hint?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 14 Feb 2005 05:57:13 -0800
Message-ID: <118389433.000151a7.041@drn.newsguy.com>

In article <420ff128$0$86898$c30e37c6_at_ken-reader.news.telstra.net>, Craig & Co. says...
>
>Hi,
>
>Thank you for your responses, so far.
>
>I have since found out the query used would return no records.
>select /*+ INDEX (MESSAGE_DETAILS EXP_MSG_STATUS_IDX) */ count(1) from
>message_details where expected_message_status = 4;
>I then asked the developer to pick something that does exist and the result
>was the same
>the query ignored the Hint (in the Explain Plan).
>
>SELECT STATEMENT Optimizer Mode=CHOOSE 1 1497
> SORT AGGREGATE 1 1
> TABLE ACCESS FULL AUSDEV.MESSAGE_DETAILS 321 K 314 K 1497
>
>The table has 386831 - expected_message_status code 3 records and
>172 expected_message_status code 5 messages.
>
>Cheers
>Craig.
>
>

Perhaps it cannot -- you don't provide a full test case to reproduce with, so I'll make one up (dbms_xplan does not exist in 8174, but I'm using here to make clear why the index cannot be used in this example...)

ops$tkyte_at_ORA9IR2> create table MESSAGE_DETAILS   2 as
  3 select '3' expected_message_status, a.*   4 from big_table.big_table a
  5 where 1=0;  

Table created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> create index EXP_MSG_STATUS_IDX on MESSAGE_DETAILS(expected_message_status);  

Index created.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> delete from plan_table;  

3 rows deleted.  

ops$tkyte_at_ORA9IR2> explain plan for
  2 select /*+ INDEX (MESSAGE_DETAILS EXP_MSG_STATUS_IDX) */ count(1)   3 from message_details
  4 where expected_message_status = 4;  

Explained.  

ops$tkyte_at_ORA9IR2> select * from table(dbms_xplan.display);  

PLAN_TABLE_OUTPUT

---

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

|   0 | SELECT STATEMENT     |                  |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE      |                  |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL  | MESSAGE_DETAILS  |     1 |     3 |     2 |
-------------------------------------------------------------------------
 

Predicate Information (identified by operation id):

---

 

   2 - filter(TO_NUMBER("MESSAGE_DETAILS"."EXPECTED_MESSAGE_STATUS")=4)  

Note: cpu costing is off  

15 rows selected.

See the implicit to_number(database column)

Maybe your table has the status defined as a string and you are storing numbers in strings. And this is causing a conversion at runtime.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Mon Feb 14 2005 - 07:57:13 CST