Oracle fast data load method-SQL * Loader

Oracle can quickly load data, which is much faster than the previous batch Insertion Method (1 kW records took nearly 2 minutes ).

Prepare data files

Generate 1 million records in the memory vector and write them to the specified data file.

# Include <string. h>
# Include <stdio. h>

# Include <unistd. h>
# Include <vector>
Using namespace std;

# Include "clocktool. h"

Typedef struct User_Record
{
Char id [16];
Char name [32];
Char passwd [16];
} USER_RECORD;

Int main (int argc, char ** argv)
{
Int iTimes = 10000000;

Vector <User_Record *> vec;
TimeTool cktool;

User_Record * pRecord = NULL;
Cktool. begin ();
For (int I = 0; I <iTimes; ++ I)
{
PRecord = new User_Record;
Memset (pRecord, 0, sizeof (struct User_Record ));
Sprintf (pRecord-> id, "% d", I );
Sprintf (pRecord-> name, "% ld", 18600000000 + I );
Strcpy (pRecord-> passwd, "111111 ");
Vec. push_back (pRecord );
}
Cktool. end ();
Printf ("cost % f \ n", cktool. getInterval ());

Cktool. reset ();

Cktool. begin ();
FILE * pf = fopen ("./load. data", "w ");
If (pf)
{
Char buff [256];
Int iResult = 0;
For (int I = 0; I <iTimes; ++ I)
{
Memset (buff, 0, sizeof (buff ));
IResult = sprintf (buff, "% s, % s, % s \ n", vec [I]-> id, vec [I]-> name, vec [I]-> passwd );
Fwrite (buff, 1, iResult, pf );
}
Fclose (pf );
}
Cktool. end ();
Printf ("cost % f \ n", cktool. getInterval ());

Vector <User_Record *>: iterator it = vec. begin ();
For (; it! = Vec. end (); ++ it)
{
Delete (* it );
}
Return 0;
}

Execution result:

Cost 2.796853
Cost 3.715381

Load data through SQL * Loader

Compile the load. ctl File

Load data
Infile 'Load. data'
Into table base_users
Fields terminated ","
(Userid, username, passwd)

Clear original data

SQL> truncate table base_users;

When loading large data volumes and pursuing efficiency, we recommend that you disable log

SQL> alter table base_users nologging;

Run the load command

Sqlldr userid = cat/cat control = load. ctl log = load. log direct = y

Tests show that direct = y is very important, greatly improving the efficiency of data loading. Check the load. log and you can find that the execution time is about 12 seconds.

Total logical records skipped: 0
Total logical records read: 10000000
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL * Loader main thread: 2204
Total stream buffers loaded by SQL * Loader load thread: 0

Run began on Sat Mar 31 22:47:07 2012
Run ended on Sat Mar 31 22:47:19 2012

Elapsed time was: 00:00:11. 73
CPU time was: 00:00:05. 31

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12