"NOT IN" performance

[MohamedSaleh]

HI all
I have the following problem
Table SOURCE has columns <char COL1, integar COL2> and has rows

{A,1},
{A,2,},
{,A,3},
{B,1},
{B,2},,
{C,1},
{C,2},
{C,3},
{D.1}
And table TARGET is empty
Lets the rows have the same COL1i s a group
for example {A,1},{A,2},{A,3} is a group

I want to insert into TARGET all rows in SOURCE which haven't minimum COL2 in its group
These rows are
{A,2,},
{,A,3},
{B,1},
{B,1},,
{C,2},
{C,3},

My solution is
INSERT INTO TARGET {COL1,COL2..)
FROM
SELECT {COL1,COL2)
FROM SOURCE
WHERE ROWID NOT IN
(
SELECT MIN(COL2),ROWID
FROM SOURCE
GROUP BY COL1
);


I want to know if the “NO INT” statement will require full table access every ROWID or ORCALE is smart enough to execute the select statement into NOT IN once and sort it by ROWID and then every time check if ROWID in it or not using binary search
That's just an example but in the real case I have many columns instead of COL1 and many columns instead of COL2 and also I have additional columns in SOURCE table.
The SOURCE table has about 84000 rows and TARGET is empty initially
Do you have any better solution (fastest)?

Thanks and Regards
Yours Mohamed Saleh
C++ Developer
www.itsoft.com.eg

[debasisdas]

ORCALE is smart enough to execute the select statement into NOT IN once and sort it by ROWID .

[debasisdas]

performance wise using IN might be slow,try to use EXIST instead.