Re: SQL Question

From: Sergey Kaplun <skaplun_at_my-dejanews.com>
Date: Wed, 26 May 1999 12:28:38 GMT
Message-ID: <7igpdk$la4$1@nnrp1.deja.com>

In article <374bb823_at_newsread3.dircon.co.uk>,   "Paul Davies" <cobalt_at_dircon.co.uk> wrote:
> Are intersects and joins equivalent?
>
> So is the query:
>
> select part from orders_list1
> intersect
> select part from orders_list2
>
> the same as
>
> select a.part from orders_list1 a, orders_list2 b where b.part =
a.part
>
> Similarly is
>
> select part from orders_list1
> minus
> select part_from orders_list2
>
> the same as
>
> select part from orders_list1 where part not in (select part from
> orders_list2)
>
> If they are equivalent, when should one choose to use a set operator
as
> opposed to a join or not in query?
>
> Help appreciated
>
> Paul
>
>

Hi Paul,
It depends.
You should look at that.I think you'll understand. SQL> select part from orders_list1
  2 intersect
  3 select part from orders_list2;

100001 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64290 Card=1100002 B
          ytes=14300026)

   1    0   INTERSECTION
   2    1     SORT (UNIQUE) (Cost=59788 Card=1000000 Bytes=13000000)
   3    2       INDEX (FAST FULL SCAN) OF 'SYS_C002081' (UNIQUE) (Cost
          =496 Card=1000000 Bytes=13000000)

   4    1     SORT (UNIQUE) (Cost=4502 Card=100002 Bytes=1300026)
   5    4       INDEX (FAST FULL SCAN) OF 'SYS_C002082' (UNIQUE) (Cost
          =50 Card=100002 Bytes=1300026)

Statistics

---

         16  recursive calls
      16022  db block gets
       9002  consistent gets
       9470  physical reads
          0  redo size
    1552855  bytes sent via SQL*Net to client
     740623  bytes received via SQL*Net from client
       6670  SQL*Net roundtrips to/from client
          1  sorts (memory)
          2  sorts (disk)
     100001  rows processed

SQL> select a.part from orders_list1 a,orders_list2 b where a.part=b.part;

100001 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13865 Card=100002 By
          tes=3600072)

   1    0   MERGE JOIN (Cost=13865 Card=100002 Bytes=3600072)
   2    1     INDEX (FULL SCAN) OF 'SYS_C002081' (UNIQUE) (Cost=8127 C
          ard=1000000 Bytes=23000000)

   3    1     SORT (JOIN) (Cost=5688 Card=100002 Bytes=1300026)
   4    3       INDEX (FAST FULL SCAN) OF 'SYS_C002082' (UNIQUE) (Cost
          =50 Card=100002 Bytes=1300026)

Statistics

---

          8  recursive calls
       1453  db block gets
      15617  consistent gets
          0  physical reads
          0  redo size
    1552857  bytes sent via SQL*Net to client
     740622  bytes received via SQL*Net from client
       6670  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
     100001  rows processed

  1* select part from orders_list1 minus select part from orders_list2 SQL> / 899999 rows selected.

 real: 65945

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64290 Card=1100002 B
          ytes=14300026)

   1    0   MINUS
   2    1     SORT (UNIQUE) (Cost=59788 Card=1000000 Bytes=13000000)
   3    2       INDEX (FAST FULL SCAN) OF 'SYS_C002081' (UNIQUE) (Cost
          =496 Card=1000000 Bytes=13000000)

   4    1     SORT (UNIQUE) (Cost=4502 Card=100002 Bytes=1300026)
   5    4       INDEX (FAST FULL SCAN) OF 'SYS_C002082' (UNIQUE) (Cost
          =50 Card=100002 Bytes=1300026)

Statistics

---

         16  recursive calls
      16022  db block gets
       9002  consistent gets
          0  physical reads
          0  redo size

   13961303 bytes sent via SQL*Net to client     6660582 bytes received via SQL*Net from client

      60003  SQL*Net roundtrips to/from client
          1  sorts (memory)
          2  sorts (disk)
     899999  rows processed

SQL> select part from orders_list1 where part not in (select part from orders_list2);

899999 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=496 Card=50000 Bytes
          =1150000)

   1    0   INDEX (FAST FULL SCAN) OF 'SYS_C002081' (UNIQUE) (Cost=496
           Card=50000 Bytes=1150000)

   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C002082' (UNIQUE) (Cost=50 C
          ard=1 Bytes=13)

Statistics

---

          0  recursive calls
          2  db block gets
    2128177  consistent gets
          0  physical reads
          0  redo size

   13961303 bytes sent via SQL*Net to client     6660596 bytes received via SQL*Net from client

      60003  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     899999  rows processed

SQL>
Don't hasitate to contact by e-mail:skaplun_at_my-deja.com,skaplun_at_usa.net. Best regards,
Serge

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 26 1999 - 07:28:38 CDT