Home » RDBMS Server » Server Utilities » SQLLDR Direct=true
SQLLDR Direct=true [message #185215] Mon, 31 July 2006 10:13 Go to next message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Hi
I have some 6-7 Million records to be uploaded into prod database. For faster uploading i will use DIRECT=Y, now my qry is after how many records a commit will happen? IS this going to put too much load on my Prod DB? what are the conerns relating to redo generation?
Please suggest.
I guess i can also use EXTERNAL TABLES. Any experience with external tables using such huge loads?

Thanks
Manish
Re: SQLLDR Direct=true [message #185220 is a reply to message #185215] Mon, 31 July 2006 10:40 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Using Direct Path, there is no interval for commit. It's all or nothing. Data goes directly into the datafiles, whereas a regular SQL*Load is internally executing INSERT statements.
Essentially, you can think of it as a commit at the end.
Re: SQLLDR Direct=true [message #185247 is a reply to message #185215] Mon, 31 July 2006 13:45 Go to previous messageGo to next message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Well I knew about DIRECT=TRUE it was just my apprehension for using it for big uploads on prod. I just finished uploading 9.7 M recs in flat 28 Mins(no indexes/constraints on table)

Re: SQLLDR Direct=true [message #185278 is a reply to message #185220] Mon, 31 July 2006 22:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
joy_division wrote on Tue, 01 August 2006 01:40

Using Direct Path, there is no interval for commit. It's all or nothing. Data goes directly into the datafiles, whereas a regular SQL*Load is internally executing INSERT statements.
Essentially, you can think of it as a commit at the end.


I disagree, and more importantly, so does Oracle:
Oracle Utilities Manual

ROWS (rows per commit)
Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.

Keep in mind that if you specify a low value for ROWS and then attempt to compress data using table compression, your compression ratio will probably be degraded. Oracle recommends that you either specify a high value or accept the default value when compressing data.

Conventional path loads only: ROWS specifies the number of rows in the bind array. See Bind Arrays and Conventional Path Loads.

Direct path loads only: ROWS identifies the number of rows you want to read from the datafile before a data save. The default is to read all rows and save data once at the end of the load. See Using Data Saves to Protect Against Data Loss. The actual number of rows loaded into a table on a save is approximately the value of ROWS minus the number of discarded and rejected records since the last save.


There is generally no real harm in not specifying the ROWS param for DIRECT path, although you may blow your rollback segments if you use the SINGLEROW or SORTED INDEXES options.

Ross Leishman
Re: SQLLDR Direct=true [message #185396 is a reply to message #185278] Tue, 01 August 2006 09:22 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks Ross. Another one of those little pieces of info that I though I knew to be wrong.
Re: SQLLDR Direct=true [message #185400 is a reply to message #185215] Tue, 01 August 2006 09:42 Go to previous message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Great piece of Info Ross.
Thanks
Previous Topic: SQL Loader
Next Topic: DB Refresh- IMPORT
Goto Forum:
  


Current Time: Wed Dec 07 08:46:34 CST 2016

Total time taken to generate the page: 0.11053 seconds