| Index not being used for query [message #65865] |
Sun, 16 January 2005 22:52  |
Vinu
Messages: 13
Registered: March 2003
|
Junior Member |
|
|
Dear All,
I have a table in which there is an serial no field which is unique and a profile id field whihc is non unique. There is a unique index created on the primary key field and a non unique index created on the profile id field.
A query like "select profileid from this_table where serialno=<input> is doing a full table scan. Could not find any valid reason for this query not using the index created on the primary key.
Please throw some light on this behavior.
Warm Regards,
Vinu.
|
|
|
|
|
|
| Re: Index not being used for query [message #65867 is a reply to message #65866] |
Mon, 17 January 2005 01:00  |
Vinu
Messages: 13
Registered: March 2003
|
Junior Member |
|
|
Dear Mahesh,
Another thing to note in the table is that the serial number is a VARCHAR TYPE FIELD.
Just to add to this, if I provide a hint in the select query like "SELECT /*+INDEX(TABLE _NAME INDEX_NAME)*/ PROFILEID FROM TABLE_NAME WHERE SERIALNO='XYZ'" then the query seems to be using the index on the primary key. This wrt the output that I see when I see the plan_table after using the EXPLAIN PLAN FOR statement.
Regards,
Vinu.
|
|
|
|
| Re: Index not being used for query [message #65868 is a reply to message #65867] |
Mon, 17 January 2005 03:49 |
Tony Andrews
Messages: 29
Registered: January 2005
|
Junior Member |
|
|
How many rows are there in the table - or more to the point, how many blocks?
Oracle will perform a full table scan if it appears to be cheaper than using the index, which it will be if there is only 1-2 blocks in your table.
|
|
|
|
| Re: Index not being used for query [message #65870 is a reply to message #65867] |
Mon, 17 January 2005 04:16 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If your <<z>input> is of datatype number, then the following happens:
select * from table where serialno = 1 will do an implicit datatype conversion on the column serialno.
This will prevent the index from being used.
try this:
select * from table where serialno = <<z>input>
hth
|
|
|
|
|
|
| Re: Index not being used for query [message #65872 is a reply to message #65870] |
Mon, 17 January 2005 20:39 |
Vinu
Messages: 13
Registered: March 2003
|
Junior Member |
|
|
Frank,
As u said, the input is of type number and the column type is of type varchar. This query is in an on insert trigger in one table which has a field exactly same as our table but the data type of these two do not match. Is this the problem? If yes how is it related to the non usage of the index.
Thanks again.
Regards,
vinu.
|
|
|
|
| Re: Index not being used for query [message #65873 is a reply to message #65872] |
Mon, 17 January 2005 21:00 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
The implicit data-conversion (from varchar2 to number) is seen as a function (to_number).
The index is on <<z>column_name>, not on to_number(<<z>column_name>), so the index cannot be used.
To solve this make the serialno you provide a variable of datatype varchar2 or do a to_char.
hth
|
|
|
|
| Re: Index not being used for query [message #65874 is a reply to message #65873] |
Mon, 17 January 2005 21:32 |
Vinu
Messages: 13
Registered: March 2003
|
Junior Member |
|
|
Frank,
This is absolutely correct, it works with ur solution. Thank u very much, you have solved a very big bottleneck in my application as this was leading to usage of a lot of database sessions.
Thanks again,
Vinu.
|
|
|
|
| Re: Index not being used for query [message #65877 is a reply to message #65871] |
Tue, 18 January 2005 00:44 |
Tony Andrews
Messages: 29
Registered: January 2005
|
Junior Member |
|
|
What I mean is, how many blocks does Oracle THINK the table has:
SQL> select blocks from user_tables where table_name='EMP'
2 /
BLOCKS
----------
1
The optimizer works according to the stats it has, which may not be correct. If incorrect, use DBMS_STATS to re-analyze.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
