DSXchange

Hi Guys,

We have a Oracle Table Patitioned by year column and Sub-Partitioned by Month column.How can i improve the performance of Datastage jobs using Oracle Patition table means how can i design the Datastage jobs by using Oracle Partition column and Sub-Partition Column.


Thanks in advance
Reddy

Multiple streams into Oracle for separate partitions (and maybe for separate sub-partitions, though this may not be worth the effort).

Ray,

Can you please elaborate how to do it in datastage.

Can you please help me on how to implement Oracle partitioning method using Datastage job.

Thanks in advance.

Reddy

First, I hope that if those sub-partitons are hash sub-partitions then there are fewer than 12 of them. Otherwise why the heck would you not just partition monthly? It's a far more productive use of the technology.

I'll assume that the partitioned table is a target of the ETL rather than a source, becasue the method for exploiting the benefits of partitioning differs.

As a target, you can split up your load data by partition and run many loads simultaneously. This is not a magic bullet though. You are going to be restricted by the speed of the database writer process, which is shared by everyone. So don't get too excited and start off 12 loads at once.

There are two good ways to split your load data:
1. If you've got a hyper-fast transformation, just direct the output to multiple files: one per partition. When complete, FTP them all to your database server (if it is remote) and start up single-partition sqlldr jobs - one for each file.

2. Split up your source data by the same partitioning logic, and transform in parallel with a separate DataStage job instance for each file. It might require a pre-parse to split the source into multiple files, but will probably be worth it.

Now, if those sub-partitions are HASH, then you will not (easily) be able to split the source or the load files, because you don't know which values go in which partition. Suggestion: get rid of the sub-partitions. They are not intended for use with time-based partitioning.