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