Home » RDBMS Server » Performance Tuning » Nested loop Vs Inlist iterator
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?  

 

 
Previous Topic: Efficient way to query and persist data. any leads
Next Topic: EXECUTE IMMEDIATE Command (Oracle 8i)
Goto Forum:
  


Current Time: Thu Mar 28 06:41:41 CDT 2024