Re: Creating multiple indexes
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: mail@xxxxxxxxxxxxx
- Date: Wed, 17 Nov 2010 17:13:20 -0800
My first (and slightly orthogonal) question would be: Is there a good
requirement for all these indexes (because frequently people are
smothering their DWs with indexes)? Given this is a DW, maybe other
technologies would be better suited for query performance (like
partitioning and maybe local indexes). Most certainly a partition
scan and local index build will touch less data than a full table scan
and global index. Work smarter, not harder.
Using parallel execution (PX) (and nologging) would be at the top of
the list for these types of activities - that why it exists. Doing a
serial index build uses 1 process and thus 1 CPU. Why not engage more
resources to shorten the activity? Just be aware that a high
DOP/degree engages more resources so you will leave less for others on
the system, however, if this process is the only one (or the most
important one) then by all means use as much as possible if your
objective is to shorten the duration as much as possible. You don't
get unused CPU cycles back -- they are gone forever! If you have the
entire system and can run it at 100% for a while, then by all means do
so. DW is not like OLTP - you can use all the resources and feel good
about it (as long as others are wanting those same resources - then
you need resource management).
On Tue, Nov 16, 2010 at 1:32 PM, Nick Pierpoint <mail@xxxxxxxxxxxxx> wrote:
> Say I have a huge table and I want to create indexes on a dozen or so
> columns. I'd fire off several sequential create index statements and go
> off and boil the kettle.
>
> I know I can speed things up by partitioning the base table and creating
> the index in parallel. I could also fire off each create index
> simultaneously in separate sessions and hope the database buffer cache
> saved the day, but this seems sub-optimal.
>
> Any ideas or comments most welcome.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
