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

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.