Gp uses DBLINK-like access to oracle for Performance Testing

0. Oracle test data preparation:
[Oracle @ db1 ~] $ Sqlplus system/000000

SQL * Plus: Release 11.2.0.3.0 Production on Tue Mar 25 10:26:06 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table test;

Table dropped.

SQL> create table test (id int, name varchar2 (20), age int, msg varchar2 (20 ));

Table created.

SQL> insert into test values (1, 'aaaaa', 1, 'aaaaa ');

1 row created.

SQL> insert into test values (2, 'bbbbbb', 2, 'bbbbbb ');

1 row created.

SQL> insert into test values (3, 'ccccc ', 3, 'ccccc ');

1 row created.

SQL> insert into test values (4, 'ddddd ', 4, 'ddddd ');

1 row created.

SQL> insert into test values (5, 'eeeee ', 5, 'eeeee ');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select count (*) from test;

COUNT (*)
----------
5

SQL> INSERT INTO TEST SELECT * FROM TEST;

5 rows created.

SQL> commit;

Commit complete.

SQL> select count (*) from test;

COUNT (*)
----------
100000

SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name = 'test ';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
TEST
3
Insert 10 million data.

1. Load the test using an external table of the landing File
First, create data export on the oracle server and enable the gpfdist service process:
[Oracle @ db1 ~] $ Gtlions. ora2text. bin user = system/000000 query = 'select * from test' text = csv file = test. SQL fast = true
0 rows exported at 10:30:19, size 0 MB.
100000 rows exported at 10:30:20, size 2 MB.
Output file test. SQL closed at 100000 rows, size 2 MB.
[Oracle @ db1 ~] $ Nohup gpfdist-d.-p 9999 &
[1] 15147
[Oracle @ db1 ~] $ Nohup: Ignore the input and append the output to "nohup. out"

[1] + Exit 1 nohup gpfdist-d.-p 9999
[Oracle @ db1 ~] $ Ps-ef | grep gpfdist
Oracle 15149 15068 0 00:00:00 pts/8 grep gpfdist
Oracle 62994 62778 0 Mar24? 00:00:04 gpfdist-d.-p 9999
The export phase takes 1 S;

Next, create an external table in gp and load the data into the database:
[Gpadmin @ bdb ~] $ Psql postgres
Timing is on.
Psql (8.2.15)
Type "help" for help.

Postgres = # \ timing on
Timing is on.
S = # drop table if exists gt_test;
Create table gt_test (id int, name character varying (20), age int, msg character varying (20) distributed randomly;
DROP TABLE
Time: 17.802 MS
Postgres = # create table gt_test (id int, name character varying (20), age int, msg character varying (20) distributed randomly;
Drop external table if exists gt_test_ext;
CREATE TABLE
Time: 17.386 MS
Postgres = # drop external table if exists gt_test_ext;
Create external table gt_test_ext (like gt_test) location ('gpfdist: // 192.168.1.2: 9999/test. SQL ') format 'csv' (header );
DROP EXTERNAL TABLE
Time: 6.734 MS
S = # create external table gt_test_ext (like gt_test) location ('gpfdist: // 192.168.1.2: 9999/test. SQL ') format 'csv' (header );
NOTICE: HEADER means that each one of the data files has a header row.
CREATE EXTERNAL TABLE
Time: 13.562 MS
S = # insert into gt_test select * from gt_test_ext;
Select count (*) from gt_test;
Inserts 0 100000
Time: 469.955 MS
Postgres = # select count (*) from gt_test;
Count
--------
100000
(1 row)

Time: 9.455 MS

The insert stage takes 0.174 S;
Total time consumed: 1 S + 0.469 S = 1.469 S;

2. Do not Load External tables
Create an External table in gp and load the data into the database:
Postgres = # \ timing on
Timing is on.
S = # drop table if exists gt_test;
Create table gt_test (id int, name character varying (20), age int, msg character varying (20) distributed randomly;
DROP TABLE
Time: 17.969 MS
Postgres = # create table gt_test (id int, name character varying (20), age int, msg character varying (20) distributed randomly;
Drop external table if exists gt_test_webext;
CREATE TABLE
Time: 16.960 MS
Postgres = # drop external table if exists gt_test_webext;
DROP EXTERNAL TABLE
Time: 6.944 MS
Postgres = # create external web table gt_test_webext (like gt_test) execute 'sh/home/gtlions/oracle. Sh' on master format 'text' (delimiter ',');
CREATE EXTERNAL TABLE
Time: 9.508 MS
S = # insert into gt_test select * from gt_test_webext;
Select count (*) from gt_test;
Inserts 0 100000
Time: 4253.003 MS
Postgres = # select count (*) from gt_test;
Count
--------
100000
(1 row)

Time: 9.867 MS
The insert stage takes 4.523 S;

3. Increase data volume for testing
50 W 100 W 200 W 400 W 800 W 1600 W 2000 W
After a step-by-step test, within 50 million records, there is little difference between the two, in 2 ~ About 3 times;
However, when the number of records increases, the gap between the two will gradually increase:
Records differ by 7 times;
12.9 million records differ by times;
12.9 million record gaps times;
The difference between 13.5 million records is times;
15.2 million records differ by times;
......
However, in the process from 0.1 billion to, the time gap does not grow linearly.

3. Summary
For small tables, it is acceptable to use the method of not landing. For tables larger than 25 MB, the speed difference is too large.
-EOF-