Enabling and validating constraints is can take a huge amount of time in big oracle databases.
To speed the validaten up, it can be done in parallel.
This can save you valuable time in release windows.
- Enable Constraint with novalidate option
- Set parallel degree on the table or on session with force ddl parallel
- Enable nable the constraints with validation
Example:
ALTER TABLE T_1 ADD CONSTRAINT NC_1 not null (colname) disabled;
ALTER TABLE T_1 ENABLE NOVALIDATE CONSTRAINT NC_1;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER TABLE T_1 PARALLEL 8;
ALTER TABLE T_1 ENABLE VALIDATE CONSTRAINT NC_1;
ALTER SESSION DISABLE PARALLEL DDL;
I was just wondering if how I could rebuild multple indexes of a table simultaneously (but not parallel)
My test case:
whenever SQLERROR continue;
create user test4 identified by test4;
grant create session to test4;
grant resource to test4;
connect test4/test4
set serveroutput on
CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER
/
show errors
create or replace function ptest(p_cursor IN SYS_REFCURSOR)
return number_ntt
pipelined
parallel_enable
is
v_number number;
begin
loop
fetch p_cursor into v_number;
EXIT WHEN p_cursor%NOTFOUND;
execute immediate 'alter index i1 rebuild';
dbms_output.put_line('doit for: '||v_number);
pipe row (1);
end loop;
end;
/
show errors
create table t1 (id1 number);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
create index i1 on t1 (id1);
select /* PARALLEL(4) */ * from table(ptest( cursor(select id1 from t1) ));
connect / as sysdba
drop user test4 cascade;
My result: NO
SQL> @pipe
User created.
Grant succeeded.
Grant succeeded.
Connected.
Type created.
No errors.
Function created.
No errors.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Index created.
select /* PARALLEL(4) */ * from table(ptest( cursor(select id1 from t1) ))
*
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "TEST4.PTEST", line 12
Connected.
User dropped.