DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Creating INDEXES

  1. #1
    Join Date
    Feb 2006
    Posts
    37

    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;

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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...

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Change the script to:

    CREATE INDEX XXX ON XXX
    (XXX)
    NOLOGGING
    PARALLEL 8 ;

    ALTER INDEX XXX LOGGING NOPARALLEL;

    Tamil

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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...

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can we discuss the reason for rebuilding the index every night?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Feb 2006
    Posts
    37
    There was a belief that dropping and recreating the index each night is the best way to go!!!

  7. #7
    Compared to what? What is the behavior of the table?

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is the table partitioned? How many rows do you add/modify each night?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  10. #10
    Join Date
    Feb 2006
    Posts
    37
    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
  •  


Click Here to Expand Forum to Full Width