BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
7 REPLIES 7
LinusH
Tourmaline | Level 20
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
Data never sleeps
deleted_user
Not applicable
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.
LinusH
Tourmaline | Level 20
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
Data never sleeps
deleted_user
Not applicable
Thanks Linus....for great help....

i think u r working with SAS for long time....right?
LinusH
Tourmaline | Level 20
Well, it has been 15 fun and interesting years (gee, time flies...).

/Linus
Data never sleeps
LinusH
Tourmaline | Level 20
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
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1974 views
  • 0 likes
  • 2 in conversation