Home » SQL & PL/SQL » SQL & PL/SQL » Index performance in Loading Target during ETL process (Oracle 10g)
Index performance in Loading Target during ETL process [message #313629] Mon, 14 April 2008 00:23 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
During ETL process, after Extract and Transform had completed in normal time, the time taken to Load the target DB is vey high. There were a few indexes created on tables specifically when Extract process was slow. This expedited Extract but it now impacts Loading Target. Without the index loading to target is faster.
Is there a way in which the index on Target DB is set to off during Load and On at a later convenient time. How does the index get built at a later time.
Also does Analyzing Table, rebuild indexes too ?
Re: Index performance in Loading Target during ETL process [message #313634 is a reply to message #313629] Mon, 14 April 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way in which the index on Target DB is set to off during Load and On at a later convenient time

Drop them and recreate them load.

Quote:
Also does Analyzing Table, rebuild indexes too ?

No, "analyze" analyzes. To rebuild, use... "alter index rebuild".

Regards
Michel
Re: Index performance in Loading Target during ETL process [message #313685 is a reply to message #313634] Mon, 14 April 2008 03:23 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you use SQL*Loader with option DIRECT=TRUE, then index maintenance will be deferred until the load is complete. At this time, it will update the index in a single pass with a merge-style process that is generally much faster than both the conventional transactional maintenance and the drop/rebuild approach.

Ross Leishman
Previous Topic: sqlplus can't start
Next Topic: Help in Dates
Goto Forum:
  


Current Time: Mon Dec 05 06:44:30 CST 2016

Total time taken to generate the page: 0.11399 seconds