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 Pruning和Partition-Wise Joins
- Clique Partition
- oracle partition by group by,詳解partition by和group by對比Oracle
- 分割槽Partition
- B. Range and Partition
- oracle partition by 語法Oracle
- 7.74 DATAOBJ_TO_PARTITIONOBJ
- 86. Partition List
- 7.73 DATAOBJ_TO_MAT_PARTITIONOBJ
- 3-Partition 問題
- Peace or partition? Cyprus - Espresso EconomistEspresso
- Codeforces 1948E Clique Partition
- Partition|Disk Utility 如何分割磁碟
- ROWNUMBER() OVER( PARTITION BY COL1
- 分割槽partition知識點
- Spark學習——分割槽Partition數Spark
- [LeetCode] 416. Partition Equal Subset SumLeetCode
- 分割槽函式Partition By的基本用法函式
- PostgreSQL DBA(94) - PG 12 Improving Partition(Select)SQL
- PostgreSQL DBA(93) - PG 12 Improving Partition(Insert)SQL
- Kafka分割槽分配策略（Partition Assignment Strategy）Kafka
- Oracle Partition 分割槽詳細總結Oracle
- PostgreSQL DBA(95) - PG 12 Partition(out of shared memory)SQL
- How to Add a New Disk new partition in centos7CentOS
- 分割槽函式partition by的基本用法【轉載】函式
- [20191203]enq: ZA - add std audit table partition.txtENQ
- Hadoop中java.lang.ClassCastException: partition解決方法HadoopJavaASTException
- TiDB 原始碼閱讀系列文章（二十）Table PartitionTiDB原始碼
- QOJ #1280.Fibonacci Partition/Fibonacci性質大雜燴
- (二)《SQL進階教程》學習記錄--GROUP BY、PARTITION BYSQL
- ext4 lvreduce報錯superblock or the partition table is likely to be corruptVRBloC
- codeforce 686div3 F Array Partition單調棧
- MySQL 千萬級資料表 partition 實戰應用MySql
- Oracle查詢Interval partition分割槽表內資料Oracle
- 使用parted建立大分割槽時 mkpart Warning: The resulting partition is not properly
- 從簡單的快速排序說起-Partition-ThreePartition-TopK排序TopK
- row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
- 通過MOVE PARTITION來回收已經使用的空間