| Join Query problem. One using index while another full table scan [message #193103] |
Thu, 14 September 2006 22:11  |
honeybee
Messages: 4
Registered: September 2006
Location: Malaysia
|
Junior Member |
|
|
Hope this is not a silly question though... 
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??
[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  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
| 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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
. 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.