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
context
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
>index.
> > i.e. 10 indexes = 10 full scans.
>
> b-tree initial construction requires sorting
> (http://en.wikipedia.org/wiki/B-tree#Initial_construction)
> Let's use a merge-sort algorithm (http://en.wikipedia.org/wiki/Merge_sort),
> 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
overhead?
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.
--
Nick
--
//www.freelists.org/webpage/oracle-l
Other related posts:
