Performance tuning for Load into Oracle table

[sumesh.abraham]

Hi All,

I'm extracting records from an Oracle table which has 142 million records using Oracle Enterprise stage. I do transformations for the columns and load it to a data set. Finally I'm loading from the data set into another Oracle table (This too has 130 million records) which is child of the first table. What are the essential things that needs to be taken care of in this scenario?

1) In the Orcale Enterprise stage, for extracting ,I've specified Read Method as Table and I don't have any information about the partitioning of the table,if any. Would the performance be better if I use a User-defined query against the table ordered by the keys.
2) In the Orcale Enterprise stage for loading to the Oracle table, I've specified the Write method as Load. How can I do indexing of the keys here?

I would appreciate any suggestions which can enhance the performance tuning.

Thanks,
Sumesh

[gnan_gun]

When u r extracting from Database table. Better to use hash Partition. Because for hash partiotion have to give partition on Key column. So that Performance will increase.

[ArndW]

(1) Table method is quite efficient, there is no need to use a SQL query
(2) The Load method is the most efficient. Indexing can be controlled in Options -> Index Mode portion of the Oracle stage definition.

[sumesh.abraham]

Thanks for the replies. I've some queries.

1) I believe that the table should be partitioned for enabling us to set the partitioning in the Oracle stage? How can I know whether it is partitioned or not?

2) Is there any way to set partitioning in the Oracle stage while extracting data?

3) I'm not able to find Options->Index mode in the Oracle Enterprise stage. Also do we need to set the Index mode during extraction and/or loading. What are the Index modes available?

Thanks a lot,
Sumesh

[ArndW]

Tools such as Toad will display partition information in easily read forms, or you can get the data directly from Oracle, see this page for some hints. Once you know your partitioning method you can reflect that in your DataStage jobs and finally, those options will not show up unless you specify "Load" method.

[sumesh.abraham]

Once you know your partitioning method you can reflect that in your DataStage jobs and finally, those options will not show up unless you specify "Load" method.

I understand from your above statement that

-These options will be available and can be mentioned in the Oracle Enterprise stage only when the Write Method is Load. These cannot be done in the stage where extraction is done. Please confirm.

[ArndW]

sumesh - instead of me confirming things, have you thought about actually reading the documentation or help files?