公司BARCODE_MASTER 这个表的数据上1.5亿多条,表的达到了16个G,平时用来查询公司产品的条码了,由于最近查询有点慢,领导考虑把这个表来分区:
script.:
1. 创建分区表:
CREATE TABLE HMBARCODE.BARCODE_MASTER
(
JEPUM_CODE CHAR(6 BYTE) NOT NULL,
JEJO_NO CHAR(8 BYTE) NOT NULL,
CASE_BARCODE VARCHAR2(30 BYTE) NOT NULL,
MIDDLE_BARCODE VARCHAR2(30 BYTE) NOT NULL,
LARGE_BARCODE VARCHAR2(30 BYTE) NOT NULL,
IPGO_CHECK CHAR(1 BYTE),
IPGO_DATE DATE,
IPGO_USER VARCHAR2(255 BYTE),
CHULGO_CHECK CHAR(1 BYTE),
CHULGO_DATE DATE,
CHULGO_USER VARCHAR2(255 BYTE),
BANPUM_CHECK CHAR(1 BYTE),
BANPUM_DATE DATE,
BANPUM_USER VARCHAR2(255 BYTE),
SALE_CHECK CHAR(1 BYTE),
SALE_DATE DATE,
SALE_USER VARCHAR2(255 BYTE),
ITEM_CODE CHAR(4 BYTE),
POJANG_REC NUMBER(2),
STOREHOUSE_CODE VARCHAR2(3 BYTE)
PARTITION BY RANGE (JEJO_NO)
(PARTITION JEJO_NO2010 VALUES LESS THAN '20110101' tablespace BARCODE2010,
(PARTITION JEJO_NO2012 VALUES LESS THAN '20130101' tablespace BARCODE2012,
(PARTITION JEJO_NO2014 VALUES LESS THAN '20150101' tablespace BARCODE2014,
(PARTITION JEJO_NOMAX VALUES LESS THAN (maxvalue) tablespace BARCODE2016
);
3. 使tablespace nologging(这样产生很少的日志)
SQL> alter tablespace barcode2010 nologging;
SQL> alter tablespace barcode2012 nologging;
4.导入数据
date && impdp hmbarcode/password tables=barcode_master exclude=constraint,index,grant content=data_only directory=dump_dir dumpfile=hmbarcode.2012-02-08_%U.dmpd parallel=2 job_name=barcode_master_job logfile=hmbarcod_impdp.log && date
5. 创建index
1.
CREATE INDEX IDX_BARCODE_MASTER_LOCAL ON BARCODE_MASTER
(MIDDLE_BARCODE, JEJO_NO)
local nologging parallel 4;
2.
CREATE INDEX IDX_BAR_JEJONO_LOCAL ON BARCODE_MASTER
(JEJO_NO) local nologging parallel 4;
3 .
CREATE INDEX IDX_BARCODE_MASTER1_LOCAL ON HMBARCODE.BARCODE_MASTER
(LARGE_BARCODE, JEJO_NO)
local nologging parallel 4;
4.
CREATE UNIQUE INDEX FOR_PK_BARCODE_LOCAL ON BARCODE_MASTER
( CASE_BARCODE, JEJO_NO, MIDDLE_BARCODE, LARGE_BARCODE, JEPUM_CODE)
local nologging parallel 4;
ALTER TABLE BARCODE_MASTER ADD CONSTRAINT PK_BARCODE_MASTER_LOCAL
PRIMARY KEY (CASE_BARCODE, JEJO_NO, MIDDLE_BARCODE, LARGE_BARCODE, JEPUM_CODE);
6 使tablespace logging
SQL> alter tablespace barcode2010 logging;
SQL> alter tablespace barcode2012 logging;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7583803/viewspace-716036/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7583803/viewspace-716036/