471,075 Members | 1,284 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Basic Sql performance question

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
Jul 19 '05 #1
2 3818
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

Jul 19 '05 #2
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Craig Stadler | last post: by
3 posts views Thread by nick | last post: by
2 posts views Thread by Danny A. | last post: by
25 posts views Thread by Jhon | last post: by
17 posts views Thread by blueapricot416 | last post: by
19 posts views Thread by frankiespark | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.