Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: a explain plan question
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,
FROM TUD_FEDIA WHERE ID_MES = :p_f_inicio) TUD_FEDIAWHERE 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=27159Card=5992606 Bytes=221726422)
| Id | Operation | Name | Rows | Bytes |Cost | Pstart| Pstop |
1022K| | |27159 | KEY | KEY |
| 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|
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-lReceived on Tue Jan 30 2007 - 09:10:07 CST
![]() |
![]() |