Re: ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes
From: Randolf Geist <mahrah_at_web.de>
Date: Tue, 8 Mar 2011 07:42:53 -0800 (PST)
Message-ID: <2e2ac83e-ccb0-40cb-b6e8-008da37fc8a9_at_p24g2000vbl.googlegroups.com>
On Mar 8, 3:17 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> Thanks, Randolf, sorry for the omission. However, we definitely don't
> have any global indexes as shown in dba_indexes...
>
> SQL> select index_name,partitioned from dba_indexes where owner =
> 'XWC' and table_name = 'XWCMD_XML_DATA_JOURNAL2';
>
> INDEX_NAME PAR
> ------------------------------ ---
> SYS_IL0000268664C00008$$ YES
> XWCMDXMLDATAJOURNAL2_IDX1 YES
> XWCMDXMLDATAJOURNAL2_IDX2 YES
> XWCMDXMLDATAJOURNAL2_IDX3 YES
Since the table name suggests some XML related content - is any of these indexes by any chance a (partitioned) domain index rather than a usual b*tree index? Since partitioned domain indexes are "local" I still don't see why they should need a partition scan when truncating/ dropping however that can depend on the domain index type implementation.
Date: Tue, 8 Mar 2011 07:42:53 -0800 (PST)
Message-ID: <2e2ac83e-ccb0-40cb-b6e8-008da37fc8a9_at_p24g2000vbl.googlegroups.com>
On Mar 8, 3:17 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> Thanks, Randolf, sorry for the omission. However, we definitely don't
> have any global indexes as shown in dba_indexes...
>
> SQL> select index_name,partitioned from dba_indexes where owner =
> 'XWC' and table_name = 'XWCMD_XML_DATA_JOURNAL2';
>
> INDEX_NAME PAR
> ------------------------------ ---
> SYS_IL0000268664C00008$$ YES
> XWCMDXMLDATAJOURNAL2_IDX1 YES
> XWCMDXMLDATAJOURNAL2_IDX2 YES
> XWCMDXMLDATAJOURNAL2_IDX3 YES
Since the table name suggests some XML related content - is any of these indexes by any chance a (partitioned) domain index rather than a usual b*tree index? Since partitioned domain indexes are "local" I still don't see why they should need a partition scan when truncating/ dropping however that can depend on the domain index type implementation.
What happens if you request the EXCHANGE PARTITION to include indexes?
Since you seem to have the time for investigation, an extended SQL trace might give you a clue what is happening at truncate / drop partition time.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Tue Mar 08 2011 - 09:42:53 CST