My Oracle Support Banner

Alter Index Rebuild Partition With Table Acess Full (Doc ID 1941699.1)

Last updated on JANUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

ALTER INDEX "user1"."INDEX" rebuild PARTITION "TEST_PKK_00001"
 tablespace users PARALLEL 180 NOLOGGING

is taking a lot of time to finish.

 

Most of the time is spent by

SELECT KEY.KEY_NAME INDEX_NAME, KEY.COLUMN_NAME, KEY.DESCEND
FROM
(SELECT C.CONSTRAINT_NAME KEY_NAME, C.COLUMN_NAME COLUMN_NAME, C.POSITION
 POSITION, 'ASC' DESCEND FROM DBA_CONS_COLUMNS C WHERE C.OWNER = :B2 AND
 C.TABLE_NAME = :B1 AND C.CONSTRAINT_NAME IN ( SELECT CON1.NAME FROM
 SYS.USER$ USER1, SYS.USER$ USER2, SYS.CDEF$ CDEF, SYS.CON$ CON1, SYS.CON$
 CON2, SYS.OBJ$ OBJ1, SYS.OBJ$ OBJ2 WHERE USER1.NAME = :B2 AND OBJ1.NAME =
 :B1 AND CDEF.TYPE# = 3 AND BITAND(CDEF.DEFER, 36) = 4 AND (CDEF.TYPE# = 5
 OR CDEF.ENABLED IS NOT NULL) AND CON2.OWNER# = USER2.USER#(+) AND
 CDEF.ROBJ# = OBJ2.OBJ#(+) AND CDEF.RCON# = CON2.CON#(+) AND OBJ1.OWNER# =
 USER1.USER# AND CDEF.CON# = CON1.CON# AND CDEF.OBJ# = OBJ1.OBJ#) AND EXISTS
 ( SELECT 'x' FROM DBA_TAB_COLUMNS T WHERE T.OWNER = C.OWNER AND
 T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME = C.COLUMN_NAME) UNION SELECT
 I.INDEX_NAME KEY_NAME, C.COLUMN_NAME COLUMN_NAME, C.COLUMN_POSITION
 POSITION, C.DESCEND DESCEND FROM DBA_INDEXES I, DBA_IND_COLUMNS C WHERE
 I.TABLE_OWNER = :B2 AND I.TABLE_NAME = :B1 AND I.UNIQUENESS = 'UNIQUE' AND
 I.OWNER = C.INDEX_OWNER AND I.INDEX_NAME = C.INDEX_NAME AND :B1 =
 C.TABLE_NAME AND :B2 = C.TABLE_OWNER AND I.INDEX_NAME IN ( SELECT
 INDEX_NAME FROM DBA_INDEXES WHERE TABLE_OWNER = :B2 AND TABLE_NAME = :B1
 AND VISIBILITY != 'INVISIBLE' AND UNIQUENESS = 'UNIQUE') AND I.INDEX_NAME
 NOT IN ( SELECT C.CONSTRAINT_NAME FROM DBA_CONS_COLUMNS C WHERE C.OWNER =
 :B2 AND C.TABLE_NAME = :B1 AND C.CONSTRAINT_NAME IN ( SELECT C1.NAME FROM
 SYS.USER$ U1, SYS.USER$ U2, SYS.CDEF$ D, SYS.CON$ C1, SYS.CON$ C2, SYS.OBJ$
 O1, SYS.OBJ$ O2 WHERE U1.NAME = :B2 AND O1.NAME = :B1 AND D.TYPE# IN (2, 3)
 AND (D.DEFER IS NULL OR D.DEFER = 0 OR BITAND(D.DEFER, 36) = 4) AND
 (D.TYPE# = 5 OR D.ENABLED IS NOT NULL) AND C2.OWNER# = U2.USER#(+) AND
 D.ROBJ# = O2.OBJ#(+) AND D.RCON# = C2.CON#(+) AND O1.OWNER# = U1.USER# AND
 D.CON# = C1.CON# AND D.OBJ# = O1.OBJ#) AND EXISTS ( SELECT 'X' FROM
 DBA_TAB_COLUMNS T WHERE T.OWNER = C.OWNER AND T.TABLE_NAME = C.TABLE_NAME
 AND T.COLUMN_NAME = C.COLUMN_NAME)) AND EXISTS ( SELECT 'x' FROM
 DBA_TAB_COLUMNS T WHERE T.OWNER = :B2 AND T.TABLE_NAME = :B1 AND
 T.COLUMN_NAME = C.COLUMN_NAME) ) KEY ORDER BY KEY.KEY_NAME, KEY.POSITION


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     60.61      61.08          0      22034          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     60.61      61.08          0      22034          0           0

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.