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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: a explain plan question

RE: a explain plan question

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Tue, 30 Jan 2007 16:10:07 +0100
Message-ID: <25D4919915CCF742A88EE3366D6D913D119B8B72@mailserver1>


I am puzzled by 'PARTITION RANGE (ITERATOR)'

followed with 'TABLE ACCESS (FULL) OF 'TUH_NVPAGINA'  

I would have thought that a partition range would avoid a full table access scan to achieve partitions pruning.  

Bernard Polarski

Oracle DBA  

Direct +32(0)2 690 28 90

Fax +32(0)2 690 27 82  

Da Vincilaan 5

1930 Zaventem

Belgium

www.atosorigin.com/be


From: amonte [mailto:ax.mount_at_gmail.com] Sent: dinsdag 30 januari 2007 15:44
To: oracle-l_at_freelists.org
Subject: a explain plan question  

Hi

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)

| Id  | Operation                  |  Name         | Rows  | Bytes |
Cost | Pstart| Pstop |


| 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 - 09:10:07 CST

Original text of this message

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