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

In article <42111dd1$0$86894$c30e37c6_at_ken-reader.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