Re: Creating multiple indexes
From: Nick Pierpoint <mail_at_pierpoint.org>
Date: Thu, 18 Nov 2010 09:12:53 +0000
Message-ID: <1290071573.2687.13.camel_at_rollins>
On Wed, 2010-11-17 at 08:18 +0200, Laimutis.Nedzinskas_at_seb.lt 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.
>
>
Date: Thu, 18 Nov 2010 09:12:53 +0000
Message-ID: <1290071573.2687.13.camel_at_rollins>
On Wed, 2010-11-17 at 08:18 +0200, Laimutis.Nedzinskas_at_seb.lt 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 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 18 2010 - 03:12:53 CST