-
Creating INDEXES
I am creating an INDEX for a datawarehouse table w/ 200M rows.
the index is created each night using 'NOPARALLEL' and takes 4 hours to finish. Just wondering if I could use the 'PARALLEL_INDEX' function in the create script to help cut down on some of that time, or any other suggesstions about parallel processing for building an index on a DW table.
CURRENT SCRIPT:
CREATE INDEX XXX ON XXX
(XXX)
LOGGING)
NOPARALLEL;
-
Why don't you create the index in parallel and then issue an ALTER INDEX x NOPARALLEL; once the create has completed?
Assistance is Futile...
-
Change the script to:
CREATE INDEX XXX ON XXX
(XXX)
NOLOGGING
PARALLEL 8 ;
ALTER INDEX XXX LOGGING NOPARALLEL;
Tamil
-
What about also creating an nologging index tablespace especially for indexes such as these (that are re-created on a nightly basis) and create everything in there as "NOLOGGING" and "PARALLEL x"?
That way in the event of failure you take the hit then and just re-create the index? Obviously you need to think carefully about your backup\recovery strategy and your standby if you have one.
Assistance is Futile...
-
Can we discuss the reason for rebuilding the index every night?
-
There was a belief that dropping and recreating the index each night is the best way to go!!!
-
Compared to what? What is the behavior of the table?
-
Is the table partitioned? How many rows do you add/modify each night?
-
Originally Posted by CENSUSDBA
There was a belief that dropping and recreating the index each night is the best way to go!!!
May be true for bitmap index only.
Tamil
-
The index is not a bitmap
Each night, there are roughly 200k rows updated/inserted.
The table has:
a PK
6 FK
151 fields
partitioned across 113 TBS
Thanx again for your help
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|