Re: ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes
Date: Wed, 9 Mar 2011 12:53:01 -0800 (PST)
On Mar 8, 12:26 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Mar 8, 6:20 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> > On Mar 8, 9:17 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> > > 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/Exper......
> > > 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>
> > What is interesting is that I can create an empty table and exchange
> > it with the partition I am trying to truncate, and that returns almost
> > instantly (less than three seconds). Also, I tried a straight drop
> > partition and that also full scans it.
> > I also didn't have an update global indexes clause on the truncate or
> > the drop.
> Maybe some internal shredding winds up with some nesting, similar to
> the explanation in bug 5007945 but without actually hitting a bug. I
> have no idea why this wouldn't happen every time if that's the case,
> though. It would have to be something special about the data, I guess
> - is there something optional in your data that shreds differently?
> Any time there's a bug or not-a-bug, there might be related odd
> _at_home.com is bogus.http://www.nationmultimedia.com/2011/03/08/technology/Oracle-offers-r...
Thanks, Joel and Randolf. Nope, it's a very simple table, although fairly large. It is 2.3TB, partitioned by month over a year.
We have the indexes I showed earlier, nothing fancy like domain indexes or anything. It is a journal table for sotring previous changes to a large XMLTYPE column.
I did issue a 10046 trace right off the bat, but nothing was odd, it just started issuing calls which resulted in db file scattered read events against the table partition.
I think for now we will just issue the partition exchange including indexes, as this gets us where we need to be.
Steve Received on Wed Mar 09 2011 - 14:53:01 CST