建按月日自增分区表
来源:互联网 发布:react 引用外部js 编辑:程序博客网 时间:2024/05/12 23:33
一、建按月自增分区表:
1.1建表SQL> create table month_interval_partition_table (id number,time_col date) partition by range(time_col)
2 interval (numtoyminterval(1,'month'))
3 (
4 partition p_month_1 values less than (to_date('2014-01-01','yyyy-mm-dd'))
5 );
Table created
1.2 查看现在分区
SQL> select table_name,partition_name from user_tab_partitions where table_name='MONTH_INTERVAL_PARTITION_TABLE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
MONTH_INTERVAL_PARTITION_TABLE P_MONTH_1
1.3、插入数据
SQL> begin
2 for i in 0..11 loop
3 insert into MONTH_INTERVAL_PARTITION_TABLE values(i,add_months(to_date('2014-01-01','yyyy-mm-dd'),i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed
1.4、查看分区
SQL> select table_name,partition_name from user_tab_partitions where table_name='MONTH_INTERVAL_PARTITION_TABLE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
MONTH_INTERVAL_PARTITION_TABLE P_MONTH_1
MONTH_INTERVAL_PARTITION_TABLE SYS_P22
MONTH_INTERVAL_PARTITION_TABLE SYS_P23
MONTH_INTERVAL_PARTITION_TABLE SYS_P24
MONTH_INTERVAL_PARTITION_TABLE SYS_P25
MONTH_INTERVAL_PARTITION_TABLE SYS_P26
MONTH_INTERVAL_PARTITION_TABLE SYS_P27
MONTH_INTERVAL_PARTITION_TABLE SYS_P28
MONTH_INTERVAL_PARTITION_TABLE SYS_P29
MONTH_INTERVAL_PARTITION_TABLE SYS_P30
MONTH_INTERVAL_PARTITION_TABLE SYS_P31
MONTH_INTERVAL_PARTITION_TABLE SYS_P32
MONTH_INTERVAL_PARTITION_TABLE SYS_P33
13 rows selected
1.5查看其中一个分区数据
SQL> select * From MONTH_INTERVAL_PARTITION_TABLE partition(SYS_P22);
ID TIME_COL
---------- -----------
0 2014/1/1
二、创建按天的分区表
2.1建表
SQL> create table day_interval_partition_table (id number,time_col date) partition by range(time_col)
2 interval (numtodsinterval(1,'day'))
3 (
4 partition p_day_1 values less than (to_date('2014-01-01','yyyy-mm-dd'))
5 );
Table created
2.2、查看现在分区
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DAY_INTERVAL_PARTITION_TABLE P_DAY_1
2.3、插入数据
SQL> begin
2 for i in 1..12 loop
3 insert into DAY_INTERVAL_PARTITION_TABLE values(i,trunc(to_date('2014-01-01','yyyy-mm-dd')+i));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed
2.4查看分区
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DAY_INTERVAL_PARTITION_TABLE P_DAY_1
DAY_INTERVAL_PARTITION_TABLE SYS_P34
DAY_INTERVAL_PARTITION_TABLE SYS_P35
DAY_INTERVAL_PARTITION_TABLE SYS_P36
DAY_INTERVAL_PARTITION_TABLE SYS_P37
DAY_INTERVAL_PARTITION_TABLE SYS_P38
DAY_INTERVAL_PARTITION_TABLE SYS_P39
DAY_INTERVAL_PARTITION_TABLE SYS_P40
DAY_INTERVAL_PARTITION_TABLE SYS_P41
DAY_INTERVAL_PARTITION_TABLE SYS_P42
DAY_INTERVAL_PARTITION_TABLE SYS_P43
DAY_INTERVAL_PARTITION_TABLE SYS_P44
DAY_INTERVAL_PARTITION_TABLE SYS_P45
2.5查看数据
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DAY_INTERVAL_PARTITION_TABLE P_DAY_1
DAY_INTERVAL_PARTITION_TABLE SYS_P34
DAY_INTERVAL_PARTITION_TABLE SYS_P35
DAY_INTERVAL_PARTITION_TABLE SYS_P36
DAY_INTERVAL_PARTITION_TABLE SYS_P37
DAY_INTERVAL_PARTITION_TABLE SYS_P38
DAY_INTERVAL_PARTITION_TABLE SYS_P39
DAY_INTERVAL_PARTITION_TABLE SYS_P40
DAY_INTERVAL_PARTITION_TABLE SYS_P41
DAY_INTERVAL_PARTITION_TABLE SYS_P42
DAY_INTERVAL_PARTITION_TABLE SYS_P43
DAY_INTERVAL_PARTITION_TABLE SYS_P44
DAY_INTERVAL_PARTITION_TABLE SYS_P45
13 rows selected
SQL> select * From DAY_INTERVAL_PARTITION_TABLE;
ID TIME_COL
---------- -----------
1 2014/1/2
2 2014/1/3
3 2014/1/4
4 2014/1/5
5 2014/1/6
6 2014/1/7
7 2014/1/8
8 2014/1/9
9 2014/1/10
10 2014/1/11
11 2014/1/12
12 2014/1/13
12 rows selected
SQL> select * From DAY_INTERVAL_PARTITION_TABLE partition(SYS_P39);
ID TIME_COL
---------- -----------
6 2014/1/7
0 0
- 建按月日自增分区表
- 按月创建分区表
- mssql 2000 按月分区表(来于网络)
- Postgresql 添加分区表(按月和按日通用)
- oracle创建日分区表
- 按月分区触发器函数编写,自动创建分区表
- mysql分区表添加含自增列的主键
- sqlserver中如何实现时间按月,日,小时分组查询
- sqlserver中如何实现时间按月,日,小时分组查询
- 按月、周、日查询统计数据量的sql语句
- 应用存储过程按月建数据表
- 应用存储过程按月建数据表
- Mysql 分别按月, 日为组group,进行统计排序order
- 一个关于使用 $month,$dayOfMonth 进行按月/日统计的问题
- sqlserver中如何实现时间按月,日,小时等时间分组查询
- Oracle建分区表
- 分区表
- 分区表
- Clojure 学习入门(17)- 异常处理
- Package and Namespace
- leetcode_3 - Longest Substring Without Repeating Characters
- 欢迎使用CSDN-markdown编辑器
- Cookie详细用法
- 建按月日自增分区表
- Android之FileOutputStream文件存储
- Android增量更新流程讲解
- PHP MySQL Update
- cc1.exe: error: argument to '-O' should be a non-negative integer
- Ceres-Solver库使用(四)-- 一些例子
- Matlab有趣代码
- 又开始要忙了
- SAP屏幕设计器专题:树控件的使用(九)