Nested loop Vs Inlist iterator [message #65274] |
Wed, 14 July 2004 23:11 |
Jai Vrat Singh
Messages: 205 Registered: September 2002 Location: Singapore
|
Senior Member |
|
|
I have a table structure like this:
create table emp(
empno NUMBER,
ename varchar2(30));
I create a regular index on empno and insert lots of records.
1.
/* assume that the subquery gives 3 value 5001, 5002, 5003*/
select * from emp where empno in ( select empno from emp where empno > 5000);
2.
select * from emp where empno in ( 5001, 5002, 5003) ;
When i get the explain plan, for 1 it mentions index range scan at lowest level ( for subquery) then same at a level juxt above the lowest . It shows NESTED LOOPS SEMI.
for 2 it mentions index range scan at lowest level ( for subquery) then TABLE scan . It shows INLIST ITERATOR.
Now, what is the difference in the way oracle executes these internally and which is better?
|
|
|