Oracle: Import CSV file

Hôm nay mình cần import dữ liệu 1 file csv với gần 5M row, lúc đầu mình thử import với tool Toad và Sql Developer, các tool hiển thị tps 123 và thời gian để import hết file của mình là 10h. Mình đã thử dùng tính năng SQLLoader utility thì kết quả ngoài dự tính.

  1. Tạo control file bl_20191118.ctl LOAD DATA infile '/home/oracle/bl18112019.csv' REPLACE INTO TABLE bl_20191118 fields terminated by ',' optionally enclosed by '"' ( msisdn)

  2. run SQL*Loader from the command line sqlldr xxx/xxx control=/home/oracle/bl_20191118.ctl log=/home/oracle/bl_20191118.log

Kết quả: mình mất chưa đến 1 phút để load 4938456 row vào table bl_20191118 [[email protected] ~]$ cat bl_20191118.log

SQL*Loader: Release - 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


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 began 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

All Rights Reserved

Let's register a Viblo Account to get more interesting posts.