SQL> --范圍分區
SQL> CREATE TABLE SALES2
2 (
3 PRODUCT_ID VARCHAR2(5),
4 SALES_DATE DATE NOT NULL,
5 SALES_COST NUMBER(10)
6 )
7 PARTITION BY RANGE (SALES_DATE)
8 (
9 PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01', 'YYYY-MM-DD')),
10 PARTITION P2 VALUES LESS THAN (TO_DATE('2004-01-01', 'YYYY-MM-DD')),
11 PARTITION P3 VALUES LESS THAN (MAXVALUE)
12 );
表已創建。
SQL> --散列分區
SQL> CREATE TABLE EMPLOYEE
2 (
3 EMP_ID NUMBER(4),
4 EMP_NAME VARCHAR2(14),
5 EMP_ADDRESS VARCHAR2(15),
6 DEPARTMENT VARCHAR2(10)
7 )
8 PARTITION BY HASH (DEPARTMENT) PARTITIONS 4;
表已創建。
SQL> CREATE TABLE MY_EMP
2 (
3 EMPNO NUMBER(4),
4 ENAME VARCHAR2(15)
5 )
6 PARTITION BY HASH (EMPNO)
7 (
8 PARTITION part_1,
9 PARTITION part_2
10 );
表已創建。
SQL> --復合分區
SQL> CREATE TABLE SALES3
2 (
3 PRODUCT_ID VARCHAR2 (5),
4 SALES_DATE DATE NOT NULL,
5 SALES_COST NUMBER (10)
6 )
7 PARTITION BY RANGE (SALES_DATE)
8 SUBPARTITION BY HASH (PRODUCT_ID)
9 SUBPARTITIONS 5
10 (
11 PARTITION P1 VALUES LESS THAN (DATE '2001-04-01'),
12 PARTITION P2 VALUES LESS THAN (DATE '2001-07-01'),
13 PARTITION P3 VALUES LESS THAN (DATE '2001-09-01'),
14 PARTITION P4 VALUES LESS THAN (MAXVALUE)
15 );
表已創建。
SQL> ---列表分區
SQL> CREATE TABLE EMPLOYER
2 (
3 EMP_ID NUMBER(4),
4 EMP_NAME VARCHAR2(14),
5 EMP_ADDRESS VARCHAR2(15),
6 DEPARTMENT VARCHAR2(10)
7 )
8 PARTITION BY LIST (EMP_ADDRESS)
9 (
10 PARTITION NORTH VALUES ('芝加哥'),
11 PARTITION WEST VALUES ('舊金山', '洛杉矶'),
12 PARTITION SOUTH VALUES ('亞特蘭大', '達拉斯', '休斯頓'),
13 PARTITION EAST VALUES ('紐約', '波士頓')
14 );
表已創建。
SQL> INSERT INTO SALES3 VALUES ('P001', '02-3月-2001', 2000);
已創建 1 行。
SQL> INSERT INTO SALES3 VALUES ('P002', '10-5月-2001', 2508);
已創建 1 行。
SQL> INSERT INTO SALES3 VALUES ('P003', '05-7月-2001', 780);
已創建 1 行。
SQL> INSERT INTO SALES3 VALUES ('P004', '12-9月-2001', 1080);
已創建 1 行。
SQL> COMMIT;
提交完成。
SQL>
SQL> SELECT * FROM SALES3 PARTITION (P3); --在分區P3中查找
PRODU SALES_DATE SALES_COST
----- -------------- ----------
P003 05-7月 -01 780
查看具體的分區
SQL> ed
已寫入 file afiedt.buf
1 select a.table_name,a.partitioned,b.partition_count,c.column_name
2 from user_tables a, user_part_tables b, user_part_key_columns c
3 where a.table_name='EMPLOYEE'
4 and b.table_name='EMPLOYEE'
5* and c.name='EMPLOYEE'
SQL> /
TABLE_NAME PAR PARTITION_COUNT COLUMN_NAME
-------------------- --- --------------- --------------------
EMPLOYEE YES 4 DEPARTMENT
查看已使用的每個分區的大小：
SQL> ed
已寫入 file afiedt.buf
1 select segment_name,partition_name from user_segments
2* where segment_name ='EMPLOYEE' order by 2 desc
SQL> /
SEGMENT_NAME PARTITION_NAME
--------------------------------------------------------------------------------- ---------------
------
EMPLOYEE SYS_P144
EMPLOYEE SYS_P143
EMPLOYEE SYS_P142
EMPLOYEE SYS_P141
查看整個表的大小：
SQL> ed
已寫入 file afiedt.buf
1 select segment_name,sum(bytes/1024/1024) from user_segments
2* where segment_name ='EMPLOYEE' group by segment_name
SQL> /
SEGMENT_NAME
-------------------------------------------------------------------
SUM(BYTES/1024/1024)
--------------------
EMPLOYEE
.25
查看表的記錄數：
SQL> set timing on
SQL> select count(*) from employee;
COUNT(*)
----------
0
已用
