15 Jun
Posted by: gato in: Administration, DBA, Oracle
It is possible to speed up creation/rebuild process of a large index using keywords PARALLEL, and NOLOGGING:
ALTER SESSION ENABLE PARALLEL DDL; CREATE INDEX INDEX_OWNER.INDEX_NAME ON TABLE_NAME ( col1 , col2, ..) NOLOGGING PARALLEL TABLESPACE TABLESPACE_NAME / -- or ALTER INDEX INDEX_OWNER.INDEX_NAME REBUILD NOLOGGING PARALLEL /
Don’t forget to set back LOGGING and NOPARALLEL options, because create/rebuild will set these options.
ALTER INDEX INDEX_OWNER.INDEX_NAME LOGGING NOPARALLEL;
Estimate statistics:
BEGIN DBMS_STATS.GATHER_INDEX_STATS( OWNNAME => 'INDEX_OWNER' ,INDNAME => 'INDEX_NAME' -- Based on index size, for large indexes as -- as low as 3% or Oracle can decide using: -- DBMS_STATS.AUTO_SAMPLE_SIZE , ESTIMATE_PERCENT => 10 -- use PARALLELISM , DEGREE => DBMS_STATS.DEFAULT_DEGREE ); END; /
Warning: By using PARALLEL keywords you can easily exhaust all the server resources, which could cause problems on a production system, use with caution and on your own risk.
Leave a reply