Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Rebuild local index Partition is using Full Table Scan
richchen65
post Apr 23 2012, 09:57 PM
Post #1


Advanced Member
***

Group: Members
Posts: 47
Joined: 20-April 09
From: IL 60559
Member No.: 40,424



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
Go to the top of the page
 
+Quote Post
burleson
post Apr 26 2012, 09:58 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,611
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
richchen65
post May 2 2012, 09:55 PM
Post #3


Advanced Member
***

Group: Members
Posts: 47
Joined: 20-April 09
From: IL 60559
Member No.: 40,424



QUOTE (burleson @ Apr 26 2012, 10:58 PM) *
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
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd October 2014 - 07:12 AM