topic Re: Loading a ORACLE table in parallel using DI studio in SAS Enterprise Guide

Loading a ORACLE table in parallel using DI studio

deleted_user — Wed, 01 Apr 2009 06:03:21 GMT

Let’s say I have a SAS dataset with 40 million records and I need to load this to a ORACLE dimension table. Is there a way I can split this file of 40 million into say 20 million each and process them in parallel , so that load time to ORA dimension will be reduced? This is like how we use DBSLICE when reading from ORA or RDBMS systems , but while writing to ORA ,can we do some parallel processing in the same dimension load?

Re: Loading a ORACLE table in parallel using DI studio

LinusH — Wed, 01 Apr 2009 07:32:29 GMT

The fastest way to write large amount of data to Oracle is by using bulk-load. I assume that you already have looked into that. I don't whether this done in parallel in Oracle, maybe the indexes are created in parallel to table load. So from my point of view, this might be more of a Oracle DBA question. Maybe you want to look into to do parallel processing by loading different tables in parallel, in that way you will probably shorten the total run-time.

/Linus

Re: Loading a ORACLE table in parallel using DI studio

LinusH — Wed, 01 Apr 2009 13:09:03 GMT

Wait a minute. I just saw this one:

BL_OPTIONS = ‘PARALLEL=TRUE’

See online doc or this Global forum paper of how to use:

http://support.sas.com/resources/papers/proceedings09/099-2009.pdf

/Linus

Re: Loading a ORACLE table in parallel using DI studio

deleted_user — Wed, 01 Apr 2009 13:11:39 GMT

thanks!

Re: Loading a ORACLE table in parallel using DI studio

deleted_user — Thu, 02 Apr 2009 06:17:46 GMT

Thanks Linus for your inputs! This is really helpful!
I went through the document you mentioned.
Looks like parallel load is only possible in the case of bulk load.

Let me briefly explain the scenario I’m looking at.
I have a job in DI studio which is as follows –

SAS datasest (40 M records) --> SQL join transform ---> Tempobject --> Lookup transform ---> Tempobject ---> SQL join transform –> Tempobject ---> Table Loader(update/Insert Technique) --> ORA DIMENSION table

So this is ideally not a case of bulk insert. Do you know in such cases any parallel processing that can be done. By parallel processing I’m looking at splitting the 40 million source file into say 20 million each and then loading them in parallel to ORA.
I’m doubtful whether this is possible. Any thoughts?

One option is to load the data into a target SAS dataset and then use bulk load to ORACLE table. I’m trying to see if there are ways to avoid this as I do not want to keep a replica SAS dataset of the ORA table.

Re: Loading a ORACLE table in parallel using DI studio

LinusH — Thu, 02 Apr 2009 06:43:37 GMT

I guess that your 40'' records are potential updates/inserts. So the question is how many of them becomes updates/inserts in your load step in a typical load?
If a major part is inserts, I still think that bulk-load is an alternative for the inserts. Carefully go through the log to find out where the bottle necks of your load step are. There are many things going on in the table loader...

/Linus

Re: Loading a ORACLE table in parallel using DI studio

deleted_user — Sat, 04 Apr 2009 12:10:39 GMT

Thanks Linus....for great help....

i think u r working with SAS for long time....right?

Re: Loading a ORACLE table in parallel using DI studio

LinusH — Sun, 05 Apr 2009 10:38:15 GMT

Well, it has been 15 fun and interesting years (gee, time flies...).

/Linus