Re: Creating multiple indexes
Date: Wed, 17 Nov 2010 17:13:20 -0800
Message-ID: <AANLkTikj4Q-tw_d4iwje=0pyQRXgWno7Nc8WfY8umaLk_at_mail.gmail.com>
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_at_pierpoint.org> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 17 2010 - 19:13:20 CST