DSXchange

Hi,

I'm looking for advice on whether a Server Job or a Parallel job is more capable to efficiently load an Oracle nested table.

Any pointers to possible design approaches would be very helpful. Thank you

It would be better if you try to explain the scenario.

else if you are calling for blind votes, I would vote for parallel.

They are tables that hold tables - basically sub-tables / multivalued data. For the 'efficient' part, I would think we'd need to know the volumes involved.

Are there SQL extensions in Oracle for accessing the nested tables? Something like the UNNEST keyword in UniVerse?
If so, you can probably supply user-defined SQL to take advantage of the nested table functionality.

We have internally decided to use parallel jobs. The only reason why the 'parallel or server' question came up was because all of the existing ETL has been built using server jobs and the support guys were very skeptical due to their lack of experience in handling parallel jobs. We've now convinced the client about the many advantages that the parallel infrastructure brings with it.

My question now, is more towards how to read from a source file and load into a nested table. A sample of the data is



The first 3 characters of each record are the record type. We are looking to have a nested table of D10 records and another of D20 records with H10 being the parent

--------------------------
Craig,
Can you please move the thread to the parallel forum?

Treat this as a parent/child problem. Insert the parent records into the base table and the child records into the nested table. You may like to use a Distributed Transaction stage.

Thank for the guidance, Ray. I'll try this approach and post tangible updates, if any.

Stupid beginner questions since this is my first time with the DT stage.

1. Can a Distributed Transaction Stage be used without a MQ Connector?
2. What purpose would the DT stage serve in my case as against a standard Oracle connector?

Sorry for not being descriptive and yes I wanted to know about the data volumes before selecting one of them.

I wasn't able to use the DT stage due to lack of sufficient knowledge. But I managed to implement it using a sequence of oracle connectors with the parent/child approach. Thank you for your timely assistance.

Done.

For the record, Oracle has a TABLES() function to access and 'unnest' these but loading you use normal INSERT statements. Well, mostly normal as you do have to worry about setting up the constructor properly.

Good job.