CODEWITHSUNDEEP
sqloracle-databaseindexing
BaseBallBatBoy
BaseBallBatBoy

Reputation: 685

How to speed up PK and index creation on Oracle

Creation of a PK and index on 880 mio rows takes about 1:15 hrs.

Query:

 alter table STG.NOTES add constraint PK_NOTES primary key (NOTES_SK);
 create unique index STG.BK_NOTES on STG.NOTES (NOTE_NBR ASC);

Is there a way to speed this up? I read of parallel and nologging options. The Oracle Server has 2 CPUs, so parallel (n-1 = 2-1 = 1) wont help. Leaving only nologging. What can you tell me about it? What is the trade off? Why would/wouldn't you use it?

edit: maybe I also need to explain what the context of index creation is:

  1. truncate target table, then drop PK and index on target table

  2. select from source and bulk insert into target

  3. create index and PK on target table (= 1:15 hrs)

Upvotes: 2

Views: 2643

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

NOSORT If NOTE_NBR is already ordered you can save time by not sorting the data. This may be true if NOTE_NBR is created from a single-threaded sequence or if your query is ordered.

Don't re-gather index statistics Creating or rebuilding indexes automatically creates the index statistics. If you re-gather statistics on the table, cascade=>false can save some time. The default option, cascade=>dbms_stats.auto_cascade does not really work.

Upvotes: 0

haki
haki

Reputation: 9759

Actually you can use a higher degree of parallelism on a pc with 2 cores. check the parallel_max_servers for the default setting in you db.

show parameter parallel_max_servers

To perform in parallel you can use

alter session force parllel ddl parallel 4

8 slaved will be created in addition to the coordinator.

About nologging,I think it's a good option. using nologging means that in the event of a disaster you'll have to recreate your indexes. If the index creation process is a routine, you can consider it.

Upvotes: 1

Related Questions