Re: Creating Index on 7 million row table!

That seems a little excessive, However i do not know the detailed config of your box but I have create concatenated indexes on 15 million row tables and its taken maybe 3 - 4 hrs on sequent C-80 with 18 pentiun 200Mhz processors. If you have multiple processors you can explore prallel processing. This will have to be carefully configured as you will need to set several int.ora parameters to support this.
Also are you sure there is or was no query or process accessing the table while you were creating the index. And for maintanance purposes use the rebuild index syntax it is quicker as it uses the data from the old index to rebuild the new as opposed to getting it from the table. You could also partition the table ( Hash based) and create partitioned indexes in parallel utilizong all CPUs

Ayokunke Giwa

Carol Kilner wrote:

> I have to create a single index on a single column in a table that contains
> approximately 7 million rows. I just completed the job and it took about 30
> hours to complete. Is this reasonable given the following conditions, or is
> there someway to speed this up.
>
> - Machine is a High end Sun solaris box with lots of memory (1Gig) and lots
> of Disk space
> - Running Oracle 8
> - Field to index is not sorted and is about 18 characters (Vehicle
> Identification Numbers)
> - The index is in a tablespace that is different from the data and the
> temporary tablespace and also located on physically different disks.
> - I used the nologging option when creating the index. I use a large
> initial extent.
> - Sort size is fairly large
>
> Is there anything else I can do to make dealing with this index less
> painful, short of having the data loaded into the table already in sorted
> order (which I'm not sure if possible!).
>
> Any suggestions would be greatly appreciated!
> Thanks,
>
> Carol Kilner
>
> ckilner_at_protectair.com
Received on Sun May 30 1999 - 11:49:42 CEST