PARTITION SPILT
嘗試一下建立分割槽表，然後將分割槽表SPILT，最後收集新增分割槽表的統計資訊。
建立表，表裡面有2013和2014年的分割槽以及子分割槽。
SQL> create table customer_consolidated2
2 (operator_id varchar2(10),
3 customer_id varchar2(10),
4 business_mon_system varchar2(6),
5 business_day_locale varchar2(10),
6 business_start_locale varchar2(10),
7 business_start_system varchar2(10),
8 currency_id varchar2(10))
9 partition by range (business_mon_system )
10 subpartition by range (business_mon_system )
11 ( partition y_2013 values less than(201400)
12 (subpartition p_201301 values less than ('201302'),
13 subpartition p_201302 values less than ('201303'),
14 subpartition p_201303 values less than ('201304'),
15 subpartition p_201304 values less than ('201305'),
16 subpartition p_201305 values less than ('201306'),
17 subpartition p_201306 values less than ('201307'),
18 subpartition p_201307 values less than ('201308'),
19 subpartition p_201308 values less than ('201309'),
20 subpartition p_201309 values less than ('201310'),
21 subpartition p_201310 values less than ('201311'),
22 subpartition p_201311 values less than ('201312'),
23 subpartition p_201312 values less than ('201401')
24 ),
25 partition y_2014 values less than(201500)
26 (subpartition p_201401 values less than ('201402'),
27 subpartition p_201402 values less than ('201403'),
28 subpartition p_201403 values less than ('201404'),
29 subpartition p_201404 values less than ('201405'),
30 subpartition p_201405 values less than ('201406'),
31 subpartition p_201406 values less than ('201407'),
32 subpartition p_201407 values less than ('201408'),
33 subpartition p_201408 values less than ('201409'),
34 subpartition p_201409 values less than ('201410'),
35 subpartition p_201410 values less than ('201411'),
36 subpartition p_201411 values less than ('201412'),
37 subpartition p_201412 values less than ('201501')
38 ),
39 partition y_max values less than (maxvalue)
40 (subpartition p_max values less than (maxvalue)
41 )
42 )
43 ;
表已建立。
--構建資料
SQL> select * from customer_consolidated2;
OPERATOR_I CUSTOMER_I BUSINE BUSINESS_D BUSINESS_S BUSINESS_S CURRENCY_I
---------- ---------- ------ ---------- ---------- ---------- ----------
1 1 201302 1 1 1 1
1 1 201303 1 1 1 1
1 1 201502 1 1 1 1
1 1 201503 1 1 1 1
1 1 201501 1 1 1 1
1 1 201505 1 1 1 1
1 1 201509 1 1 1 1
1 1 201508 1 1 1 1
已選擇8行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立8行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立16行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立32行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立64行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立128行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立256行。
SQL> commit;
提交完成。
--檢視目前分割槽和子分割槽情況
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
已選擇25行。
--增加2015年的分割槽
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT PARTITION Y_MAX AT (201600) INTO
2 (PARTITION Y_2015, PARTITION Y_MAX) UPDATE GLOBAL INDEXES;--此時Y_2015下多了一個系統自動生成的子分割槽SYS_SUBP21
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 SYS_SUBP21
已選擇26行。
--增加子分割槽
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201502) INTO (SUBPARTITION P_201501,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';--可以看到新增加的P_201501的子分割槽
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 P_201501
Y_2015 SYS_SUBP21
已選擇27行。
--把2015年剩餘的子分割槽都加入
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201503) INTO (SUBPARTITION P_201502,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201504) INTO (SUBPARTITION P_201503,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201505) INTO (SUBPARTITION P_201504,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201506) INTO (SUBPARTITION P_201505,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201507) INTO (SUBPARTITION P_201506,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201508) INTO (SUBPARTITION P_201507,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201509) INTO (SUBPARTITION P_201508,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201510) INTO (SUBPARTITION P_201509,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201511) INTO (SUBPARTITION P_201510,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201512) INTO (SUBPARTITION P_201511,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201600) INTO (SUBPARTITION P_201512,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 P_201501
Y_2015 P_201502
Y_2015 P_201503
Y_2015 P_201504
Y_2015 P_201505
Y_2015 P_201506
Y_2015 P_201507
Y_2015 P_201508
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2015 P_201509
Y_2015 P_201510
Y_2015 P_201511
Y_2015 P_201512
Y_2015 SYS_SUBP21
已選擇38行。
--刪除系統自動建立的子分割槽SYS_SUBP21
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 drop subpartition SYS_SUBP21 UPDATE GLOBAL INDEXES;
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 P_201501
Y_2015 P_201502
Y_2015 P_201503
Y_2015 P_201504
Y_2015 P_201505
Y_2015 P_201506
Y_2015 P_201507
Y_2015 P_201508
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2015 P_201509
Y_2015 P_201510
Y_2015 P_201511
Y_2015 P_201512
已選擇37行。
--SPLIT成功
--收集一下MAX分割槽以及新增的分割槽的統計資訊，新增的2015年的分割槽granularity需要指定partition和subpartition。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'CUSTOMER_CONSOLIDATED2',partname => 'Y_MAX', method_opt => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'PARTITION',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'CUSTOMER_CONSOLIDATED2',partname => 'Y_2015',method_opt => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'PARTITION',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'CUSTOMER_CONSOLIDATED2',partname => 'Y_2015',method_opt => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'SUBPARTITION',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
建立表，表裡面有2013和2014年的分割槽以及子分割槽。
SQL> create table customer_consolidated2
2 (operator_id varchar2(10),
3 customer_id varchar2(10),
4 business_mon_system varchar2(6),
5 business_day_locale varchar2(10),
6 business_start_locale varchar2(10),
7 business_start_system varchar2(10),
8 currency_id varchar2(10))
9 partition by range (business_mon_system )
10 subpartition by range (business_mon_system )
11 ( partition y_2013 values less than(201400)
12 (subpartition p_201301 values less than ('201302'),
13 subpartition p_201302 values less than ('201303'),
14 subpartition p_201303 values less than ('201304'),
15 subpartition p_201304 values less than ('201305'),
16 subpartition p_201305 values less than ('201306'),
17 subpartition p_201306 values less than ('201307'),
18 subpartition p_201307 values less than ('201308'),
19 subpartition p_201308 values less than ('201309'),
20 subpartition p_201309 values less than ('201310'),
21 subpartition p_201310 values less than ('201311'),
22 subpartition p_201311 values less than ('201312'),
23 subpartition p_201312 values less than ('201401')
24 ),
25 partition y_2014 values less than(201500)
26 (subpartition p_201401 values less than ('201402'),
27 subpartition p_201402 values less than ('201403'),
28 subpartition p_201403 values less than ('201404'),
29 subpartition p_201404 values less than ('201405'),
30 subpartition p_201405 values less than ('201406'),
31 subpartition p_201406 values less than ('201407'),
32 subpartition p_201407 values less than ('201408'),
33 subpartition p_201408 values less than ('201409'),
34 subpartition p_201409 values less than ('201410'),
35 subpartition p_201410 values less than ('201411'),
36 subpartition p_201411 values less than ('201412'),
37 subpartition p_201412 values less than ('201501')
38 ),
39 partition y_max values less than (maxvalue)
40 (subpartition p_max values less than (maxvalue)
41 )
42 )
43 ;
表已建立。
--構建資料
SQL> select * from customer_consolidated2;
OPERATOR_I CUSTOMER_I BUSINE BUSINESS_D BUSINESS_S BUSINESS_S CURRENCY_I
---------- ---------- ------ ---------- ---------- ---------- ----------
1 1 201302 1 1 1 1
1 1 201303 1 1 1 1
1 1 201502 1 1 1 1
1 1 201503 1 1 1 1
1 1 201501 1 1 1 1
1 1 201505 1 1 1 1
1 1 201509 1 1 1 1
1 1 201508 1 1 1 1
已選擇8行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立8行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立16行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立32行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立64行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立128行。
SQL> insert into customer_consolidated2 select * from customer_consolidated2;
已建立256行。
SQL> commit;
提交完成。
--檢視目前分割槽和子分割槽情況
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
已選擇25行。
--增加2015年的分割槽
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT PARTITION Y_MAX AT (201600) INTO
2 (PARTITION Y_2015, PARTITION Y_MAX) UPDATE GLOBAL INDEXES;--此時Y_2015下多了一個系統自動生成的子分割槽SYS_SUBP21
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 SYS_SUBP21
已選擇26行。
--增加子分割槽
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201502) INTO (SUBPARTITION P_201501,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';--可以看到新增加的P_201501的子分割槽
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 P_201501
Y_2015 SYS_SUBP21
已選擇27行。
--把2015年剩餘的子分割槽都加入
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201503) INTO (SUBPARTITION P_201502,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201504) INTO (SUBPARTITION P_201503,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201505) INTO (SUBPARTITION P_201504,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201506) INTO (SUBPARTITION P_201505,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201507) INTO (SUBPARTITION P_201506,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201508) INTO (SUBPARTITION P_201507,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201509) INTO (SUBPARTITION P_201508,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201510) INTO (SUBPARTITION P_201509,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201511) INTO (SUBPARTITION P_201510,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201512) INTO (SUBPARTITION P_201511,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 SPLIT SUBPARTITION SYS_SUBP21 AT (201600) INTO (SUBPARTITION P_201512,SUBPARTITION SYS_SUBP21);
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 P_201501
Y_2015 P_201502
Y_2015 P_201503
Y_2015 P_201504
Y_2015 P_201505
Y_2015 P_201506
Y_2015 P_201507
Y_2015 P_201508
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2015 P_201509
Y_2015 P_201510
Y_2015 P_201511
Y_2015 P_201512
Y_2015 SYS_SUBP21
已選擇38行。
--刪除系統自動建立的子分割槽SYS_SUBP21
SQL> ALTER TABLE CUSTOMER_CONSOLIDATED2 drop subpartition SYS_SUBP21 UPDATE GLOBAL INDEXES;
表已更改。
SQL> select partition_name,subpartition_name from dba_tab_subpartitions where table_name='CUSTOMER_CONSOLIDATED2';
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201301
Y_2013 P_201302
Y_2013 P_201303
Y_2013 P_201304
Y_2013 P_201305
Y_2013 P_201306
Y_2013 P_201307
Y_2013 P_201308
Y_2013 P_201309
Y_2013 P_201310
Y_2013 P_201311
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2013 P_201312
Y_2014 P_201401
Y_2014 P_201402
Y_2014 P_201403
Y_2014 P_201404
Y_2014 P_201405
Y_2014 P_201406
Y_2014 P_201407
Y_2014 P_201408
Y_2014 P_201409
Y_2014 P_201410
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2014 P_201411
Y_2014 P_201412
Y_MAX P_MAX
Y_2015 P_201501
Y_2015 P_201502
Y_2015 P_201503
Y_2015 P_201504
Y_2015 P_201505
Y_2015 P_201506
Y_2015 P_201507
Y_2015 P_201508
PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
Y_2015 P_201509
Y_2015 P_201510
Y_2015 P_201511
Y_2015 P_201512
已選擇37行。
--SPLIT成功
--收集一下MAX分割槽以及新增的分割槽的統計資訊，新增的2015年的分割槽granularity需要指定partition和subpartition。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'CUSTOMER_CONSOLIDATED2',partname => 'Y_MAX', method_opt => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'PARTITION',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'CUSTOMER_CONSOLIDATED2',partname => 'Y_2015',method_opt => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'PARTITION',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',TABNAME=>'CUSTOMER_CONSOLIDATED2',partname => 'Y_2015',method_opt => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'SUBPARTITION',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26084062/viewspace-1466932/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- iPad 多工 Spilt View & Size ClassiPadView
- partition table and partition indexIndex
- PARTITION partition01
- PARTITION partition02
- PARTITION partition04
- swift2.0實現String.spilt()方法Swift
- Pruning、Reference Partition、Exchange Partition
- partition timestamp(0) not use partition
- 對Maxvalue上限範圍分割槽進行spilt操作
- hive partitionHive
- over (partition by)
- exchange partition
- oracle partitionOracle
- split partition
- Clique Partition
- Partition Pruning和Partition-Wise Joins
- oracle partition by group by,詳解partition by和group by對比Oracle
- partition table update partition-key result in changing tablespace
- sql shard/partitionSQL
- Partition Pruning
- partition table test
- partition table(1)
- partition table(2)
- exchange partition(轉）
- When to Partition a Table
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- ORA-14257: cannot move partition other than a Range or Hash partition
- oracle composite partition組合分割槽_composite partition rangeOracle
- 7.74 DATAOBJ_TO_PARTITIONOBJ
- 86. Partition List
- exchange partition原理探究
- exchange partition 實驗
- oracle partition的方法Oracle
- exchange partition 的用法
- 關於Hash Partition
- 【Oracle】ORA-14400: inserted partition key does not map to any partitionOracle
- 【實驗】【PARTITION】RANGE分割槽表截斷表分割槽(Truncate Partition)
- 【實驗】【PARTITION】RANGE分割槽表移動表分割槽(Move Partition)