Rebuild local index Partition is using Full Table Scan Options

[richchen65]

Hi,

I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.

The DDL of the partition table/indexes:
=========================
CREATE TABLE TESTME
( INST_NO CHAR(3) NOT NULL,
ACCT_NO CHAR(16) NOT NULL,
REC_NO CHAR(9) NOT NULL,
TRAN_TYPE CHAR(2) DEFAULT ' ',
STAT CHAR(2) DEFAULT ' ',
POST_DATE NUMBER(9) DEFAULT (0),
TRN_DATE NUMBER(9) DEFAULT (0),
SYSTEM_DATE NUMBER(9) DEFAULT (0),
SYSTEM_TIME NUMBER(9) DEFAULT (0),
TELL_AND_BR NUMBER(9) DEFAULT (0),
JRNL_NO NUMBER(7) DEFAULT (0),
TRN_CODE NUMBER(7) DEFAULT (0),
BRTERM NUMBER(3) DEFAULT (0),
CHANNEL CHAR(1) DEFAULT ' ',
DELI CHAR(1) DEFAULT ' ',
VAR_AREA CHAR(120) DEFAULT ' ')
TABLESPACE DATAMISC PARTITION BY RANGE (ACCT_NO)
(PARTITION PART1_TESTME VALUES LESS THAN ('1000000000000000') TABLESPACE TSTEST1 ,
PARTITION PART2_TESTME VALUES LESS THAN ('2000000000000000') TABLESPACE TSTEST2,
PARTITION PART3_TESTME VALUES LESS THAN ('3000000000000000') TABLESPACE TSTEST3,
PARTITION PART4_TESTME VALUES LESS THAN ('4000000000000000') TABLESPACE TSTEST4 ,
PARTITION PART5_TESTME VALUES LESS THAN ('5000000000000000') TABLESPACE TSTEST5 ,
PARTITION PART6_TESTME VALUES LESS THAN ('6000000000000000') TABLESPACE TSTEST6 ,
PARTITION PART7_TESTME VALUES LESS THAN ('7000000000000000') TABLESPACE TSTEST7,
PARTITION PART8_TESTME VALUES LESS THAN ('8000000000000000') TABLESPACE TSTEST8,
PARTITION PART9_TESTME VALUES LESS THAN ('9000000000000000') TABLESPACE TSTEST9,
PARTITION PART10_TESTME VALUES LESS THAN ('9999999999999999') TABLESPACE TSTEST10
);

CREATE UNIQUE INDEX TESTMEPK ON TESTME (INST_NO, ACCT_NO, POST_DATE DESC, REC_NO) LOCAL ..
CREATE UNIQUE INDEX TESTME_01 ON TESTME (INST_NO, ACCT_NO, TRN_DATE DESC, REC_NO) LOCAL ..
CREATE UNIQUE INDEX TESTME_02 ON TESTME (INST_NO, ACCT_NO, REC_NO) LOCAL ...
====
The rebuild SQL (for example)
SQL> alter index TESTMEPK rebuild partition PART1_TESTME;
--> which will take very long time.

From the plan table , it showed scanning all partitions of the table instead of the corresponding partition.
------------------------------------------------------------------------------- -
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
------------------------------------------------------------------------------- -
| ALTER INDEX STATEMENT | | 423M| 26G| 200014 | | |
| INDEX BUILD UNIQUE |TESTMEPK | | | | | |
| SORT CREATE INDEX | | 423M| 26G| | | |
| PARTITION RANGE ALL | | | | | 1 | 10 |
| TABLE ACCESS FULL |TESTME | 423M| 26G| 200014 | 1 | 10 |
-------------------------------------------------------------------------------

Any ideas ?

Rich

[burleson]

Hi Rich,

Sorry, offhand I don't know what's up with this.

I do have notes on Oracle partition-aware that might help, but you may need to log an SR on MOSC:

http://support.oracle.com

[richchen65]

Hi Rich,

Sorry, offhand I don't know what's up with this.

I do have notes on Oracle partition-aware that might help, but you may need to log an SR on MOSC:

http://support.oracle.com

Hi Don,

According to Oracle Support , this problem is a BUG . (Bug #2466104)

Problem fixed after applying patch 1987514.

Regards,

Rich