RE: Running time of index rebuild
Date: Wed, 5 Jun 2013 18:56:21 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D4992_at_exmbx06.thus.corp>
And when estimating, allow 4 bytes overhead per index entry and 2 bytes overhead per column, and don't forget to add in the rowed (plus 2 bytes). The way the data is stored during sorting is not the same as the way it's stored in the final index.
Time to build, if you do the create as 2select and sort", is probably close to n*log(n) - but there are a number of factors involved in how (and how often) you apply this formula - you've got CPU and disc activity to consider.
Building in memory is generally fastest if you have to compete with other sessions for disc, but if the discs (and the disc cache) is virtually dedicated to you then single pass with minimum memory is likely to be fastest.
Similarly, parallel execution introduces a massive CPU and stall overhead on messaging between PX slaves - so single threading can be faster if you are competing for CPU.
Are you going to drop and recreate, or rebuild online - either requires to scan and sort on the table - if you could fast full scan and sort the index it might be quicker (but locks the table), if you could force an index full scan (reading the index in order) you could count the number of blocks in the index, divide by 100, and use that as an approximate guideline for the time to build in seconds.
If you try rebuilding online and users are changing the data - and if your 16 hours is a reasonable estimate - then by the time you finish the tablescan you'll be reading blocks which have to be rolled back 16 hours - it is a relatively common phenomenon for very large index rebuilds to be aborted because the longer they run for, the slower they get until the work done by rolling back takes too much time and they can "never" catch up.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Powell, Mark [mark.powell2_at_hp.com] Sent: 05 June 2013 19:40
To: ORACLE-L
Subject: RE: Running time of index rebuild
Before rebuilding or drop/re-create of large indexes it is a good idea to compare the size of the index to the amount of temporary tablespace available. Over the last 20 years I have encountered a couple of cases where the indexes outgrew the temp allocation. This is not something you want to find out after you have dropped the index.-- http://www.freelists.org/webpage/oracle-l Received on Wed Jun 05 2013 - 20:56:21 CEST