>Instead of just doing
>Alter TAble Add contraints primary key using index in which oracle will build
>the pk index without the parallel option (which is much slower)

Does this still apply in 9iR2 - ie, the only way to (re)build the pk-supporting index in parallel is to pre-create it ?

[if you're busy just tell me and i will post this as a question later]

More context and test case:

create table t (x constraint t_pk primary key)
as select rownum from all_objects where rownum <= 100;

alter table t modify constraint t_pk disable;

Now i want to re-enable the pk, without specifing the index columns - the idea is to rebuild in the simplest way possible, focusing on minimizing human errors (swapped column order, missing columns, etc).

I tried:

dellera@ORACLE9I> alter table t modify constraint t_pk using index parallel 8 enable novalidate;
alter table t modify constraint t_pk using index parallel 8 enable novalidate
*
ERROR at line 1:
ORA-03001: unimplemented feature

Then i tried:

dellera@ORACLE9I> alter session force parallel ddl parallel 8;

Session altered.

dellera@ORACLE9I> alter table t modify constraint t_pk enable novalidate;

Table altered.

Unfortunately the index is created serially (as v$px_session told me).

Surely i can generate the index ddl using a pl/sql program reading the data dictionary, or using "imp indexfile", or using dbms_metadata, or similar ways - i just wondered if there's already a native way such as the two commands that i tried above (9.2.0.5).

Thanks (and sorry if i bothered you)

Hi

I noticed that when we enable a constraint a way or another Oracle does parallelquery in one way and the other not.

If I do this (EMP has parallel degree of 8 and no statistics)

alter table ISMS_HEN_CONTROLENTRADAS_HCO
enable novalidate constraint
ISMS_HEN_CONTROLENTRADAS_FK;

then

ALTER TABLE "ISMS_HEN_CONTROLENTRADAS_HCO" ENABLE CONSTRAINT ISMS_HEN_CONTROLENTRADAS_FK; then Oracle does parallel query to enable the constraint

However if I do

alter table ISMS_HEN_CONTROLENTRADAS_HCO
enable validate constraint
ISMS_HEN_CONTROLENTRADAS_FK;

It doesnt

I tkprofed both way and they show same query but differet execution plans.

PARALLEL:

select /*+ all_rows ordered */ "A".rowid, 'LSC',
'ISMS_HEN_CONTROLENTRADAS_HCO', 'ISMS_HEN_CONTROLENTRADAS_FK'
from
"LSC"."ISMS_HEN_CONTROLENTRADAS_HCO" "A" ,
"LSC"."ISMS_HEN_CONTROLENTRADAS_DAD" "B" where( "A"."ISMS_HEN_SEMANA" is
not null) and( "B"."WEEKNO" (+)= "A"."ISMS_HEN_SEMANA") and( "B"."WEEKNO"
is null)

Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
0 NESTED LOOPS OUTER
0 TABLE ACCESS FULL ISMS_HEN_CONTROLENTRADAS_HCO
0 INDEX RANGE SCAN ISMS_HEN_CONTROLENTRADAS_PK (object id 11462)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Parse Reply 2 0.00 0.00
PX Deq: Execute Reply 19 0.08 0.69
PX Deq: Table Q Normal 2 0.00 0.00
PX Deq: Signal ACK 2 0.09 0.09


NON-PARALLEL:

select /*+ all_rows ordered */ "A".rowid, 'LSC',
'ISMS_HEN_CONTROLENTRADAS_HCO', 'ISMS_HEN_CONTROLENTRADAS_FK'
from
"LSC"."ISMS_HEN_CONTROLENTRADAS_HCO" "A" ,
"LSC"."ISMS_HEN_CONTROLENTRADAS_DAD" "B" where( "A"."ISMS_HEN_SEMANA" is
not null) and( "B"."WEEKNO" (+)= "A"."ISMS_HEN_SEMANA") and( "B"."WEEKNO"
is null)

Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
581082 NESTED LOOPS OUTER
581082 TABLE ACCESS FULL ISMS_HEN_CONTROLENTRADAS_HCO
581082 INDEX RANGE SCAN ISMS_HEN_CONTROLENTRADAS_PK (object id 11462)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 1077 0.00 0.04
db file sequential read 6 0.00 0.00


I dont see what is the difference between the two queries/ways?