Loading Nested Tables

jerome_rajan · Post by **jerome_rajan** » Mon Sep 01, 2014 4:22 am

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

priyadarshikunal · Post by **priyadarshikunal** » Mon Sep 01, 2014 6:30 am

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

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

chulett · Post by **chulett** » Mon Sep 01, 2014 8:05 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 01, 2014 4:30 pm

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.

jerome_rajan · Post by **jerome_rajan** » Tue Sep 02, 2014 1:42 am

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?

ray.wurlod · Post by **ray.wurlod** » Tue Sep 02, 2014 2:13 am

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.

jerome_rajan · Post by **jerome_rajan** » Tue Sep 02, 2014 2:18 am

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

jerome_rajan · Post by **jerome_rajan** » Tue Sep 02, 2014 3:39 am

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?

priyadarshikunal · Post by **priyadarshikunal** » Tue Sep 02, 2014 4:25 am

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

jerome_rajan · Post by **jerome_rajan** » Tue Sep 02, 2014 6:24 am

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.

chulett · Post by **chulett** » Tue Sep 02, 2014 7:06 am

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.