RE: DWH loading : Do you use 2 types of tablespaces ?

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 1 Jun 2004 11:23:16 -0500
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607B00704@EXCHMN3>

Philippe

Someone else may weigh in with another opinion, but based on the limited amount of information you have provided, my guess is that the best practice is 1) whatever works reliably 2) produces the fewest side effects
(ex. the least wasted space, has the data available for users in time) 3)
isn't likely to bite you later. My questions are:

Do you need to perform the alter table move for a reason other than recouping the wasted space induced by using parallel loaders?
Is the combined time for the parallel load plus the table rebuild shorter than the time to perform a less aggressive load that doesn't create as much wasted space?
What do you do with the data once you load it? Exchange it into a partition? Question #1 may answer this. As far as whether to rebuild the table within the tablespace or a different tablespace, you may find you get better performance by using a separate tablespace since you will have an opportunity to involve more than one storage device. Whether this buys you much depends on your physical configuration.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of NGUYEN Philippe (Cetelem)
Sent: Tuesday, June 01, 2004 10:04 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: DWH loading : Do you use 2 types of tablespaces ?

Thanks for your reponses :

Dareell : I do 'alter move ' 2 or 3 times and it works===> COOL !!
Ryan : my table is set with pctused 99 and pctfree 1 but as my ETL
(PX+Dastage) is using parallel loaders (x4) with Direct Path I suppose that
each nodes used new extents for its loading. So we waste space.
Dennis : is it a bests practises doing so ? (I mean using 2 type of tbs ?)

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jun 01 2004 - 11:23:53 CDT