Re: ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes

From: Randolf Geist <>
Date: Tue, 8 Mar 2011 07:42:53 -0800 (PST)
Message-ID: <>

On Mar 8, 3:17 pm, Steve Howard <> 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
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.


Oracle related stuff blog:

Co-author of the "OakTable Expert Oracle Practices" book: Received on Tue Mar 08 2011 - 09:42:53 CST

Original text of this message