| SQLLDR Direct=true [message #185215] |
Mon, 31 July 2006 10:13  |
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  |
joy_division
Messages: 4963
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 #185278 is a reply to message #185220] |
Mon, 31 July 2006 22:02 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
