while indexes are created

[st2000]

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

[Mr.Hanky]

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

[prakashs43]

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.

[st2000]

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

[tamilselvan]

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.

[Mr.Hanky]

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

[pando]

alter session set sort_area_size

create index nologging, nosort, parallel

[vijay8282]

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