Home » RDBMS Server » Performance Tuning » Index not being used for query
Index not being used for query [message #65865] Sun, 16 January 2005 22:52 Go to next message
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 #65866 is a reply to message #65865] Mon, 17 January 2005 00:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
analyze the table and the index.
try again.
Re: Index not being used for query [message #65867 is a reply to message #65866] Mon, 17 January 2005 01:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #65871 is a reply to message #65868] Mon, 17 January 2005 19:03 Go to previous messageGo to next message
Vinu
Messages: 13
Registered: March 2003
Junior Member
Tony,
The table is pretty huge I can assure u that, and is taking a lot of database sessions.

Regards,
Vinu
Re: Index not being used for query [message #65872 is a reply to message #65870] Mon, 17 January 2005 20:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: row cache lock: Cache id 8 - dc tables on QUERIES.
Next Topic: improving performace of oracle 9i application server release 1.0
Goto Forum:
  


Current Time: Sat Sep 19 01:04:25 CDT 2020