Re: Basic Sql performance question

Thanks Mauro,
I was asked this in an interview. I think your answer is the answer that guy was looking for.
Singh
mj_23_at_libero.it (Mauro) wrote in message news:<a2af5c1f.0310080306.2417ef79_at_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.
>
> dasingh_at_hotmail.com (DA Singh) wrote in message news:<78340f33.0310060938.42c89a3f_at_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
Received on Mon Oct 13 2003 - 22:46:41 CEST