订单主表（tOrder）
列名 中文名 数据类型 长度 约束 备注
oid 订单ID 字符串 12 主键
UIID 用户ID 字符串 6 非空
ODATE 销售日期 日期 默认系统时间
Osummoney 总金额 数值 12，3
Otype 订单状态 字符串 1 1、审核中，2、发货中，3、已完结，4、取消 将订单主表进行分区创建，分区条件是按照订单日期按月分区
列名 中文名 数据类型 长度 约束 备注
oid 订单ID 字符串 12 主键
UIID 用户ID 字符串 6 非空
ODATE 销售日期 日期 默认系统时间
Osummoney 总金额 数值 12，3
Otype 订单状态 字符串 1 1、审核中，2、发货中，3、已完结，4、取消 将订单主表进行分区创建，分区条件是按照订单日期按月分区
SQL> create table tOrder(
2 oid varchar2(12) primary key,
3 uiid varchar2(6),
4 odate date default sysdate,
5 osummoney number(12,3),
6 otype varchar2(1))
7 partition by range(odate)
8 (
9 partition order_201001 values less than(to_date('201001', 'yyyymm')),
10 partition order_201002 values less than(to_date('201002', 'yyyymm')),
11 partition order_201003 values less than(to_date('201003', 'yyyymm')));表已创建。SQL> insert into tOrder(oid, uiid, odate, osummoney, otype)
2 values('0001', '0005', to_date('201001','yyyymm'), 1000, 1);已创建 1 行。SQL> ed
已写入 file afiedt.buf 1 insert into tOrder(oid, uiid, odate, osummoney, otype)
2* values('0002', '0005', to_date('201002','yyyymm'), 1000, 1)
SQL> /已创建 1 行。SQL> ed
已写入 file afiedt.buf 1 insert into tOrder(oid, uiid, odate, osummoney, otype)
2* values('0003', '0005', to_date('201003','yyyymm'), 1000, 1)
SQL> /
insert into tOrder(oid, uiid, odate, osummoney, otype)
*
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区
SQL> ed
已写入 file afiedt.buf 1 insert into tOrder(oid, uiid, odate, osummoney, otype)
2* values('0003', '0005', to_date('200912','yyyymm'), 1000, 1)
SQL> /已创建 1 行。SQL> set autotrace on;
SQL> select *
2 from tOrder
3 where odate < to_date('201001', 'yyyymm');OID UIID ODATE OSUMMONEY O
------------ ------ -------------- ---------- -
0003 0005 01-12月-09 1000 1
执行计划
----------------------------------------------------------
Plan hash value: 2034643695
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01
| | |
| 1 | PARTITION RANGE SINGLE| | 1 | 37 | 2 (0)| 00:00:01
| 1 | 1 |
| 2 | TABLE ACCESS FULL | TORDER | 1 | 37 | 2 (0)| 00:00:01
| 1 | 1 |
--------------------------------------------------------------------------------
-----------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
168 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
650 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed SQL> ed
已写入 file afiedt.buf 1 select *
2 from tOrder
3 where odate >= to_date('201002', 'yyyymm')
4* and odate < to_date('201003', 'yyyymm')
SQL> /OID UIID ODATE OSUMMONEY O
------------ ------ -------------- ---------- -
0002 0005 01-2月 -10 1000 1
执行计划
----------------------------------------------------------
Plan hash value: 2034643695
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01
| | |
| 1 | PARTITION RANGE SINGLE| | 1 | 37 | 2 (0)| 00:00:01
| 3 | 3 |
| 2 | TABLE ACCESS FULL | TORDER | 1 | 37 | 2 (0)| 00:00:01
| 3 | 3 |
--------------------------------------------------------------------------------
-----------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
108 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
650 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed SQL> spool off