a explain plan question

From: amonte <ax.mount_at_gmail.com>
Date: Tue, 30 Jan 2007 15:43:31 +0100
Message-ID: <85c1fb130701300643p488aafedjdde7ee60b85ee648@mail.gmail.com>

I have this query

SELECT

   TUH_NVPAGINA.ID_SECCION AS ID_SECCION,
   TUD_FEDIA.ID_TIPO_DIA AS ID_TIPO_DIA,
   TUD_FEDIA.ID_MES AS ID_MES,

   count(distinct TUH_NVPAGINA.TX_COOKIE_SESION) CNT FROM
   TUH_NVPAGINA,
   (SELECT FE_DIA, ID_MES, ID_TIPO_DIA

      FROM TUD_FEDIA
     WHERE ID_MES = :p_f_inicio) TUD_FEDIA

WHERE TUH_NVPAGINA.FE_DIA = TUD_FEDIA.FE_DIA GROUP BY

   TUH_NVPAGINA.ID_SECCION,
   TUD_FEDIA.ID_TIPO_DIA,
   TUD_FEDIA.ID_MES

and this plan

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1022392 Card=934 Bytes=49502)

1 0 SORT (GROUP BY) (Cost=1022392 Card=934 Bytes=49502)

   2    1     NESTED LOOPS (Cost=814767 Card=182275095 Bytes=9660580035)
   3    2       TABLE ACCESS (FULL) OF 'TUD_FEDIA' (Cost=3 Card=30
Bytes=480)
   4    2       PARTITION RANGE (ITERATOR)
   5    4         TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159

Card=5992606 Bytes=221726422)

| 0 | SELECT STATEMENT | | 934 | 49502 |

1022K|       |       |


|   1 |  SORT GROUP BY             |               |   934 | 49502 |

1022K|       |       |


|   2 |   NESTED LOOPS             |               |   182M|  9213M|

814K|       |       |
|*  3 |    TABLE ACCESS FULL       | TUD_FEDIA     |    30 |   480 |     3


|       |       |

|   4 |    PARTITION RANGE ITERATOR|               |       |       |

|   KEY |   KEY |

|*  5 |     TABLE ACCESS FULL      | TUH_NVPAGINA  |  5992K|   211M| 27159

| KEY | KEY |

Predicate Information (identified by operation id):

3 - filter("TUD_FEDIA"."ID_MES"=TO_NUMBER(:Z)) 5 - filter("TUH_NVPAGINA"."FE_DIA"="TUD_FEDIA"."FE_DIA")

I was wondering how to read this plan, the order of steps. From old set autotrace trace exp it seems to me that step 5 is the first step?

5 4 TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159 Card=5992606 Bytes=221726422)

Thanks

Alex

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 30 2007 - 08:43:31 CST