Re: Creating multiple indexes

  • From: Laimutis.Nedzinskas@xxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 19 Nov 2010 12:14:45 +0200

>Nick Pierpoint <mail@xxxxxxxxxxxxx
>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.

--
//www.freelists.org/webpage/oracle-l


Other related posts: