Continued Thread: Why would an index ignore a hint?

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.

In article <420ff128$0$86898$c30e37c6 (AT) ken-reader (DOT) 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@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@ORA9IR2>
ops$tkyte@ORA9IR2> create index EXP_MSG_STATUS_IDX on
MESSAGE_DETAILS(expected_message_status);

Index created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;

3 rows deleted.

ops$tkyte@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@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_MESSA GE_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

Thomas Kyte wrote:

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.

BINGO !!!!

The lookup status is a VARCHAR2, apparently the developers are following a
standard
that a third party used.
Changed the query from
SELECT /*+ INDEX (MESSAGE_DETAILS EXP_MSG_STATUS_IDX) */ *
FROM MESSAGE_DETAILS
WHERE expected_message_status = 11;

SELECT /*+ INDEX (MESSAGE_DETAILS EXP_MSG_STATUS_IDX) */ *
FROM MESSAGE_DETAILS
WHERE expected_message_status = TO_CHAR('11');

Thank you very much Thomas.

Craig.

In article <42111dd1$0$86894$c30e37c6 (AT) ken-reader (DOT) news.telstra.net>, Craig & Co.
says...

Thomas Kyte wrote:


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.


BINGO !!!!

The lookup status is a VARCHAR2, apparently the developers are following a
standard
that a third party used.
Changed the query from
SELECT /*+ INDEX (MESSAGE_DETAILS EXP_MSG_STATUS_IDX) */ *
FROM MESSAGE_DETAILS
WHERE expected_message_status = 11;

SELECT /*+ INDEX (MESSAGE_DETAILS EXP_MSG_STATUS_IDX) */ *
FROM MESSAGE_DETAILS
WHERE expected_message_status = TO_CHAR('11');

Thank you very much Thomas.

Craig.

select *
from message_details
where expected_Message_status = '11'

is what you are looking for though, no hint, no redundant to_char()


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

select *
from message_details
where expected_Message_status = '11'

is what you are looking for though, no hint, no redundant to_char()

Yep, did that in the next step.

Cheers
Craig