George, I'm not going to answer your question (since it already has been), but here are some ways to speed up the index builds:
1. ALTER SESSION SET SORT_AREA_SIZE=100000000; As long as you have 100 MB of free physical memory on the machine, doing this before the index build will speed up the sort operations that are a part of the index build. I've even used higher numbers. 2. Add the PARALLEL clause in the index build statement. If your table is partitioned, then the PARALLEL clause will use the number of partitions as the degree of parallelism, though you can change that if you want. 3. Add the NOLOGGING clause in the index. After all, if the build fails, you're going to run it again not try to recover right? Using these things together has made a huge improvement. One index build was running for over 8 hours. After changing these things (using 500MB for the sort area size since the machine had the available RAM) and the build ran in 10 minutes! This was on a table with over 30 million rows. Good luck. Stephen >>> [EMAIL PROTECTED] 09/28/02 05:48AM >>> Hi there How can I see how far my index build has is. I got a table with 43 mil records. It is taking forever as expected but would like to see how far it is, Any idea. Oracle 8.1.6.3 (32Bit) on Solaris 2.6 on Sun E10K George ________________________________________________ George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! *************************************************************************************** This message contains information intended solely for the addressee, which is confidential or private in nature and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message or any file attached to this message. Any such unauthorised use is prohibited and may be unlawful. If you have received this message in error, please notify the sender immediately by e-mail, facsimile or telephone and thereafter delete the original message from your machine. Furthermore, the information contained in this message, and any attachments thereto, is for information purposes only and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of Dimension Data (South Africa) (Proprietary) Limited or its subsidiaries and associated companies ("Dimension Data"). Dimension Data therefore does not accept liability for any claims, loss or damages of whatsoever nature, arising as a result of the reliance on such information by anyone. Whilst all reasonable steps are taken to ensure the accuracy and integrity of information transmitted electronically and to preserve the confidentiality thereof, Dimension Data accepts no liability or responsibility whatsoever if information or data is, for whatsoever reason, incorrect, corrupted or does not reach its intended destination. ************************************************************************************* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Leonard (ZA) INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
