My Question: What might I expect from loading 750,000 rows with SQL*Loader. Via ODBC (INSERT INTO) it is 2.6 hours.


Detail -------------------

I am loading Data into an oracle table from Spreadsheet, import to MS Access, prepare the table with an update or two, and then INSERT INTO a oracle tables via ODBC.
We have about 10 spreadsheets to load. Once the data is in Oracle tables, we can do some very fast "processing" via MS Access Pass through queries.
They work great. It is very fast.

The issue is the speed when loading "prepared" MS Access tables to Oracle.

For our larger tables 300,000 row to 750,000 rows, we get about 80 rows per second, MS ACCESS to ODBC linked oracle table.
My thought is to Convert the MS ACCESS back to .csv or .txt (without headers) and use the SQL*Loader.
I understand the CONTROL file and can build that accordingly.

Rather than getting too far ahead, there an general indication that SQL Loader would process the (spreadsheet.csv) considerably faster.
I understand also that we can RUN Sql*Loader with the CONTROL FILE like a "batch-file" from MS ACCESS (vba)...so that should be no problem to construct the Control file and execute the batch as part of the current "load" procedure.

The question is speed. I have not used SQL * Loader so I cannot sing its praises, yet. But I sense this might be a good step forward

My Question (once again) : What might I expect from loading 750,000 rows with SQL*Loader. Via ODBC it is 2.6 hours.

Thanks.

Joseph Giallombardo

SQL> create table emp  as
  2  select * from scott.emp
  3  where 1=0;

Table created.

SQL> desc emp
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 EMPNO                         NOT NULL NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)

C:\temp>cat scott.ctl
LOAD DATA
INFILE "c:\temp\scott.dat"
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)

C:\temp>sqlldr userid=mcdonac control=scott.ctl direct=true 
Password: ********

SQL*Loader: Release 12.2.0.1.0 - Production on Tue Aug 15 12:07:34 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 11413668.

Table EMP:
  11413668 Rows successfully loaded.

Check the log file:
  scott.log
for more information about the load.

C:\temp>grep Elap scott.log
Elapsed time was:     00:00:14.67