Re: SQL Help

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 11 Jul 2007 13:17:19 -0700
Message-ID: <1184185037.970791@bubbleator.drizzle.com>

Just for fun:

explain plan for
SELECT T.TASK_ID, T.TASK_CODE
FROM TASK T, ACTV_CODE AC, (
   SELECT DISTINCT TASK_ID FROM ACTV_CODE    WHERE CODE='Outage Code') ACN
WHERE
   T.TASK_ID=ACN.TASK_ID(+)
   AND ACN.TASK_ID IS NULL
   AND T.TASK_ID=AC.TASK_ID
   AND AC.SHORT_NAME LIKE '%FIN';

---

| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| 
Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     2 |   106 |    11  (19)| 
00:00:01 |


|*  1 |  HASH JOIN ANTI      |           |     2 |   106 |    11  (19)| 

00:00:01 |


|*  2 |   HASH JOIN          |           |     4 |   160 |     7  (15)| 

00:00:01 |


|*  3 |    TABLE ACCESS FULL | ACTV_CODE |     4 |    80 |     3   (0)| 

00:00:01 |
|   4 |    TABLE ACCESS FULL | TASK      |     8 |   160 |     3   (0)| 
00:00:01 |
|   5 |   VIEW               |           |     4 |    52 |     4  (25)| 

00:00:01 |

| 6 | HASH UNIQUE | | 4 | 100 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| ACTV_CODE | 4 | 100 | 3 (0)|
00:00:01 |

---

select task_id
from actv_code a
where short_name like '%FIN'

   and code = 'Lead Craft'
   and not exists (select null from actv_code b where b.task_id = a.task_id and b.code = 'Outage Code');

---

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| 
Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    57 |     7  (15)| 
00:00:01 |


|*  1 |  HASH JOIN ANTI    |           |     1 |    57 |     7  (15)| 

00:00:01 |


|*  2 |   TABLE ACCESS FULL| ACTV_CODE |     4 |   128 |     3   (0)| 

00:00:01 |


|*  3 |   TABLE ACCESS FULL| ACTV_CODE |     4 |   100 |     3   (0)| 

00:00:01 |

---

Though the result sets are not exactly the same.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Jul 11 2007 - 15:17:19 CDT