Re: Creating multiple indexes

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Fri, 19 Nov 2010 12:14:45 +0200
Message-ID: <OF76A0E920.03194B01-ONC22577E0.00374A52-C22577E0.003848EF_at_seb.lt>



>Nick Pierpoint <mail_at_pierpoint.org
>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.

You assume cache size is enough to perform in-memory sort only. That brings interesting questions:

  • is it better to scan many times few columns required for a particular index but be able to perform in-memory sorting?
  • or is it better to scan all columns for all indexes but run into multiple sorts which require to flush data into disks (temporary tablespace in Oracle terms) ?

As I said - some optimizations can be done(as usual) but it is not straightforward. If you have ample of memory then you can effectively force caching of table blocks into memory (or storage memory), one way or another. Then your multi-index build won't scan disks any longer! If you have enough memory left for PGA then subsequent sorting won't touch disks any longer.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 19 2010 - 04:14:45 CST

Original text of this message