oracle数据表创建分区与查询

时间：2024-03-07 22:41:01

场景： 遇到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\');

 

相关文章