oracle分区、子分区、分区索引、分区索引数据查询sql

最新推荐文章于 2024-01-16 09:52:48 发布

好记忆不如烂笔头abc

最新推荐文章于 2024-01-16 09:52:48 发布

阅读量1.6k

点赞数

分类专栏： oracle

原文链接：https://blog.csdn.net/xzg1109/article/details/78743744

版权

oracle 专栏收录该内容

433 篇文章 7 订阅

订阅专栏

--查询表对应的分区，子分区信息
SELECT * FROM USER_TAB_PARTITIONS t WHERE t.TABLE_NAME = 'XXX';
SELECT * FROM USER_TAB_SUBPARTITIONS T WHERE T.TABLE_NAME = 'XXX';

--查询分区，子分区对应的数据，
SELECT * FROM XXX PARTITION(RESULT_PART_201411);
SELECT * FROM XXX SUBPARTITION(RESULT_PART_201411_61);
SELECT * FROM XXX SUBPARTITION(RESULT_PART_201411_62);

--根据索引查找分区、表信息
SELECT * FROM dba_indexes WHERE index_name='XXX_IDX1' ;--索引基本信息
SELECT * FROM dba_part_indexes WHERE index_name='XXX_IDX1' ;--索引对应表，相关信息，local/global，范围分区，list分区
SELECT * FROM dba_ind_partitions WHERE index_name='XXX_IDX1' ;--索引对应分区信息
SELECT * FROM dba_ind_subpartitions WHERE index_name='XXX_IDX1' ;--索引对应子分区信息

https://docs.oracle.com/database/121/VLDBG/GUID-EC3C51AF-2F56-4617-A12B-4540D5C17F33.htm

http://blog.itpub.net/8109090/viewspace-2126345/

实战例子:

SQL> CREATE TABLE JZSH_MRO_SDE
   (    OBJECTID NUMBER(*,0) NOT NULL ENABLE,
        LTESCRSRP NVARCHAR2(50),
        LTENCRSRP NVARCHAR2(50),
        LTESCPCI NVARCHAR2(50),
        EXPTIME NVARCHAR2(50),
        ENBID NVARCHAR2(50),
        FREBAND NVARCHAR2(50),
        SRPDIFF NUMBER(6,0),
        NCCELL NVARCHAR2(1024),
        VENDORNAME NVARCHAR2(32),
        FLDDATE DATE,
        FLDHOUR NVARCHAR2(64),
        FLDMIN NVARCHAR2(64),
        SHAPE ST_GEOMETRY
   )
LOB (SHAPE.POINTS) STORE AS SECUREFILE
 PARTITION BY RANGE (FLDDATE) interval (numtodsinterval(1,'DAY'))
 SUBPARTITION BY HASH (ENBID) SUBPARTITIONS 64
 (PARTITION MRO_P1 VALUES LESS THAN (TO_DATE('2020-07-18','YYYY-MM-DD'))
)
;

Table created.

SQL> CREATE INDEX R9_SDE_P ON JZSH_MRO_SDE (FLDDATE,ENBID) LOCAL;

Index created.

--同结构分区表ctas方式创建
SQL> CREATE TABLE JZSH_MRO_SDE_TEST
    (    OBJECTID,
         LTESCRSRP,
         LTENCRSRP,
         LTESCPCI,
         EXPTIME,
         ENBID,
         FREBAND,
         SRPDIFF,
         NCCELL,
         VENDORNAME,
         FLDDATE,
         FLDHOUR,
         FLDMIN,
         SHAPE
    )
 LOB (SHAPE.POINTS) STORE AS SECUREFILE
  PARTITION BY RANGE (FLDDATE) interval (numtodsinterval(1,'DAY'))
  SUBPARTITION BY HASH (ENBID) SUBPARTITIONS 64
  (PARTITION MRO_P1 VALUES LESS THAN (TO_DATE('2020-07-18','YYYY-MM-DD'))
 )
 AS 
 SELECT * FROM JZSH_MRO_SDE where 1=2;

Table created.

SQL> 
SQL> 
SQL> set pagesize 0
SQL> set long 900000    
SQL> select dbms_metadata.get_ddl('TABLE','JZSH_MRO_SDE_TEST') from dual; 

  CREATE TABLE "SDE"."JZSH_MRO_SDE_TEST"
   (    "OBJECTID" NUMBER(*,0) NOT NULL ENABLE,
        "LTESCRSRP" NVARCHAR2(50),
        "LTENCRSRP" NVARCHAR2(50),
        "LTESCPCI" NVARCHAR2(50),
        "EXPTIME" NVARCHAR2(50),
        "ENBID" NVARCHAR2(50),
        "FREBAND" NVARCHAR2(50),
        "SRPDIFF" NUMBER(6,0),
        "NCCELL" NVARCHAR2(1024),
        "VENDORNAME" NVARCHAR2(32),
        "FLDDATE" DATE,
        "FLDHOUR" NVARCHAR2(64),
        "FLDMIN" NVARCHAR2(64),
        "SHAPE" "SDE"."ST_GEOMETRY"
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
  PARTITION BY RANGE ("FLDDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
  SUBPARTITION BY HASH ("ENBID")
  SUBPARTITIONS 64
 (PARTITION "MRO_P1"  VALUES LESS THAN (TO_DATE(' 2020-07-18 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 ( SUBPARTITION "SYS_SUBP629"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP630"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP631"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP632"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP633"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP634"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP635"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP636"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP637"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP638"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP639"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP640"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP641"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP642"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP643"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP644"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP645"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP646"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP647"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP648"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP649"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP650"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP651"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP652"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP653"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP654"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP655"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP656"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP657"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP658"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP659"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP660"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP661"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP662"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP663"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP664"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP665"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP666"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP667"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP668"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP669"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP670"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP671"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP672"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP673"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP674"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP675"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP676"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP677"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP678"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP679"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP680"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP681"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP682"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP683"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP684"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP685"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP686"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP687"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP688"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP689"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP690"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP691"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ,
  SUBPARTITION "SYS_SUBP692"
 LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" )
  TABLESPACE "USERS"
 NOCOMPRESS ) )

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4
 (PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
  )
PARALLEL;



CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 SUBPARTITION BY LIST (channel_id)
   SUBPARTITION TEMPLATE
   ( SUBPARTITION p_catalog VALUES ('C')
   , SUBPARTITION p_internet VALUES ('I')
   , SUBPARTITION p_partners VALUES ('P')
   , SUBPARTITION p_direct_sales VALUES ('S')
   , SUBPARTITION p_tele_sales VALUES ('T')
   )
 (PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
  )
PARALLEL;



CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY RANGE(amount_sold)
   SUBPARTITION TEMPLATE
   ( SUBPARTITION p_low VALUES LESS THAN (1000)
   , SUBPARTITION p_medium VALUES LESS THAN (4000)
   , SUBPARTITION p_high VALUES LESS THAN (8000)
   , SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)
   )
 (PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
  )
PARALLEL;

https://www.cnblogs.com/lijiaman/p/11872845.html

按时间（年、月、日、周）创建间隔分区

（1）按”年“自动创建分区（关键字：NUMTOYMINTERVAL）

例子：创建按年自动分区表，按照员工生日（birthday字段），每年一个分区。

复制代码

--创建按年分区表

CREATE TABLE interval_year_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTOYMINTERVAL(1,'year')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition2014 VALUES LESS THAN(to_date('2015-01-01:00:00:00','yyyy-mm-dd hh24:mi:ss')),
  PARTITION partition2015 VALUES LESS THAN(to_date('2016-01-01:00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

复制代码

（2）按”月“自动创建分区（关键字：NUMTOYMINTERVAL）

例子：创建按月自动分区表，按照员工生日（birthday字段），每月一个分区。

复制代码

-- 创建按月分区表

CREATE TABLE interval_month_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition201401 VALUES LESS THAN(to_date('2014-02-01:00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

复制代码

（3）按”天（日）“自动创建分区（关键字：NUMTODSINTERVAL）

例子：创建按天自动分区表，按照员工生日（birthday字段），每天一个分区。

复制代码

-- 按天（日）创建分区
CREATE TABLE interval_day_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTODSINTERVAL(1,'day')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition20140101 VALUES LESS THAN(to_date('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

复制代码

（4）按”周“自动创建分区（关键字：NUMTODSINTERVAL）

例子：创建按周自动分区表，按照员工生日（birthday字段），每周一个分区。需要注意的是，这里使用的关键字与“天”分区一样，都是“day”，只是改为了7天。

复制代码

-- 按周创建分区

CREATE TABLE interval_week_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTODSINTERVAL(7,'day')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition201401w VALUES LESS THAN(to_date('2014-01-07 00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

复制代码

（4）按”小时“自动创建分区（关键字：NUMTODSINTERVAL）

复制代码

-- 按”小时”进行分区

CREATE TABLE interval_hour_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTODSINTERVAL(1,'hour')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition20140100 VALUES LESS THAN(to_date('2014-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss'))
);

复制代码

除此之外，还可以按”分钟“、”秒“进行自动分区。

（5）按数字（number）创建间隔分区

例子：按数字进行分区相对简单，这里相近的10个数字进入同一个分区

复制代码

CREATE TABLE interval_number_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(employee_id)
INTERVAL (10) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition10 VALUES LESS THAN(10)
);

复制代码