Re: Running time of index rebuild
Date: Wed, 5 Jun 2013 12:09:34 -0700 (PDT)
Message-ID: <1370459374.26413.YahooMailNeo_at_web121603.mail.ne1.yahoo.com>
Let me clarify my hasty initial reaponse.
Each index takes one full table scan. HOW that is processed makes a big difference in the execution time. For example, if you have sufficient RAM to do this processing in-memory the index creation will run faster than if you need to use your temporary tablespace for the sort or table scan activity. MESSAGE
Table Scan: GRIBNAUT.EMP: 486461 out of 2757392 Blocks done
USERNAME USER SESSION_ADDR ------------------------------ ------------------------------ ----------------SESSION_NUM SQLADDR SQLHASH SQL_ID
----------- ---------------- ---------- ------------- TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# ------------------------------- --------- --------- ---------- ----------
EXTENTS BLOCKS SEGRFNO#
---------- ---------- ----------
GRIBNAUT GRIBNAUT 000007FF6285FCC8
35 000007FF58A89BF8 3179200636 24zvjzuyrxh3w
TEMP TEMPORARY LOB_DATA 201 77184
1 128 1
David Fitzjarrell
From: Ingrid Voigt <giantpanda_at_gmx.net> To: oracle-l_at_freelists.org
Sent: Wednesday, June 5, 2013 8:56 AM
Subject: Running time of index rebuild
Hi, does anybody know how the running time for index create or rebuilds inOracle changes withnumber of lines / table size? I would expect O(n log n)? We need a guesstimate for the time for a complete rebuild where the test environment is considerably smaller: TEST: 19 mio rows / 16 GB / 15 minutes PROD: 894 mio rows / 725 GB / ??? Refilling test with complete production data is not an option. I know that index rebuilds are not usually necessary. In this case we need to gain enough space in the database to last until the new SAN is usable, and 50 GB (as predicted by the segment advisor) would be very helpful. My suggestion for the maintenance window an prod would be 16 hours, do you have a better idea? Best regards IngridVoigt
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 05 2013 - 21:09:34 CEST