Today I need to import a csv file with nearly 5M row, at first I tried to import with Toad and Sql Developer tool, the tools show tps 123 and the time to import all my files is 10h. I tried using the SQLLoader utility feature, the results were unexpected.
- Create control file bl_20191118.ctl LOAD DATA infile ‘/home/oracle/bl18112019.csv’ REPLACE INTO TABLE bl_20191118 fields terminated by ‘,’ optionally enclosed by ‘”(msisdn)
- run SQL * Loader from the command line sqlldr xxx / xxx control = / home / oracle / bl_20191118.ctl log = / home / oracle / bl_20191118.log
Result: it took me less than 1 minute to load 4938456 row to table bl_20191118 [ [email protected] ~] $ cat bl_20191118.log
SQL * Loader: Release 18.104.22.168.0 – Production on Mon Nov 25 23:19:50 2019
Copyright (c) 1982, 2011, Oracle and / or its affiliates. All rights reserved.
Control File: /home/oracle/bl_20191118.ctl Data File: /home/oracle/bl18112019.csv Bad File: /home/oracle/bl18112019.bad Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional
Table BL_20191118, loaded from every logical record. Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
MSISDN FIRST *, O (“) CHARACTER
Table BL_20191118: 4938456 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.
Space allocated for bind array: 16512 bytes (64 rows) Read buffer bytes: 1048576
Total logical records skipped: 0 Total logical records read: 4938456 Total logical records rejected: 0 Total logical records discarded: 0
Run ended on Mon Nov 25 23:19:50 2019 Run ended on Mon Nov 25 23:20:44 2019
Elapsed time was: 00: 00: 53.77 CPU time was: 00: 00: 05.36