DaiXT:Oracle数据库-分区

查询oracle数据库的Partitioning 是否为true

首先查询自己的数据库是否为企业版，如果Partitioning 是否为FALSE一般版本非企业版。

select * from v$option s order by s.PARAMETER desc

创建自己的表空间（可跳过）

可利用指定的表空间来创建分区

CREATE TABLESPACE tablespace DATAFILE 'F:/tablespace/tablespace.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

常用的分区方式有三种：range（范围）分区、list（列表）分区、hash（散列）分区

range（范围）分区
创建表格、创建range（范围）分区
按时间分区

CREATE TABLE part_tab_customer_by_range 
( 
  CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
  FIRST_NAME VARCHAR2(30) NOT NULL, 
  LAST_NAME VARCHAR2(30) NOT NULL, 
  PHONE VARCHAR2(15) NOT NULL, 
  EMAIL VARCHAR2(80), 
  SEX VARCHAR2(10),
  STATUS VARCHAR2(10),
  INSERT_DATE DATE 
) 
PARTITION BY RANGE (INSERT_DATE) 
( 
  PARTITION DATE_RANGE1 VALUES LESS THAN (TO_DATE('2001-01-01', 'YYYY-MM-DD')), 
  PARTITION DATE_RANGE2 VALUES LESS THAN (TO_DATE('2007-01-01', 'YYYY-MM-DD')), 
  PARTITION DATE_RANGE3 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))  
);

查询表的分区

 select * from dba_tab_partitions  
 where table_name = upper('PART_TAB_CUSTOMER_BY_RANGE') 
 order by partition_name  desc; 

给表–添加分区

ALTER TABLE part_tab_customer_by_range ADD PARTITION DATE_RANGE4 VALUES LESS THAN(TO_DATE('2035-06-01','YYYY-MM-DD'));

-- 编写存储过程对分区表进行信息插入 
CREATE OR REPLACE PROCEDURE "INSERTDATA"(CUSTOMER_ID IN NUMBER,
                                         FIRST_NAME  IN VARCHAR2,
                                         LAST_NAME   IN VARCHAR2,
                                         PHONE       IN VARCHAR2,
                                         EMAIL       IN VARCHAR2,
                                         SEX         IN VARCHAR,
                                         STATUS      IN VARCHAR,
                                         INSERT_DATE IN DATE) IS
BEGIN
  INSERT INTO part_tab_customer_by_range
  VALUES
    (CUSTOMER_ID,
     FIRST_NAME,
     LAST_NAME,
     PHONE,
     EMAIL,
     SEX,
     STATUS,
     INSERT_DATE);
END;

调用存储过程插入数据

BEGIN
  INSERTDATA(1,'q','q','q','q','q','q',TO_DATE('2001-01-01', 'YYYY-MM-DD'));
  INSERTDATA(2,'q','q','q','q','q','q',TO_DATE('2001-01-01', 'YYYY-MM-DD'));
  INSERTDATA(3,'q','q','q','q','q','q',TO_DATE('2001-02-01', 'YYYY-MM-DD'));
  INSERTDATA(4,'q','q','q','q','q','q',TO_DATE('2001-03-01', 'YYYY-MM-DD'));
  INSERTDATA(5,'q','q','q','q','q','q',TO_DATE('2001-04-01', 'YYYY-MM-DD'));
  INSERTDATA(6,'q','q','q','q','q','q',TO_DATE('2001-05-01', 'YYYY-MM-DD'));
  INSERTDATA(7,'q','q','q','q','q','q',TO_DATE('2001-06-01', 'YYYY-MM-DD'));
  INSERTDATA(8,'q','q','q','q','q','q',TO_DATE('2007-01-01', 'YYYY-MM-DD'));
  INSERTDATA(9,'q','q','q','q','q','q',TO_DATE('2027-02-01', 'YYYY-MM-DD'));
  INSERTDATA(10,'q','q','q','q','q','q',TO_DATE('2007-03-01', 'YYYY-MM-DD'));
  INSERTDATA(11,'q','q','q','q','q','q',TO_DATE('2007-04-01', 'YYYY-MM-DD'));
  INSERTDATA(12,'q','q','q','q','q','q',TO_DATE('2007-05-01', 'YYYY-MM-DD'));
  INSERTDATA(13,'q','q','q','q','q','q',TO_DATE('2000-04-01', 'YYYY-MM-DD'));
  INSERTDATA(14,'q','q','q','q','q','q',TO_DATE('2000-05-01', 'YYYY-MM-DD'));
  END;

查询分区的表

SELECT * FROM part_tab_customer_by_range PARTITION(DATE_RANGE1);
SELECT * FROM part_tab_customer_by_range PARTITION(DATE_RANGE2);
SELECT * FROM part_tab_customer_by_range PARTITION(DATE_RANGE3);
SELECT * FROM part_tab_customer_by_range PARTITION(DATE_RANGE4);

创建List列表分区-‘男’|‘女’

CREATE TABLE LISTTEST(
ID NUMBER PRIMARY KEY,
SEX VARCHAR(10)
)
PARTITION BY LIST (SEX)
(
PARTITION LIST1 VALUES('男'),
PARTITION LIST2 VALUES('女') 
);

创建存储过程-奇数的时候设置为男，偶数的时候设置为女

CREATE OR REPLACE PROCEDURE "INSERTLIST" IS
  i NUMBER;
BEGIN
  i := 1;
  WHILE i < 10 loop
    IF MOD(i, 2) > 0 THEN
      INSERT INTO LISTTEST (ID, SEX) VALUES (i, '男');
    ELSE
      INSERT INTO LISTTEST (ID, SEX) VALUES (i, '女');
    END IF;
    i := i + 1;
  END LOOP;
  commit;
END;

调用存储过程

BEGIN
INSERTLIST();
END;

查询分区数据

SELECT * FROM LISTTEST PARTITION(LIST1);
SELECT * FROM LISTTEST PARTITION(LIST2);

查询分区的时候区分字段的大小写，如果输入的参数有大小写混合或者全是小写，建议使用upper函数进行字符串的转换

select * from dba_tab_partitions where table_name=upper('listtest');
select * from dba_tab_partitions where table_name='LISTTEST';


--创建表、创建HASH（散列）分区
CREATE TABLE HASHTEST(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(10)
)
PARTITION BY HASH(ID)
(
 PARTITION hash1 tablespace tablespace01 , 
 PARTITION hash2 tablespace tablespace01 
);


--查询分区的名称
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='HASHTEST';



--查询分区的名称
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='LISTTEST';


--将LISTTEST表的两个分区进行合并
ALTER TABLE LISTTEST MERGE PARTITIONS list1,list2 INTO PARTITION list3; 

--拆分分区-list
ALTER TABLE LISTTEST SPLIT PARTITION list3 VALUES ('男') INTO (PARTITION list1, PARTITION list2);



--将LISTTEST表的两个分区进行合并
ALTER TABLE part_tab_customer_by_range MERGE PARTITIONS DATE_RANGE1,DATE_RANGE2 INTO PARTITION DATE_RANGE5; 

--拆分分区-range
ALTER TABLE part_tab_customer_by_range SPLIT PARTITION DATE_RANGE5 AT(TO_DATE('2001-01-01','YYYY-MM-DD')) INTO (PARTITION DATE_RANGE1,PARTITION DATE_RANGE2); 

--查询分区的名称
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=upper('part_tab_customer_by_range');