Case study of accelerating data loading by Oracle swap Partition

How can I increase the data loading speed of oracle swap partitions? CASE Environment: OS: linuxdb: oracle10g where the data loading speed of a database is very slow? Lower

How can I increase the data loading speed of oracle swap partitions? CASE Environment: OS: linuxdb: oracle10g where the data loading speed of a database is very slow? Lower

Case study of accelerating data loading by Oracle swap Partition

Environment:

OS: linux

Db: oracle10g

Data Loading in one database is very slow. How can we increase the data loading speed? The following is a small example.

First, calculate the SQL statements involved in loading data tables. This is the basis for the following.

The SQL statements for table tabname1 are as follows:

Select count (*)
FROM (select rpl. ITEMCODE, RPL. CATALOGID
FROM tabname RPL
Where rpl. L3COLUMN =: 1
And RPL. SUPPLIERID =: 2
And RPL. STATDATE = TO_DATE (sysdate-2, 'yyyy-MM-DD ')
Group by (RPL. CATALOGID, RPL. ITEMCODE) TEMP

Select sum (LISTNUM) AS lSUM, SUM (CLICKNUM) AS CSUM
FROM tabname RPL
Where rpl. L3COLUMN =: 1
And RPL. SUPPLIERID =: 2
And RPL. STATDATE = TO_DATE (: 3, 'yyyy-MM-DD ')

Select TMPB .*
From (select tmpa. *, ROWNUM rownum _
FROM (select temp. LSUM,
TEMP. CSUM,
TEMP. ITEMCODE,
TEMP. CATALOGID,
RPO. ORDERNUM,
RPO. ORDER_PRO_NUM,
TEMP. PRODUCTID
FROM (select sum (LISTNUM) AS lSUM,
SUM (CLICKNUM) as csum,
RPL. ITEMCODE,
RPL. CATALOGID,
RPL. PRODUCTID
FROM tabname1 RPL
Where rpl. L3COLUMN =: 1
And RPL. SUPPLIERID =: 2
And (RPL. CATALOGID like '000000' or
RPL. CATALOGID like '20140901 ')
And RPL. STATDATE = TO_DATE (: 3, 'yyyy-MM-DD ')
Group by (RPL. CATALOGID, RPL. ITEMCODE, RPL. PRODUCTID) TEMP
Left join tabname3 RPO
On temp. ITEMCODE = RPO. ITEMCODE
And RPO. STATDATE = TO_DATE (: 4, 'yyyy-MM-DD ')
Order by lsum desc, TEMP. ITEMCODE) TMPA
Where rownum <=: 5) TMPB
Where tmpb. rownum _>: 6

Select tcc. DESCRIPTION
FROM tabname1 RPL, tabname2 TCC
Where rpl. COUNTRY = TCC. COUNTRYID
And rpl. L3COLUMN =: 1
And RPL. SUPPLIERID =: 2
And RPL. ITEMCODE =: 3
And RPL. STATDATE = TO_DATE (: 4, 'yyyy-MM-DD ')
And ROWNUM <=: 5
Order by rpl. LISTNUM DESC

From the preceding SQL statements, we can see that statistics are collected on a specific day. These SQL statements mainly affect the I/O of the db disk. Therefore, we recommend that you adjust the partition format of tabname1 and use rang-list to combine partitions; only create partition indexes, not global indexes. Create rang partitions in the STATDATE column and list partitions in the L3COLUMN Column

Current load data logic:
1. The daily truancate table tmp_tabname1,
2. Then the gp cluster loads all the data of the day to tmp_tabname1,
3. Then, insert tmp_tabname1 directly to table tabname1. The speed is mainly slow in the insert Process. (You can load data to tmp_tabname1 by L3COLUMN)

Adjusted load data logic:
1. The daily truancate table tmp_tabname1,
2. then, the gp cluster loads each list (L3COLUMN) data of the day to tmp_tabname1, that is, the gp cluster changes the original load to 1000 times, or the tmp_tabname1 partition is the same as tabname1 ,, in this way, the gp cluster is also a load
3. Use oracle's table exchange technology (eg: alter table t_temp exchange subpartition p9sublist1 with table t_temp1 update indexes) to speed up data load to tabname1

I tested table exchange technology and insert load data comparison

Data volume: 300 MB, 17 million recorded test data

Table Exchange (with global indexes) is about twice faster than insert.
Table Exchange (without a global index) is about 4-6 times faster than insert.

The advantage of doing so is that the io bottleneck of the db is very serious, and the utile % of io is rarely less than 95%.
1. Improve the query speed. SQL finds the rang primary partition based on STATDATE, and then finds the list partition based on L3COLUMN, which reduces the data volume scanned.
2. Accelerated load
3. Easy to maintain

Disadvantages:
1. The load speed from the gp cluster to tmp_tabname1 is affected.

You can select an appropriate implementation method based on your business needs. The best choice is the best !!!

------ End ------