Oracle Database Discussions

Hi
I am trying to create the Index with Parallel option using Oracle 11g , to speed up the index creation process. But looks like its not running Parallel. Here's the DDL for 2 index's.

Size of the Table - EMP_DTL = 98 Million.

ALTER SESSION ENABLE PARALLEL DDL;

---Index 1

CREATE INDEX INDX1_EMP_FN ON EMP_DTL
(EMP_FST_NA)
TABLESPACE TEST
NOLOGGING
PARALLEL ( DEGREE 8)
LOCAL;

---TIME TAKEN - 50 MINS

--Index 2

CREATE UNIQUE INDEX PK_UNQ_EMP ON EMP_DTL
(SEQ_ID, JOIN_DTTI,DEPT_CD, CC_CLUB_CD, CC_HASH, CC_SEQ)
TABLESPACE TEST
NOLOGGING
LOCAL
PARALLEL 8;

---TIME TAKEN - 53 MINS

I tried with Default Parallel, parallel = 2,4,6,8,16, but i don't see index running parallel. Here's where i am checking


select inst_id,sid,serial#,opname,target,sofar,totalwork,time_remaining,message,QCSID
from gv$session_longops
where sql_id IN ( '4wqxz5wnhr7ms','32qyt5brckp6b')
and time_remaining <>0 order by time_remaining desc

But at the same time if I use below DDL's it runs parallel and I can see multiple threads on gv$session_longops.

INSERT /*+ APPEND */ INTO EMP_DTL
SELECT * FROM EMP_DTL_NEW
/

---TIME TAKEN - 32 MINS


CREATE TABLE EMP_DTL AS SELECT * FROM EMP_DTL_NEW;
/
---TIME TAKEN - 32 MINS

Is there syntax wrong in my Index script or do I need to add some more parameters to make Index Creation to run Parallel ?

Thanks
Jitesh