Ask The Real Tom

July 28, 2010



Enable and Validate Constraints in Parallel (Oracle)

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.

  1. Enable Constraint with novalidate option
  2. Set parallel degree on the table or on session with force ddl parallel
  3. 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;

April 14, 2009



Is it possible to parallize index rebuild with piplined functions?

Filed under: oracle — Tags: , , — admin @ 4:33 pm

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.

Powered by WordPress