Re: Creating multiple indexes

  • From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
  • To: mail@xxxxxxxxxxxxx
  • Date: Thu, 25 Nov 2010 05:56:53 -0800 (PST)

Well it's been a while since I did this, but as I recall if you kick off all of 
your index builds (or rebuilds) at the same time (DBMS_Scheduler or DBMS_Job) 
and without parallelism then you'll generally get 1 index creation process 
performing the physical reads and the other 9 (for example) processes greedily 
waiting for the blocks to load into the buffer.

You see a very high number of buffer busy waits (10.2, but a different event in 
11+ I think) which looks alarming to the Uninitiated (including many tools that 
produce automatic tuning recommendations) but it's not a problem in this 
of course.

I hope I'm remembering this correctly.

From: Nick Pierpoint <mail@xxxxxxxxxxxxx>
To: Laimutis.Nedzinskas@xxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Thu, 18 November, 2010 9:12:53
Subject: Re: Creating multiple indexes

On Wed, 2010-11-17 at 08:18 +0200, Laimutis.Nedzinskas@xxxxxx wrote:
> >I'm trying to find the reason why there isn't a
> mechanism to create multiple indexes at the same time - a "create
> indexes" statement or similar.
> > Each create index needs to scan through every row in the table to form the 
> > i.e. 10 indexes = 10 full scans.
> b-tree initial construction requires sorting
> (
> Let's use a merge-sort algorithm (,
> ok?
> Merge algorithm does scan all rows but it also does more than that.

Are you saying that index creation requires more work than the initial
table scan so any benefits derived from creating multiple indexes at the
same time with a single scan would be countered by the additional

I don't see this. I'd have thought that the first "data gathering" stage
of index creation would benefit from caching each column as part of a
single table scan.



Other related posts: