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 www.2cto.com where the data loading speed of a database is very slow? 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) tempselect 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 ') www.2cto.com select TM PB. * 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 '000000') 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 www.2cto.com 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. through the preceding SQL statements, listnum desc collects statistics on data 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, use rang-list to combine partitions. Only partition indexes are created, and no global indexes are created. 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, you can directly insert tmp_tabname1 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 partitions of tmp_tabname1 are the same as those of tabname1, in this way, the gp Cluster also uses the oracle 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 the table exchange technology and compared the insert load data. The data volume is 300 MB, and 17 million of the recorded test data is exchanged with tables (with global indexes ), the speed is about 2 times faster than insert, and table exchange is used (without a global index). The speed is about 4-6 times faster than insert. The advantage of cto.com is that the I/O bottleneck of db is very serious, and the utile % of I/O is rarely lower than 1 of 95%. to speed up the query, the SQL statement finds the rang primary partition based on STATDATE, and then finds the list partition based on L3COLUMN. This reduces the amount of data scanned. speed up load 3. disadvantages of easy maintenance: 1. the load speed from the gp cluster to tmp_tabname1 has an impact. You can select an appropriate implementation method based on your business needs. It is the best choice for you !!! ------ End ------ author skate