Basic Sql performance question

[DA Singh]

If I have two tables
create table tab1(col1 number, col2 varchar2(50));
create table tab2(col1 number, col2 varchar2(50));

insert into tab1 values (1, 'One');
insert into tab1 values (2, 'Two');
insert into tab1 values (3, 'Three');

insert into tab2 values (3, 'Three');
insert into tab2 values (4, 'Four');
insert into tab2 values (5, 'Five');

I want all tuples that are in tab1 but not in tab2. That is,
1, 'One' and 2, 'Two'
The ways I can do this are
1.
Select * from tab1 minus select * from tab2;
2.
Select t1.*
from tab1 t1
where not exists
(select col1 from tab2 where col1 = t1.col1);
3.
Select *
from tab1 t1
where col1 not in (select col1 from tab2);
4. Select t1.*
from tab1 t1, tab2 t2
where t1.col1 = t2.col1(+)
and t2.col1 is null

Which of these four methods is the fastest and in which circumstances?
Could someone rank them for me?
Is there a fifth, still better way?
Thanks,
DA Singh

[Mauro]

Hi...

It is not so simple to say which is the fastest.
How many rows are there on your tables?
Which is the average row lenght?
Are the tables indexed with some primary key or other indexes?
Is there a partitioning?

In this example the better is the first one, as the only access
possible is a FULL TABLE SCAN. In the first statement you get only 2
table scans, in other statements you get about (rows tab1)*(rows tab2)
table scans; hash join help you but the cost is expensive.

If you give me some more data i can try to send you a better answer.
Bye.

da*****@hotmail.com (DA Singh) wrote in message news:<78**************************@posting.google. com>...

If I have two tables
create table tab1(col1 number, col2 varchar2(50));
create table tab2(col1 number, col2 varchar2(50));

insert into tab1 values (1, 'One');
insert into tab1 values (2, 'Two');
insert into tab1 values (3, 'Three');

insert into tab2 values (3, 'Three');
insert into tab2 values (4, 'Four');
insert into tab2 values (5, 'Five');

I want all tuples that are in tab1 but not in tab2. That is,
1, 'One' and 2, 'Two'
The ways I can do this are
1.
Select * from tab1 minus select * from tab2;
2.
Select t1.*
from tab1 t1
where not exists
(select col1 from tab2 where col1 = t1.col1);
3.
Select *
from tab1 t1
where col1 not in (select col1 from tab2);
4. Select t1.*
from tab1 t1, tab2 t2
where t1.col1 = t2.col1(+)
and t2.col1 is null

Which of these four methods is the fastest and in which circumstances?
Could someone rank them for me?
Is there a fifth, still better way?
Thanks,
DA Singh

[DA Singh]

Thanks Mauro,
I was asked this in an interview. I think your answer is the answer
that guy was looking for.
Singh
mj***@libero.it (Mauro) wrote in message news:<a2**************************@posting.google. com>...

Hi...

It is not so simple to say which is the fastest.
How many rows are there on your tables?
Which is the average row lenght?
Are the tables indexed with some primary key or other indexes?
Is there a partitioning?

In this example the better is the first one, as the only access
possible is a FULL TABLE SCAN. In the first statement you get only 2
table scans, in other statements you get about (rows tab1)*(rows tab2)
table scans; hash join help you but the cost is expensive.

If you give me some more data i can try to send you a better answer.
Bye.

da*****@hotmail.com (DA Singh) wrote in message news:<78**************************@posting.google. com>...

If I have two tables
create table tab1(col1 number, col2 varchar2(50));
create table tab2(col1 number, col2 varchar2(50));

insert into tab1 values (1, 'One');
insert into tab1 values (2, 'Two');
insert into tab1 values (3, 'Three');

insert into tab2 values (3, 'Three');
insert into tab2 values (4, 'Four');
insert into tab2 values (5, 'Five');

I want all tuples that are in tab1 but not in tab2. That is,
1, 'One' and 2, 'Two'
The ways I can do this are
1.
Select * from tab1 minus select * from tab2;
2.
Select t1.*
from tab1 t1
where not exists
(select col1 from tab2 where col1 = t1.col1);
3.
Select *
from tab1 t1
where col1 not in (select col1 from tab2);
4. Select t1.*
from tab1 t1, tab2 t2
where t1.col1 = t2.col1(+)
and t2.col1 is null

Which of these four methods is the fastest and in which circumstances?
Could someone rank them for me?
Is there a fifth, still better way?
Thanks,
DA Singh