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: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 11 Jul 2007 14:03:13 -0700
Message-ID: <1184187793.666982.136440@r34g2000hsd.googlegroups.com>


On Jul 11, 4:17 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

It would be interesting to see how the different solutions perform as additional data is added - I suspect that Michel Cadot's solution will be the most efficient, even after his SQL statement is modified to retrieve the extra TASK_CODE column that the OP indicated in the desired result set. A re-test after modifying the tables so that TAS_ID cannot be null:

ALTER TABLE TASK MODIFY (
  TASK_ID NOT NULL); ALTER TABLE ACTV_CODE MODIFY (
  TASK_ID NOT NULL); SELECT /*+ GATHER_PLAN_STATISTICS */
  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 | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

|* 1 | HASH JOIN ANTI | | 1 | 2 | 2 |00:00:00.01 | 21 | 898K| 898K| 701K (0)|
|* 2 | HASH JOIN | | 1 | 4 | 4 |00:00:00.01 | 14 | 1066K| 1066K| 676K (0)|
|*  3 |    TABLE ACCESS FULL | ACTV_CODE |	1 |	 4 |	  4 |00:00:00.01
|	 7 |	   |	   |	      |
|   4 |    TABLE ACCESS FULL | TASK	 |	1 |	 8 |	  8 |00:00:00.01 |	 7
|	   |	   |	      |
|   5 |   VIEW		     |		 |	1 |	 4 |	  4 |00:00:00.01 |	 7 |	   |
|	      |
|   6 |    HASH UNIQUE	     |		 |	1 |	 4 |	  4 |00:00:00.01 |	 7 |
|	   |	      |
|*  7 |     TABLE ACCESS FULL| ACTV_CODE |	1 |	 4 |	  4 |00:00:00.01
|	 7 |	   |	   |	      |
-----------------------------------------------------------------------------------------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */
  T.TASK_ID,
  T.TASK_CODE
FROM
  TASK T,
  ACTV_CODE AC,
  (SELECT
    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 | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

|* 1 | HASH JOIN ANTI | | 1 | 2 | 2 |00:00:00.01 | 21 | 898K| 898K| 663K (0)|
|* 2 | HASH JOIN | | 1 | 4 | 4 |00:00:00.01 | 14 | 1066K| 1066K| 682K (0)|
|*  3 |    TABLE ACCESS FULL| ACTV_CODE |      1 |	4 |	 4 |00:00:00.01
|	7 |	  |	  |	     |
|   4 |    TABLE ACCESS FULL| TASK	|      1 |	8 |	 8 |00:00:00.01 |	7
|	  |	  |	     |
|   5 |   VIEW		    |		|      1 |	4 |	 4 |00:00:00.01 |	7 |	  |
|	     |
|*  6 |    TABLE ACCESS FULL| ACTV_CODE |      1 |	4 |	 4 |00:00:00.01
|	7 |	  |	  |	     |
----------------------------------------------------------------------------------------------------------------------

select /*+ GATHER_PLAN_STATISTICS  */
     t.task_id,
     t.task_code
from actv_code a,
     task t

where a.short_name like '%FIN'

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



| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

|* 1 | HASH JOIN | | 1 | 1 | 2 |00:00:00.01 | 21 | 1066K| 1066K| 422K (0)|
|* 2 | HASH JOIN ANTI | | 1 | 1 | 2 |00:00:00.01 | 14 | 1066K| 1066K| 682K (0)|
|*  3 |    TABLE ACCESS FULL| ACTV_CODE |      1 |	4 |	 4 |00:00:00.01
|	7 |	  |	  |	     |
|*  4 |    TABLE ACCESS FULL| ACTV_CODE |      1 |	4 |	 4 |00:00:00.01
|	7 |	  |	  |	     |
|   5 |   TABLE ACCESS FULL | TASK	|      1 |	8 |	 8 |00:00:00.01 |	7
|	  |	  |	     |
----------------------------------------------------------------------------------------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */

Not requested, but just for entertainment: select /*+ GATHER_PLAN_STATISTICS */

     t.task_id,
     t.task_code
from actv_code a,
     task t

where a.short_name like '%FIN'
   and a.code = 'Lead Craft'
   and a.task_id=t.task_id
   and a.task_id not in (select b.task_id from actv_code b where
b.task_id =
a.task_id and b.code = 'Outage Code');

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

|* 1 | HASH JOIN | | 1 | 1 | 2 |00:00:00.01 | 21 | 1066K| 1066K| 405K (0)|
|* 2 | HASH JOIN ANTI | | 1 | 1 | 2 |00:00:00.01 | 14 | 1066K| 1066K| 682K (0)|
|*  3 |    TABLE ACCESS FULL| ACTV_CODE |      1 |	4 |	 4 |00:00:00.01
|	7 |	  |	  |	     |
|*  4 |    TABLE ACCESS FULL| ACTV_CODE |      1 |	4 |	 4 |00:00:00.01
|	7 |	  |	  |	     |
|   5 |   TABLE ACCESS FULL | TASK	|      1 |	8 |	 8 |00:00:00.01 |	7
|	  |	  |	     |
----------------------------------------------------------------------------------------------------------------------

Without help from Oracle 10.2.0.2's transformations: select /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */

     t.task_id,
     t.task_code
from actv_code a,
     task t

where a.short_name like '%FIN'
   and a.code = 'Lead Craft'
   and a.task_id=t.task_id
   and a.task_id not in (select b.task_id from actv_code b where
b.task_id =
a.task_id and b.code = 'Outage Code');

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
|*  1 |  FILTER 	    |		|      1 |	  |	 2 |00:00:00.01 |      34 |
|	  |	     |
|*  2 |   HASH JOIN	    |		|      1 |	4 |	 4 |00:00:00.01 |      14 |
1066K|  1066K|  646K (0)|
|*  3 |    TABLE ACCESS FULL| ACTV_CODE |      1 |	4 |	 4 |00:00:00.01
|	7 |	  |	  |	     |
|   4 |    TABLE ACCESS FULL| TASK	|      1 |	8 |	 8 |00:00:00.01 |	7
|	  |	  |	     |
|*  5 |   TABLE ACCESS FULL | ACTV_CODE |      4 |	1 |	 2 |00:00:00.01
|      20 |	  |	  |	     |
----------------------------------------------------------------------------------------------------------------------

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jul 11 2007 - 16:03:13 CDT

Original text of this message

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