http://asktom.oracle.com/pls/asktom/f?p=100:11:974743640809258::::P11_QUESTION_ID:442029737684

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074

Tom,
    I am presently working on a Data Warehouse project which is using Informatica for 
mapping and loading data into an Oracle database (8.1.7 on HP UX 11.0). The data is in 
flat files and some of the hundreds of tables in the Warehouse are HUGE (300-500 million 
records). The designers have not partioned the base tables into which this data is being 
loaded. Their contention is that since we are using SAN ( storage Area Network) that has 
multiple disks we don't need to partion the tables. I am not sure if this is right. Also 
they have primary keys on the tables. My question is - what is the best way to speed up 
the loading process? What init.ora parameters need to be set? If they agree to partition 
then whether it is better to partion by range or by hash? Is it possible to use parallel 
loading of a table if yes then How? 
    When I question I am told that since they are using Informatica we will have this 
kind of performance. I don't understand why one would use a 1/2 million package and have 
such horrible performance. Please let me know in yr opinion what is the most optimum 
method of loading such huge amount of data.

Thanks in advance. 

and we said...

The designers of this system are not database guys are they.  Well, that is a rhetorical 
question -- they aren't.

Partitioning provides many things -- partition elimination can make your queries go 
faster in a data warehouse.  many times faster.

Partitioning allows for easy aging of data, rolling windows.  Something virtually every 
data warehouse does.

Partitioning allows for higher availability when that soft corruption kicks in and it 
will.  better to have a single, small partition offline to be rebuilt then the entire 
table.

Partitioning eases adminstrative burdens.  It is a fact that performing operations on 
small objects is inherently easier, faster and less resource intensive then performing 
the same operations on a large object.

Partitioning makes mass updates -- something you might need to do in a data warehouse, 
might not -- faster.  You can use parallel DML, but only if you partition.

And the list goes on.

We've had this "multiple disk" thing from day 1 in databases.  There has ALWAYS been more 
then one disk.  Enter raid striping -- a SANS didn't do anything new here.  Your 
designers haven't worked on large data warehouses -- or if they have, they've never used 
partitioning and don't understand it.



Loading a huge amount of data -- i would be using sqlldr in parallel direct path mode.  I 
would be using parallel index builds with no logging.  It would not take very long at 
all.