Home » RDBMS Server » Performance Tuning » Join Query problem. One using index while another full table scan
Join Query problem. One using index while another full table scan [message #193103] Thu, 14 September 2006 22:11 Go to next message
honeybee
Messages: 4
Registered: September 2006
Location: Malaysia
Junior Member
Hope this is not a silly question though... Razz

I have 2 tables let say tblA, tblB
Let say tblA got these colA1, colA2, colA3 and so on...
table also same thing colB1, colB2, colB3 and so on..

i have an sql statement that looks like this....

_Statement 1_
select a.colA3, a.colA4
from tblA a, tblB b
where a.colA1 = b.colB1
and a.colA2 = b.colB2

When i run this statement, the performance is faster compare to the statement below:-

_Statement 2_
select a.colA3, a.colA4, b.colB5
from tblA a, tblB b
where a.colA1 = b.colB1
and a.colA2 = b.colB2

Take note that b.colB5 is a nonunique field.
The sql statement for the first one will run using index but the second one is full table scan!! But the question is why the second statement is full table scan, but the first one is using index?? Isit possible to modify the second statement so that it will use index to join?? Razz

[Updated on: Thu, 14 September 2006 23:13]

Report message to a moderator

Re: Join Query problem. One using index while another full table scan [message #193135 is a reply to message #193103] Fri, 15 September 2006 00:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are not filtering the results, so as far as Oracle is concerned, you are joining every row in table A to every row in table B.

Now, in the first example, all of the columns you are using in table B are contained in a single index - so there is no need to read the actual table to pick up additional columns. It is very fast to Nested Loops join to a large index, but NOT if you then have to lookup the table.

If all of the columns you need to satisfy such a query are NOT contained in a single index, it is usually fastest to simply full table scan both tables and perform a HASH join.

Think about it. If you have to read EVERY row in both tables anyway, what could be faster than reading them straight through from top to bottom? Certainly reading the entire table in dribs and drabs via the index would have to be the SLOWEST way.


Ross Leishman
icon10.gif  Re: Join Query problem. One using index while another full table scan [message #193142 is a reply to message #193103] Fri, 15 September 2006 01:01 Go to previous messageGo to next message
honeybee
Messages: 4
Registered: September 2006
Location: Malaysia
Junior Member
Thanks for the quick answer. But when i change b.colB5 to be a unique index field, it will not use the full table scan, but instead it will use index for searching. I think this could be due when the record try to find those table A field, it will search using index to look for the field in table B. Cool . But from my understanding, when we try to execute any sql statement the first step the sql will filter using the where clause before they run the select column. Cool
Re: Join Query problem. One using index while another full table scan [message #193378 is a reply to message #193142] Fri, 15 September 2006 23:20 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
honeybee wrote on Fri, 15 September 2006 16:01

But when i change b.colB5 to be a unique index field, it will not use the full table scan

Exactly! If it can satisfy the entire SELECT (including SELECTed colums and WHERE columns) from the INDEX, then it will prefer to use the index. If not, it prefers a Full Table Scan.

Read over my previous post again. If you still think the optimiizer is doing something inexplicable, post both the SQL and the Explain Plan.

Ross Leishman
Previous Topic: problems with updating big table by commit at end
Next Topic: REPORT BASED ON 2 VIEWS
Goto Forum:
  


Current Time: Tue Dec 06 15:46:47 CST 2016

Total time taken to generate the page: 0.12120 seconds