| Author |
Message |
Craig & Co.
*nix forums beginner
Joined: 12 Feb 2005
Posts: 13
|
 Posted: Wed Feb 16, 2005 2:29 am Post subject:
Re: Continued Thread: Why would an index ignore a hint?
|
|
|
| Quote: |
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 |
|
| Back to top |
|
 |
Thomas Kyte
*nix forums addict
Joined: 03 May 2005
Posts: 62
|
| Posted: Mon Feb 14, 2005 9:07 pm Post subject:
Re: Continued Thread: Why would an index ignore a hint?
|
|
|
In article <42111dd1$0$86894$c30e37c6@ken-reader.news.telstra.net>, Craig & Co.
says...
| Quote: |
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 |
|
| Back to top |
|
|
Craig & Co.
*nix forums beginner
Joined: 12 Feb 2005
Posts: 13
|
| Posted: Mon Feb 14, 2005 8:53 pm Post subject:
Re: Continued Thread: Why would an index ignore a hint?
|
|
|
Thomas Kyte wrote:
| Quote: |
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. |
|
| Back to top |
|
|
Thomas Kyte
*nix forums addict
Joined: 03 May 2005
Posts: 62
|
| Posted: Mon Feb 14, 2005 12:57 pm Post subject:
Re: Continued Thread: Why would an index ignore a hint?
|
|
|
In article <420ff128$0$86898$c30e37c6@ken-reader.news.telstra.net>, Craig & Co.
says...
| Quote: |
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_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 |
|
| Back to top |
|
|
Craig & Co.
*nix forums beginner
Joined: 12 Feb 2005
Posts: 13
|
| Posted: Sun Feb 13, 2005 11:29 pm Post subject:
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. |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
