Mục đích: Khi bảng non-partiton lớn (> 2GB hoặc > 50 triệu row) thì chúng ta cân nhắc chuyển bảng sang partition, nếu quét theo ngày thì tạo partition theo ngày theo thủ tục sau:
select min(month) from user1.table1_NOPART;
--select min(load_date) from user1.table1 ;
--1.Rename table
--2. Tao bang partition tu 02112013 -> 31122014: lay script xong 1 bang partition va tao bang co 1 partition
CREATE TABLE user1.table1(
SUB_ID NUMBER,
BILL_ITEM_ID NUMBER
)
TABLESPACE DATA
PARTITION BY RANGE (PDATE)
(
PARTITION DATA20130101 VALUES LESS THAN (TO_DATE(' 2013-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA201301
)
--2.1.Add them partition
DECLARE
v_nam NUMBER (4) := 2013; --2014
v_tablename VARCHAR2 (50) := 'IVMS_user1';
v_date_from date := to_date('02/11/2013','dd/mm/yyyy');
v_date_to date := to_date('31/12/2015','dd/mm/yyyy');
v_numday number(5);
v_tablespace varchar2(50):='DATA';
BEGIN
v_numday:=v_date_to-v_date_from;
FOR i IN 0 .. v_numday
LOOP
DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||to_date(v_date_from+i,'YYYY')||';');
END LOOP;
END;
--3. Insert du lieu
insert /*+append nologging parallel(a,16)*/ into user1.table1 a select /*+ parallel(b,16) */ * from user1.table1_NOPART b;
commit;
--Monitoring qua trinh insert
select /*+ parallel(b,16) */ count(*) from user1.table1_NOPART b;
select /*+ parallel(b,16) */ count(*) from user1.table1 b;
--4.Tao Index
create index user1.IVMS_user1_IDX1 on user1.IVMS_user1 (LOAD_DATE, MSISDN, STATUS) tablespace INDX local nologging parallel 16 online;
alter index user1.IVMS_user1_IDX1 noparallel;
--4.1. Chuyen index sang partition ind2013, indx2014
select * from dba_indx_partitions;
-- Rebuild index
DECLARE
v_date_from date := to_date('02/11/2013','dd/mm/yyyy');
v_date_to date := to_date('31/12/2014','dd/mm/yyyy');
v_numday number;
v_tablespace varchar2(50):='INDX';
cursor c1 is
select a.* from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME and b.table_owner='USER1' and b.table_name='TABLE1' and a.index_name not like '%$%' and b.partition_name like '%20141231' order by a.owner,a.index_name;
BEGIN
v_numday:=v_date_to-v_date_from;
FOR i1 in c1
LOOP
FOR i IN 0 .. v_numday
LOOP
DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYY')||' nologging parallel 8 online;');
END LOOP;
END LOOP;
END;
-- Set noparallel nologging
select distinct a.index_owner, a.index_name from DBA_ind_partitions a where a.index_owner='CUS_OWNER' and a.index_name not like '%$%' and a.partition_name like '%20141231'
order by a.index_owner,a.index_name;
