Re: Running time of index rebuild
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "giantpanda@xxxxxxx" <giantpanda@xxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 Jun 2013 12:09:34 -0700 (PDT)
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@xxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
