BookmarkSubscribeRSS Feed
Yeti
Calcite | Level 5

Greetings,

I have a large dataset that is appended to multiple datasets (i.e. monthly and history oracle tables).

Currently I am using bulkload to load it into these two Oracle tables.  It is working just fine but it takes twice the amount of time since it creates the same DAT file again to load to next table.

Is there a way I can append this dataset to multiple datasets at the same time?  Are there options to re-use the BL.DAT file without recreating?  I googled for answers to no avail.

Thank you very much.

Have a great day.

Naresh

4 REPLIES 4
ballardw
Super User

I should think it possible to upload the set to Oracle once and then use pass through SQL to append to the target tables.

jakarman
Barite | Level 11

It is the bulkload process that is the dedicated fastest Oracle loader SQL-loader that is creating tables at Oracle.  http://support.sas.com/resources/papers/proceedings09/099-2009.pdf. Without the loading process is much slower (journaling/logging and commits). It is the SAS (Is it?) creating that dat-file yup SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition
For reuse that dat-fil it must

a/ be able to be kept. BL_DELETE_DATAFILE=NO (yes is default)

b/ reuse that dataset letting SAS not create that .dat file.

   I see what you mean there is nothing there to stop that.

   The only options would be to start the SLQ-Loader manualy/scripted. SQL*Loader Control File Reference probably not worth all the effort introducing new risks/errors.

Tip: review the  BL_DEFAULT_DIR='host-specific-directory-path' as that one should fast and big enough.

---->-- ja karman --<-----
Yeti
Calcite | Level 5

Thank you very much gentlemen.  I think I am not going to spend too much time on this one.

I will keep bulkload for one process and for another load process, I think will just use INSERTBUFF=32767 buffsize=32000 in the libname option.

Thank you all.  Have a great weekend.

Naresh

Patrick
Opal | Level 21

I believe the "best" way would be to load the source table once via bulk into an Oracle staging table (not sure if it would work with a global temporary table) and then use pass-through SQL with an append hint in a multi-table insert. Something like below:

INSERT /*+APPEND */ ALL
  INTO <Ora table 1>
  INTO <Ora table 2>
SELECT  * from <staging_table>

In case you have table partitioning then loading directly into a partition can also speed up things quite a bit.

"I think will just use INSERTBUFF=32767 buffsize=32000": Make sure you set also DBCOMMIT to something better than the installation default.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 942 views
  • 0 likes
  • 4 in conversation