Basic Sql performance question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • DA Singh
    #1

    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
    Tags: None
    • Mauro
      #2
      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@hotmail .com (DA Singh) wrote in message news:<78340f33. 0310060938.42c8 9a3f@posting.go ogle.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

        Comment

        • DA Singh
          #3
          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@libero.it (Mauro) wrote in message news:<a2af5c1f. 0310080306.2417 ef79@posting.go ogle.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@hotmail .com (DA Singh) wrote in message news:<78340f33. 0310060938.42c8 9a3f@posting.go ogle.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

            Comment

            Previous template Next
            Working...