Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Help

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US