ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes

Three node cluster on Enterprise 10.2.0.4 SLES 10 64 bit

Can anyone tell me why the following may full scan the partition being truncated?

SQL> select target,totalwork,sofar,time_remaining,opname,sql_id from gv $session_longops where time_remaining > 0   2 /

TARGET                          TOTALWORK      SOFAR TIME_REMAINING
------------------------------ ---------- ---------- --------------
OPNAME                         SQL_ID
------------------------------ -------------
XWC.XWCMD_XML_DATA_JOURNAL2       3228739     358853          10381
Table Scan                     f2zrnjvk9usv9

SQL> select sql_text from gv$sql where sql_id = 'f2zrnjvk9usv9';

SQL_TEXT

SQL> select locality,index_name from dba_part_indexes where owner = 'XWC' and table_name = 'XWCMD_XML_DATA_JOURNAL2';

LOCALI INDEX_NAME

------ ------------------------------

LOCAL  XWCMDXMLDATAJOURNAL2_IDX1
LOCAL  XWCMDXMLDATAJOURNAL2_IDX2
LOCAL  XWCMDXMLDATAJOURNAL2_IDX3

SQL> select distinct constraint_type from dba_constraints where owner = 'XWC' and table_name = 'XWCMD_XML_DATA_JOURNAL2';

C
-
C

SQL> select blocks from dba_segments where owner = 'XWC' and segment_name = 'XWCMD_XML_DATA_JOURNAL2' and partition_name = 'P01';

    BLOCKS

   3232384

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.

This is the "lowest" partition time wise (partitioned by month) and there is a LOB that can be stored out of row. This occurs about 60% of the time. However, the block and file number in the p1/p2 parameters are always the table.

Thanks,

Steve Received on Mon Mar 07 2011 - 13:56:54 CST