创建测试表select * from beacn_cust_nds_port_hold;select count(*) from beacn_cust_nds_port_hold;--8042234create table beacn_cust_nds_port_hold_test as select * from beacn_cust_nds_port_hold;--62.969sselect * from beacn_cust_nds_port_hold_test;select count(*) from beacn_cust_nds_port_hold_test;--8042234
备份数据create table cust_nds_port_hold_test_bak as select * from beacn_cust_nds_port_hold_test;--46.859select * from cust_nds_port_hold_test_bak;select count(*) from cust_nds_port_hold_test_bak;--8042234
删除业务表drop table beacn_cust_nds_port_hold_test purge;select * from beacn_cust_nds_port_hold_test;
交换分区-- 创建分区表：列表分区-- Create tablecreate table beacn_cust_nds_port_hold_test(CUST_ID NUMBER(9) not null,AS_OF_DATE DATE not null,LOCALE VARCHAR2(5) not null,ACCT_NUM VARCHAR2(30) not null,CERT_NUM VARCHAR2(8) not null,INSTR_ID VARCHAR2(20) not null,INSTR_NAME VARCHAR2(100),INSTR_CCY VARCHAR2(3),INV_ASSET VARCHAR2(10),DISPLAY_GROUP VARCHAR2(10),DISPLAY_SUB_GROUP VARCHAR2(10),AMT NUMBER(28,6),AVAIL_AMT NUMBER(28,6),HOLD_AMT NUMBER(28,6),PCT NUMBER(5,2),REF_CCY VARCHAR2(3),AMT_IN_REF_CCY NUMBER(28,6),HIDE_AMT NUMBER(28,6),HIDE_AMT_IN_REF_CCY NUMBER(28,6))partition by list(instr_ccy)(partition cust_nds_port_hold_test_p1 values('EUR') tablespace SP_TEST1,partition cust_nds_port_hold_test_p2 values('NZD') tablespace SP_TEST2,partition cust_nds_port_hold_test_p4 values('CNY') tablespace SP_TEST4,partition cust_nds_port_hold_test_p5 values('GBP') tablespace SP_TEST5,partition cust_nds_port_hold_test_p6 values('JPY') tablespace SP_TEST6,partition cust_nds_port_hold_test_p7 values('SGD') tablespace SP_TEST7,partition cust_nds_port_hold_test_p8 values('HKD') tablespace SP_TEST8,partition cust_nds_port_hold_test_p9 values('CHF') tablespace SP_TEST9,partition cust_nds_port_hold_test_p10 values('CAD') tablespace SP_TEST10,partition cust_nds_port_hold_test_p11 values('AUD') tablespace SP_TEST11,partition cust_nds_port_hold_test_p12 values('USD') tablespace SP_TEST12,partition cust_nds_port_hold_test_p3 values(default) tablespace SP_TEST3--SEK DEM FRF NOK );select * from beacn_cust_nds_port_hold_test;--0-- 创建分别对应分区的基表：CREATE TABLE dba_p1 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='EUR';CREATE TABLE dba_p2 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='NZD';CREATE TABLE dba_p3 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy not in('EUR','NZD','CNY','GBP','JPY','SGD','HKD','CHF','CAD','AUD''USD');CREATE TABLE dba_p4 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='CNY';CREATE TABLE dba_p5 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='GBP';CREATE TABLE dba_p6 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='JPY';CREATE TABLE dba_p7 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='SGD';CREATE TABLE dba_p8 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='HKD';CREATE TABLE dba_p9 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='CHF';CREATE TABLE dba_p10 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='CAD';CREATE TABLE dba_p11 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='AUD';CREATE TABLE dba_p12 as SELECT * FROM cust_nds_port_hold_test_bak WHERE instr_ccy='USD';select count(*) from dba_p1;select count(*) from dba_p2; --将基表与分区进行交换：alter table beacn_cust_nds_port_hold_test exchange partition cust_nds_port_hold_test_p1 with table dba_p1;alter table beacn_cust_nds_port_hold_test exchange partition cust_nds_port_hold_test_p2 with table dba_p2; --查询2个分区：select count(*) from beacn_cust_nds_port_hold_test partition(cust_nds_port_hold_test_p1);--707422select count(*) from beacn_cust_nds_port_hold_test partition(cust_nds_port_hold_test_p2);--549408select * from beacn_cust_nds_port_hold_test;--0注意：数据和之前的基表一致。 查询原来的2个基表：select count(*) from dba_p2;select count(*) from dba_p1;注意： 2个基表的数据变成成0。-- Create/Recreate primary, unique and foreign key constraints alter table beacn_cust_nds_port_hold_testadd constraint cust_nds_port_hold_test_PK primary key (CUST_ID, AS_OF_DATE, LOCALE, ACCT_NUM, CERT_NUM, INSTR_ID);-- Create/Recreate indexes create index UNIQUE_IDX_NDS_PORT_TEST on beacn_cust_nds_port_hold_test (CUST_ID);
测试，系统使用新的业务表，系统的运行性能是否提高
新的业务表（分区表）创建成功且测试通过，删除备份表drop table cust_nds_port_hold_test_bak purge; --查看表空间使用情况select dbf.tablespace_name, dbf.totalspace '总量(M)', dbf.totalblocks as 总块数, dfs.freespace '剩余总量(M)', dfs.freeblocks '剩余块数', (dfs.freespace / dbf.totalspace) * 100 '空闲比例' from (select t.tablespace_name, sum(t.bytes) / 1024 / 1024 totalspace, sum(t.blocks) totalblocks from dba_data_files t group by t.tablespace_name) dbf, (select tt.tablespace_name, sum(tt.bytes) / 1024 / 1024 freespace, sum(tt.blocks) freeblocks from dba_free_space tt group by tt.tablespace_name) dfs where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)order by dbf.tablespace_name;