ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes

Giganews Newsgroups
Subject: ALTER TABLE TRUNCATE PARTITION full scans partition with LOCAL indexes
Posted by:  Steve Howard (stevedhowa…@gmail.com)
Date: Mon, 7 Mar 2011

Hi All,

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
--------------------------------------------------------------------------------
alter table xwc.xwcmd_xml_data_journal2 truncate partition p01

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

LOCALI INDEX_NAME
------ ------------------------------
LOCAL  SYS_IL0000268664C00008$$
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

Replies