Oracle Database quickly generates a large amount of related data practices

The following article describes how to quickly generate a large amount of relevant data in Oracle databases. The following describes how to generate a large amount of relevant test data. The following describes the relevant content.

1. Use plsql developer to generate a csv file

2. Use> output redirection to append it to a cvs file.

3. Use SQL loader to quickly load data.

Run in plsql developer

SQL code

SELECT object_id, object_name FROM dba_objects;

Right-click plsql developer to export the csv format 1.csv. Execute the following script on linux

C code

#! /Bin/bash

For (I = 1; I <200; I = I + 1 ))

Do

Cat 1.csv> 2.csv;

Echo $ I;

Done

In this way, 50000*200 is less than 10 million of the data. I tested 11047500 392 M

Available:

Linux code

Wc-l 2.csv

Check the number of data entries in the csv file. Now the test data is available. Let's try the loading effect of SQL loader.

Create the sqlloader control file and save it as 1.ctl.

Sqlldr ctl code

Load data

Infile '2.csv'

Into table my_objects

Fields terminated by ', 'optionally enclosed '"'

(Object_id,

Object_name

);

Brief description of the control file:

-- INFILE 'n.csv ': Import multiple files

-- INFILE * the content to be imported is after BEGINDATA in the control file.

-- BADFILE '1. bad' specifies the address of the bad file

-- Apend into table my_objects append

-- INSERT: loads empty tables. If the original table has data, sqlloader will stop the default value.

-- REPLACE the original table with data. The original data will be deleted.

-- If the content specified by TRUNCATE is the same as that specified by replace, the truncate statement is used to delete existing data.

-- You can specify a location for loading.

-- (Object_id position () char, object_name position () char)

-- Specify separators respectively

-- (Object_id char terminated by ",", object_name char terminated ",")

-- Execute sqlldr userid = scott/a123 control = 1.ctl log = 1.out direct = true

-- 2 million of test data can be loaded in 30 seconds, 79 MB

-- Sqlldr userid =/control = result1.ctl direct = true parallel = true

-- Sqlldr userid =/control = result2.ctl direct = true parallel = true

-- Sqlldr userid =/control = result2.ctl direct = true parallel = true

-- When loading a large amount of data, it is best to suppress the log generation by about 10 GB:

-- Sqlalter table resultxt nologging;

-- This will not generate REDO logs, which can improve the efficiency. Add an unrecoverable line to load data in the CONTROL file.

-- This option must be applied together with DIRECT.

-- In concurrent operations, Oracle databases claim to be able to process GB of data per hour! In fact, it is estimated that it will be good to reach 1-10 Gb, and the available structure will start.

-- The same file, but only a small amount of data, starts to load a large amount of data after success, this can avoid the waste of time

The following is the execution

Shell code

Sqlldr userid = scott/a123 control = 1.ctl log = 1.out direct = true

Result: 2 million of the test data can be loaded in 30 seconds, 79 MB.

226 seconds to load 11 million of test data 392 Mb

My environment is on a virtual machine and the measured results

MemTotal: 949948 kB

Model name: Intel (R) Pentium (R) d cpu 2.80 GHz

Stepping: 8

Cpu MHz: 2799.560

Cache size: 1024 KB

It's still fast :)

The above content is an introduction to the Oracle database, SQL loader, and Quick Generation of a large amount of data.