Re: Basic Sql performance question

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 Wed Oct 08 2003 - 13:06:37 CEST