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

From: Randolf Geist <mahrah_at_web.de>
Date: Tue, 8 Mar 2011 04:48:55 -0800 (PST)
Message-ID: <0b2d0486-480d-4226-ae18-3cb02dd08dc4_at_q14g2000vbf.googlegroups.com>

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/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Tue Mar 08 2011 - 06:48:55 CST

This message: [ Message body ]
Next message: Steve Howard: "Re: ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes"
Previous message: Nimish Garg: "Re: Playing With Truncate and Date"
In reply to: Steve Howard: "ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes"
Next in thread: Steve Howard: "Re: ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message