Re: ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Tue, 8 Mar 2011 06:17:34 -0800 (PST)
Message-ID: <1d6665d7-9850-413e-aeaa-ae6c0ceca1bc_at_u24g2000prn.googlegroups.com>
On Mar 8, 7:48 am, Randolf Geist <mah..._at_web.de> wrote:
> On Mar 7, 2:56 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
> > As you can see, the number of blocks is very close to what is
> > estimated it will require to truncate this partition. Also as shown,
> > all indexes are LOCAL, and there are no constraints either against or
> > on the table.
>
> Actually I can't see from your output that there are no global indexes
> since you show us only the output from a query against
> DBA_PART_INDEXES which by definition covers only partitioned indexes.
>
> So if there was a global index and your output from GV$SQL was
> truncated and the statement executed actually contained a "UPDATE
> [GLOBAL] INDEXES" then this would explain a read from the partition
> segment as part of the truncate to maintain the global index.
>
> 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
Date: Tue, 8 Mar 2011 06:17:34 -0800 (PST)
Message-ID: <1d6665d7-9850-413e-aeaa-ae6c0ceca1bc_at_u24g2000prn.googlegroups.com>
On Mar 8, 7:48 am, Randolf Geist <mah..._at_web.de> wrote:
> On Mar 7, 2:56 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
> > As you can see, the number of blocks is very close to what is
> > estimated it will require to truncate this partition. Also as shown,
> > all indexes are LOCAL, and there are no constraints either against or
> > on the table.
>
> Actually I can't see from your output that there are no global indexes
> since you show us only the output from a query against
> DBA_PART_INDEXES which by definition covers only partitioned indexes.
>
> So if there was a global index and your output from GV$SQL was
> truncated and the statement executed actually contained a "UPDATE
> [GLOBAL] INDEXES" then this would explain a read from the partition
> segment as part of the truncate to maintain the global index.
>
> 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
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
SQL> Received on Tue Mar 08 2011 - 08:17:34 CST