oracle数据表创建分区与查询

时间：2023-03-08 21:49:18

场景： 遇到1亿数据量的数据需要根据用户名做些数据统计分析，想直接做些聚合计算基本没可能，于是打算先根据日期按照年月创建分区，然后对各个分区分别进行统计，最后汇总结果。

有两种方法，分别是手工设置分区和自动设置分区，自动设置分区在oracle11g版本才新增的功能。

方法一：自动设置分区的表创建方式如下:

create table TEST_RANGE

(

  data_dt     NVARCHAR2(10),

  deviceid    NVARCHAR2(100),

  useraccount NVARCHAR2(200),

  macadd      NVARCHAR2(200),

  platform    NVARCHAR2(20),

  clientver   NVARCHAR2(20),

  ip          NVARCHAR2(30),

  channel     NVARCHAR2(20),

  color       NUMBER(15),

  lastlogin   DATE,

  logintimes  NUMBER(20),

  batch_id    NUMBER(38)

)

partition by range (lastlogin)

interval(numtoyminterval(1,'MONTH'))

(

  PARTITION P1 VALUES LESS THAN (TO_DATE('2016-07-01','YYYY-MM-DD'))

)

方法二： 手工创建分区记录

操作方式，新建分区表，然后用ETL工具抽取源并写入新表。

create table TEMP_TEST

(

  log_id              NUMBER(20),

  user_id             NUMBER(20),

  user_name           NVARCHAR2(200),

  money               NUMBER(30,8),

  history_total       NUMBER(30,8),

  order_id            NVARCHAR2(100),

  money_type_id       NUMBER(20),

  reason_id           NUMBER(20),

  operation_user_name NVARCHAR2(200),

  operation_user_ip   NVARCHAR2(100),

  remark              NVARCHAR2(200),

  osn                 NVARCHAR2(100),

  isn                 NVARCHAR2(100),

  return_sn           NVARCHAR2(100),

  repeate_flag        NUMBER(3),

  create_date         DATE,

  modify_date         DATE,

  status              NUMBER(20),

  batch_id            NUMBER(38)

) nologging

partition  by range (create_date)

(

  partition  create_date1500 values less than (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1501 values less than (TO_DATE('2015-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1502 values less than (TO_DATE('2015-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1503 values less than (TO_DATE('2015-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1504 values less than (TO_DATE('2015-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1505 values less than (TO_DATE('2015-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1506 values less than (TO_DATE('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1507 values less than (TO_DATE('2015-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1508 values less than (TO_DATE('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1509 values less than (TO_DATE('2015-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1510 values less than (TO_DATE('2015-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1511 values less than (TO_DATE('2015-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1512 values less than (TO_DATE('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1601 values less than (TO_DATE('2016-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1602 values less than (TO_DATE('2016-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1603 values less than (TO_DATE('2016-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1604 values less than (TO_DATE('2016-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1605 values less than (TO_DATE('2016-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1606 values less than (TO_DATE('2016-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1607 values less than (TO_DATE('2016-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1608 values less than (TO_DATE('2016-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1609 values less than (TO_DATE('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1610 values less than (TO_DATE('2016-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1611 values less than (TO_DATE('2016-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS,

  partition  create_date1612 values less than (TO_DATE('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace DW_STG_TBS

)

2. 表分区的查询

注意要查询的表的名字要大写

SELECT table_name,partition_name FROM user_tab_partitions

WHERE table_name=upper('temp_test');

相关文章