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
Oracle Partition Table
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
rleishman
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
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.
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.
Ross Leishman