|
表分區(qū)是一種思想，分區(qū)表示一種技術(shù)實(shí)現(xiàn)。當(dāng)表的大小過(guò)G的時(shí)候可以考慮進(jìn)行表分區(qū)，提高查詢效率，均衡IO。oracle分區(qū)表是oracle數(shù)據(jù)庫(kù)提供的一種表分區(qū)的實(shí)現(xiàn)形式。表進(jìn)行分區(qū)后，邏輯上仍然是一張表，原來(lái)的查詢SQL同樣生效，同時(shí)可以采用使用分區(qū)查詢來(lái)優(yōu)化SQL查詢效率，不至于每次都掃描整個(gè)表。 根據(jù)年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR')) 此時(shí)已經(jīng)有普通表了，我創(chuàng)建了含相同字段的分區(qū)表，把數(shù)據(jù)導(dǎo)入到分區(qū)表中，再把原表刪掉。  -- Create table（WMS_OPERATION_RECORD） create table DPHOMEWMS.WMS_OPERATION_RECORD_TMP ( ID NUMBER(19) not null, WAREHOUSE_ID NUMBER(19), ASN_ID NUMBER(19), PICK_TICKET_ID NUMBER(19), RELATION_CODE VARCHAR2(50), OPERATION_TYPE VARCHAR2(50), OPERATION_ID NUMBER(19), OPERATION_NAME VARCHAR2(50 CHAR), OPERATION_TIME TIMESTAMP(6) DEFAULT systimestamp not null, STATUS VARCHAR2(10), TYPE VARCHAR2(10), COUNTS NUMBER(10) )tablespace DPHOMEWMS_DATA PARTITION BY RANGE (OPERATION_TIME) interval (numtoyminterval(1, 'month')) STORE IN (DPHOMEWMS_DATA) ( partition OPERATION_RECORD_P01 values less than (TIMESTAMP' 2017-05-08 00:00:00') tablespace DPHOMEWMS_DATA ); -- Add comments to the columns comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.WAREHOUSE_ID is '倉(cāng)庫(kù)id'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.ASN_ID is '收貨單id'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.PICK_TICKET_ID is '發(fā)貨單ID'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.RELATION_CODE is '關(guān)聯(lián)單據(jù)號(hào)(收貨單或發(fā)貨單的code)'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_TYPE is '操作類型'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_ID is '操作人ID'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_NAME is '操作人名稱'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.OPERATION_TIME is '操作時(shí)間'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.STATUS is '狀態(tài)'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.TYPE is '明細(xì)或者統(tǒng)計(jì)次數(shù)類型'; comment on column DPHOMEWMS.WMS_OPERATION_RECORD_TMP.COUNTS is '統(tǒng)計(jì)次數(shù)'; --創(chuàng)建本地索引 create index IDX_WMS_OPERATION_RECORD on DPHOMEWMS.WMS_OPERATION_RECORD_TMP(RELATION_CODE) tablespace DPHOMEWMS_INDEX local; update DPHOMEWMS.WMS_OPERATION_RECORD set OPERATION_TIME = sysdate where OPERATION_TIME is null; insert into DPHOMEWMS.WMS_OPERATION_RECORD_TMP select * from WMS_OPERATION_RECORD; drop table DPHOMEWMS.WMS_OPERATION_RECORD; alter table DPHOMEWMS.WMS_OPERATION_RECORD_TMP rename to WMS_OPERATION_RECORD; alter table DPHOMEWMS.WMS_OPERATION_RECORD enable row movement; （1）OPERATION_TIME是分區(qū)鍵，每一個(gè)月會(huì)自動(dòng)創(chuàng)建一個(gè)分區(qū)，分區(qū)鍵不允許為null。 （2）alter table table_name enable row movement; 是指允許分區(qū)表的分區(qū)鍵是可更新，當(dāng)某一行更新時(shí)，如果更新的是分區(qū)列，并且更新后的列值不屬于原來(lái)的這個(gè)分區(qū)，如果開(kāi)啟了這個(gè)選項(xiàng)，就會(huì)把這行從這個(gè)分區(qū)中delete掉，并加到更新后所屬的分區(qū)，此時(shí)就會(huì)發(fā)生rowid的改變。相當(dāng)于一個(gè)隱式的delete+insert，但是不會(huì)觸發(fā)insert/delete觸發(fā)器。如果沒(méi)有開(kāi)啟這個(gè)選項(xiàng)，就會(huì)在更新時(shí)報(bào)錯(cuò)。 |
|
|
來(lái)自： aaie_ > 《性能優(yōu)化》
