Home » SQL & PL/SQL » SQL & PL/SQL » Query Not using Index
icon12.gif  Query Not using Index [message #190063] Tue, 29 August 2006 03:30 Go to next message
vilas_more
Messages: 2
Registered: September 2005
Junior Member
Dear All,

I using two tables CUSTOMER and INVOICE in SQL Query
CUSTOMERID is primary key of CUSTOMER table and there is a INDEX on CUSTOMERID field of INVOICE table.

Long Running query

SELECT CUSTOMER.CUSTOMERID,
CUSTOMER.NAME,
INVOICE.INVNUMBER,
INVOICE.INVDATE
FROM CUSTOMER,
INVOICE
WHERE CUSTOMER.CUSTOMERID = INVOICE.CUSTOMERID

Problem :
The above query is not using INDEX even after providing the HINT /*+INDEX(CUSTOMER PKCUSTOMERID)*/
BUT If I remove the CUSTOMER.NAME field from SQL the
query uses the INDEX.

Please guide me? Why Oracle9i behaves like this?

TIA

Vilas
Re: Query Not using Index [message #190079 is a reply to message #190063] Tue, 29 August 2006 04:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The optimiser isn't using the index because using the index in your example would (assuming every customer has invoices) run slower.

Your query needs to look at the whole CUSTOMER table, and a Full Table Scan (FTS) is the quickest way to do this. I'll guess that the plan shows either a FTS on CUSTOMER and a nested loops index access on INVOICES, or a FTS on both CUSTOMERS and INVOICES and a hash join between them.

When you remove the customer name from the column list, then the optimiser realises that the only information it needs from the CUSTOMERS table is the ID, which is on the index.

I strongly suspect that it will replace the FTS on CUSTOMERS with a Fast Full Index Scan on the index.

In the words of Tom Kyte:
Full Table Scans are not always bad
Indexes are not always good
Repeat until believed.
Re: Query Not using Index [message #190362 is a reply to message #190079] Wed, 30 August 2006 06:41 Go to previous messageGo to next message
pareshr
Messages: 18
Registered: August 2006
Location: Ahmedabad
Junior Member
Hi,

I think this can be happened if optimizer mode is set to cost base. But here he has used the user index hint in query. So how engine avoid index usage.
Can u explain me more.

Thnank You,
Paresh Rupareliay
Re: Query Not using Index [message #190366 is a reply to message #190362] Wed, 30 August 2006 06:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What needs explaining?
The query is going to look at the whole CUSTOMERS table.
The CBO (must be the CBO, because the RBO would almost certainly get it wrong and use the index) looks at the stats for the table and the estimated number of rows the query will look at, and decides that the quickest way of getting at all the data is to do a FTS on CUSTOMERS.

I suspect that there will be a problem with the syntax used for the hint if it isn't getting used, but that's all to the good as a full table scan is the correct thing to do in this situation.

Re: Query Not using Index [message #190598 is a reply to message #190366] Thu, 31 August 2006 06:28 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just to cap it off...

JR is right, an index is the wrong way to go and Oracle was right not to use it.

It is often said that Hints are only that - they are not instructions to the optimizer, only suggestions. Oracle makes no guarantee that a hint will be followed.

Having said that, Oracle will usually follow a hint if it is possible to do so. For instance, in a single-table SELECT, an INDEX hint like the above would probably be honored - the CBO would Full Scan the index and then lookup the table rows. It would be slower, but Oracle figures you must have your reasons - perhaps you wanted to use the index as a sneaky way to order the rows returned without an ORDER BY clause (don't do this though, it is another conversation).

Your example is different though - it involves a join. Since you have not provided a hint on the join order or join method, the CBO has to determine both of these itself. It does this (or seems to) without any regard for hints. Looking at the query, it rightly considers a HASH join using CUSTOMERS as the hash table to be the right approach. Without hints, the best way to do this is to FTS the CUSTOMERS table, and then probe it with an FTS of the INVOICE table. Since CUSTOMERs are being looked up from the hash-table, it is not possible to use the index.

The only other way Oracle could have used the index was in a Full Index Scan to build the hash table. This is patently absurd, as it cannot possibly be more efficient (vs a FTS) to read every row from a table via the index. And unlike my earlier example (where the result differs in the order of rows returned) there is no possible difference in the reult - its just slower.

If you want your index hint to be honoured, you will have to also provide join-order and join method hints.
/*+ LEADING(invoice) USE_NL(customer) INDEX(CUSTOMER PKCUSTOMERID)*/
Of course, this is possibly the worst possible plan you could choose for this SQL.

Ross Leishman
Previous Topic: Rank Over
Next Topic: MERGE
Goto Forum:
  


Current Time: Fri Dec 02 17:02:13 CST 2016

Total time taken to generate the page: 0.26554 seconds