468,727 Members | 1,638 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,727 developers. It's quick & easy.

"NOT IN" performance

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
Jul 9 '07 #1
2 1847
debasisdas
8,127 Expert 4TB
ORCALE is smart enough to execute the select statement into NOT IN once and sort it by ROWID .
Jul 9 '07 #2
debasisdas
8,127 Expert 4TB
performance wise using IN might be slow,try to use EXIST instead.
Jul 9 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

28 posts views Thread by petermichaux | last post: by
4 posts views Thread by Jacinle Young | last post: by
6 posts views Thread by =?Utf-8?B?SmVmZg==?= | last post: by
350 posts views Thread by Lloyd Bonafide | last post: by
reply views Thread by zhoujie | last post: by
xarzu
1 post views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.