while indexes are created
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: while indexes are created

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hi : I am creating a unique index on 65 million rows for a table that is about 3GB. I have allocated about 3.5GB for the index creation. How can I monitor this while this index creation is happening, since when I coming closer to end of datafile or extents, I can add more space..
    I tried dba_extents and dba_free_space for this index tablespace and this TS is going to hold only this index..

    Thanks, ST2000

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Well in the future you can always allocate more than you need. Since this is a clean build you can go back and re-size the datafile when the index is built. This way you insure that it gets built the first time. If you have OEM you can refresh the tablespace view and monitor from there.


    MH
    I remember when this place was cool.

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Monitoring growth of dba_extents and dba_free_space
    could be enough if extents size is small .
    if u have a very large initial extent then may physically checking the average approximate size of the indexed row with rowid may help u in monitoring the growth of the index.
    siva prakash
    DBA

  4. #4
    Join Date
    Oct 2000
    Posts
    449
    The question is how do I monitor the index growth?

    I know after the creation of the index, I can query dba_data_files for allocation, dba_extents for extent_allocation and dba_free_space for free space ..
    (All the above in extents.)

    I have uniform extents of 100MB in a LMTablespace..

    After the creation, I found that it took 2GB..and took almost 1.5 hrs for about 60 million rows/3GB data..

    But while the indexes are created, in that 1.5 hrs, how can I query the db to see how the index creation is growing???

    Thanks, ST2000


  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you create the index in parallel mode?

    If so, you can query from DBA_EXTENTS where segment_name will be in numbers (some thing like 35.213) and segment_type like 'TEMP%'. This will give rough idea how much have been done so far.

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    I hate to keep NOT answering your question but....


    You can use a parrallel hint to speed up the index create, you can also increase the sort area size at the session level. This can have a dramatic affect on index creates.

    MH
    I remember when this place was cool.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    alter session set sort_area_size

    create index nologging, nosort, parallel

  8. #8
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    If i rebuild the index which takes so much time using parallel clause.

    Do i need to reset the parallelism after the index gets rebuild ?

    I gues i read something abt thi sin this forum itself, but I m unable to locate that threah ?

    vijay
    --------------------------
    The Time has come ....

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