#眉標=Oracle、Range、Hash
#副標=管理Oracle資料庫必要的功力(7)
#大標=實用的Oracle資料表分割模式
#作者=文／圖 張天慧


================<反灰>=================
CREATE TABLE [… column …]
PARTITION BY RANGE [(column_list)] [PARTITION specifications ];
================<END>=================




==================<反灰>====================
CREATE TABLE TEST.TEST_TABLE (
ID VARCHAR2(10) NOT NULL,
NAME VARCHAR2(10) NOT NULL,
DAY DATE NOT NULL)
PARTITION BY RANGE (DAY)
(PARTITION TEST_TABLE_0601 VALUES LESS THAN(TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_0801_TAB,
PARTITION TEST_TABLE_0607 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_0807_TAB,
PARTITION TEST_TABLE_0701 VALUES LESS THAN(TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_0901_TAB,
PARTITION TEST_TABLE_0707 VALUES LESS THAN(TO_DATE('2009-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_0907_TAB,
PARTITION TEST_TABLE_0801 VALUES LESS THAN(TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_1001_TAB,
PARTITION TEST_TABLE_0807 VALUES LESS THAN(TO_DATE('2010-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_1007_TAB,
PARTITION TEST_TABLE_0901 VALUES LESS THAN(TO_DATE('2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_1101_TAB,
PARTITION TEST_TABLE_0907 VALUES LESS THAN(TO_DATE('2011-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE TEST_1107_TAB);
==================<END>====================




===========<反灰>====================
CREATE TABLE [ … column …]
PARTITION BY HASH [(column_list)] [PARTITION specifications ]; 
================<END>===================




===========<反灰>====================
CREATE TABLE TEST.TEST_TABLE(
ID VARCHAR2(10) NOT NULL,
NAME VARCHAR2(10) NOT NULL,
DEPARTMENT VARCHAR2(10) NOT NULL)
PARTITION BY HASH (DEPARTMENT)
(PARTITION TEST_TABLE01 TABLESPACE TABLESPACE01,
PARTITION TEST_TABLE02 TABLESPACE TABLESPACE02,
PARTITION TEST_TABLE03 TABLESPACE TABLESPACE03,
PARTITION TEST_TABLE04 TABLESPACE TABLESPACE04,
PARTITION TEST_TABLE05 TABLESPACE TABLESPACE05,
PARTITION TEST_TABLE06 TABLESPACE TABLESPACE06);
================<END>===================




===========<反灰>====================
CREATE TABLE [ … column …]
PARTITION BY LIST [(column)] [PARTITION specifications ]; 
================<END>===================



===========<反灰>====================
CREATE TABLE TEST.TEST_TABLE(
ID VARCHAR2(10) NOT NULL,
NAME VARCHAR2(10) NOT NULL,
DEPARTMENT VARCHAR2(10) NOT NULL)
PARTITION BY LIST (DEPARTMENT)
(
PARTITION TEST_TABLE01 VALUES ( HR, SALES) TABLESPACE TABLESPACE01,
PARTITION TEST_TABLE02 VALUES ( IT, RD) TABLESPACE TABLESPACE02,
PARTITION TEST_TABLE03 VALUES ( OP, NULL) TABLESPACE TABLESPACE03
);
================<END>===================




===========<反灰>====================
CREATE TABLE [ … column …]
PARTITION BY RANGE [ (column_list1)] 
 SUBPARTITION BY  [(column_list2)] 
   SUBPARTITION 3 STORE IN [( tablespace_list)]
[PARTITION specifications ]; 
================<END>===================




===========<反灰>====================
CREATE TABLE TEST.TEST_TABLE(
ID VARCHAR2(10) NOT NULL,
NAME VARCHAR2(10) NOT NULL,
DAY DATE NOT NULL,
DEPARTMENT VARCHAR2(10) NOT NULL)
PARTITION BY RANGE (DAY) SUBPARTITION BY HASH (DEPARTMENT)
SUBPARTITION 3 STORE IN (TABLESPACE01, TABLESPACE02, TABLESPACE03)
(
PARTITION TABLE01 VALUES LESS THAN(TO_DATE(’2006-01-01’,’YYYY-MM-DD’)),
PARTITION TABLE01 VALUES LESS THAN(TO_DATE(’2007-01-01’,’YYYY-MM-DD’)),
PARTITION TABLE03 VALUES LESS THAN(MAXVALUE)
);
================<END>===================





===========<反灰>====================
CREATE CLUSTER cluster_name
  [column data type][SIZE integer [K | M] ][TABLESPACE tablespace_name]
  [STORAGE storage_cluse][HASHKEYS integer HASH IS expression][INDEX]
================<END>===================




===========<反灰>====================
CREATE CLUSTER TEST_CLUSTER (DEPT_ID CHAR(10))
  SIZE 500 TABLESPACE TEABLEAPCE01
  STORAGE (INITIAL 64K NEXT 64K)INDEX;
================<END>===================




===========<反灰>====================
CREATE INDEX TEST_CLUSTER_INDX ON CLUSTER TEST_CLUSTER 
  TABLESPACE TABLESPACE_INDX;
================<END>===================




===========<反灰>====================
CREATE TABLE EMPLOYEE
  (NAME VARCHAR2(10),
   EMP_ID VARCHAR2(8),
   DEPT_ID VARCHAR2(3)
  )
 CLUSTER TEST_CLUSTER (DEPT_ID);

CREATE TABLE DEPARTMENT
  (DEPT_NAME VARCHAR2(10),
   DEPT_ID VARCHAR2(3)
  )
 CLUSTER TEST_CLUSTER (DEPT_ID);
================<END>===================





===========<反灰>====================
CREATE CLUSTER TEST_HASH (DEPT_ID CHAR(10))
  SIZE 500
  TABLESPACE TEABLEAPCE01
  HASHKEY 100
  HASH IS (DEPT_ID);
================<END>===================




===========<反灰>====================
CREATE TABLE EMPLOYEE
  (NAME VARCHAR2(10),
   EMP_ID VARCHAR2(8),
   DEPT_ID VARCHAR2(3)
  )
 CLUSTER TEST_HASH (DEPT_ID);
================<END>===================





===========<反灰>====================
CREATE GLOBAL TEMPORARY TABLE test_tmp
(
 COLUMN01 VARCHAR(10),
 COLUMN02 VARCHAR(10),
 COLUMN03 VARCHAR(10)
) ON COMMIT PRESERVE ROWS;
================<END>===================





===========<反灰>====================
CREATE GLOBAL TEMPORARY TABLE test_temp 
    AS SELECT * FROM test_table01;
================<END>===================





===========<反灰>====================
CREATE TABLE TEST.IOT_TABLE(
  COLUMN01 VARCHAR2(10) NOT NULL,     
  COLUMN02 VARCHAR2(10) NOT NULL, 
  COLUMN03 VARCHAR2(10) NOT NULL, 
  CONSTRAINT PK_COLUMN01 PRIMARY KEY(COLUMN01)) 
  ORGANIZATION INDEX TABLESPACE TABLESPACE01;
================<END>===================





===========<反灰>====================
CREATE MATERIALIZED VIEW test.mv_test
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
START WITH TO_DATE('03-07-2007 20:00:00', 'DD-MM-YYYY HH24:MI:SS') 
NEXT TRUNC(SYSDATE,'HH')+1/24 
AS
SELECT * FROM test.mv_test@db_link;
================<END>===================





===========<反灰>====================
EXECUTE DBMS_JOB.BROKEN('112');
COMMIT;
================<END>===================