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

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Tue, 15 Feb 2005 08:53:23 +1100
Message-ID: <42111dd1$0$86894$c30e37c6@ken-reader.news.telstra.net>

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. Received on Mon Feb 14 2005 - 15:53:23 CST