程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 表分區(學習筆記)，表分區學習筆記

表分區(學習筆記)，表分區學習筆記

編輯：Oracle教程

表分區(學習筆記)，表分區學習筆記

表分區：

應用場景：如數據量比較大的表，比如2G的表，可以分20塊來查詢肯定比不分塊查詢速度快

區間分區：常常用於日期字段的分區

less than 是不包括這（）裡的值小於的意思

示例一、創建區間分區

--創建表
CREATE TABLE drawlist(
     draw_dt         DATE       NOT NULL
)
--創建表分區
PARTITION BY RANGE(draw_dt)(
          PARTITION part_1 VALUES LESS THAN (to_date('1/1/2009','dd-mm-yyyy')),
          PARTITION part_2 VALUES LESS THAN (to_date('1/1/2011','dd-mm-yyyy')),
          PARTITION part_3 VALUES LESS THAN(Maxvalue)
);
--表和分區要同時創建，不然無法創建分區

插入數據

--插入數據
INSERT INTO drawlist(draw_dt)VALUES(to_date('2008-12-31','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-1-1','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2010-12-31','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2011-1-1','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2011-5-5','yyyy-mm-dd'));

查詢

--查詢
SELECT * FROM drawlist;
SELECT * FROM drawlist PARTITION(part_1); --查詢分區1
SELECT * FROM drawlist PARTITION(part_2); --查詢分區2
SELECT * FROM drawlist PARTITION(part_3); --查詢分區3

示例二、創建散列分區

--創建表
CREATE TABLE drawlist(
     draw_dt         DATE       NOT NULL
)
--創建表分區
PARTITION BY RANGE(draw_dt)(
          PARTITION part_1 VALUES LESS THAN (to_date('1/1/2009','dd-mm-yyyy')),
          PARTITION part_2 VALUES LESS THAN (to_date('1/1/2011','dd-mm-yyyy')),
          PARTITION part_3 VALUES LESS THAN(Maxvalue)
);
--表和分區要同時創建，不然無法創建分區
--插入數據
INSERT INTO drawlist(draw_dt)VALUES(to_date('2008-12-31','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-1-1','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2010-12-31','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2011-1-1','yyyy-mm-dd'));
INSERT INTO drawlist(draw_dt)VALUES(to_date('2011-5-5','yyyy-mm-dd'));

--查詢
SELECT * FROM hash_table;
SELECT COUNT(*) FROM hash_table;
SELECT COUNT(*) FROM hash_table PARTITION(part_1);
SELECT COUNT(*) FROM hash_table PARTITION(part_2);
SELECT COUNT(*) FROM hash_table PARTITION(part_3);
SELECT COUNT(*) FROM hash_table PARTITION(part_4);

列表分區:主要用區代碼數據庫如郵編，區號之類

示例三、創建列表分區

--創建表
CREATE TABLE area(
       CODE    INTEGER  NOT NULL,
       NAME    VARCHAR2(10)
)
--創建列表分區
PARTITION BY LIST(CODE)(
      PARTITION part_1 VALUES(102200,102202,102203),
      PARTITION part_2 VALUES(164300,164302,164303)
);
--使用數據生成器，插入記錄
--查詢
SELECT * FROM area;
SELECT * FROM area PARTITION(part_1);
SELECT * FROM area PARTITION(part_2);

示例四、組合分區--區間-散列分區

--區間散列
--創建表
CREATE TABLE range_hash(
          dt_date        DATE              NOT NULL,
          hash_no        INTEGER           NOT NULL
)
--創建區間散列分區
PARTITION BY RANGE(dt_date) SUBPARTITION BY HASH(hash_no)
(
         PARTITION part_1 VALUES LESS THAN(to_date('1/1/2009','dd-mm-yyyy')),
         PARTITION part_2 VALUES LESS THAN(to_date('1/1/2011','dd-mm-yyyy')),
         PARTITION part_3 VALUES LESS THAN(maxvalue)
       
);
--區間散列分區，先按區間分區再按散列分區，這時散列的意思不大，散列仍然要按照區間進行分區

示例五、組合分區--區間-列表分區

--區間列表
--創建表
CREATE TABLE range_list(
           dt_date        DATE              NOT NULL,
           CODE       INTEGER           NOT NULL

)
--創建區間列表分區
PARTITION BY RANGE(dt_date) SUBPARTITION BY LIST(CODE)(
  PARTITION part_1 VALUES LESS THAN(to_date('1/1/2009','dd-mm-yyyy'))(
             SUBPARTITION part1_list1 VALUES(102200),
             SUBPARTITION part1_list2 VALUES(164300)
  ),
   PARTITION part_2 VALUES LESS THAN(to_date('1/1/2011','dd-mm-yyyy'))(
             SUBPARTITION part2_list1 VALUES(102200),
             SUBPARTITION part2_list2 VALUES(164300)
  ),
   PARTITION part_3 VALUES LESS THAN(MAXVALUE)(
             SUBPARTITION part3_list1 VALUES(102200),
             SUBPARTITION part3_list2 VALUES(164300)
  )
);
--插入數據
INSERT INTO range_list(dt_date,code)VALUES(to_date('2008-12-31','yyyy-mm-dd'),102200);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2008-12-31','yyyy-mm-dd'),164300);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2010-12-31','yyyy-mm-dd'),102200);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2010-12-31','yyyy-mm-dd'),164300);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2011-12-31','yyyy-mm-dd'),102200);
INSERT INTO range_list(dt_date,code)VALUES(to_date('2009-12-31','yyyy-mm-dd'),164300);
--查詢
SELECT * FROM range_list;
SELECT * FROM RANGE_list PARTITION(part_1);
SELECT * FROM range_list SUBPARTITION  (part1_list1);--查詢子分區
SELECT * FROM range_list SUBPARTITION  (part1_list2);

高界限的分區不能合並到低界限的分區中

維護分區

-創建表
CREATE TABLE dlist(
       dl_date          DATE         NOT NULL
)
--創建區間分區
PARTITION BY RANGE(dl_date)(
          PARTITION part_1 VALUES LESS THAN (to_date('1/1/2009','dd-mm-yyyy')),
          PARTITION part_2 VALUES LESS THAN (to_date('1/1/2011','dd-mm-yyyy')),
          PARTITION part_3 VALUES LESS THAN(Maxvalue)
);
--插入數據
INSERT INTO dlist(dl_date)VALUES(to_date('2008-12-31','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2009-1-1','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2010-12-31','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2009-6-6','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-1-1','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-5-5','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-12-31','yyyy-mm-dd'));
--查詢
SELECT * FROM dlist;
SELECT * FROM dlist PARTITION(part_1);
SELECT * FROM dlist PARTITION(part_2);
SELECT * FROM dlist PARTITION(part_3);

增加分區

-增加分區表中時新增加的分區，必須高於已經有分於的最後一個分區界限
ALTER TABLE dlist ADD PARTITION part_4 VALUES LESS THAN(to_date('1/1/2012','dd-mm-yyyy'));
--要先刪除PART_3，maxvalue
--刪除分區表
ALTER TABLE dlist DROP PARTITION part_3;
--drop數據一起刪除

--插入數據
INSERT INTO dlist(dl_date)VALUES(to_date('2011-5-5','yyyy-mm-dd'));
INSERT INTO dlist(dl_date)VALUES(to_date('2011-12-31','yyyy-mm-dd'));
--查詢
SELECT * FROM dlist;
SELECT * FROM dlist PARTITION(part_1);
SELECT * FROM dlist PARTITION(part_2);
SELECT * FROM dlist PARTITION(part_4);

截斷分區

--截斷分區
ALTER TABLE dlist TRUNCATE PARTITION part_1;
--查詢

SELECT * FROM dlist PARTITION(part_1);

合並分區

--合並分區
ALTER TABLE dlist MERGE PARTITIONS part_2,part_4 INTO PARTITION part_4;
--合並後的分區會消失
SELECT * FROM dlist PARTITION(part_2);
--會提示分區不存在
SELECT * FROM dlist PARTITION(part_4);