创建语句;
create table GPS_CZC_MONTH
(
id VARCHAR2(32) not null,
fpassno VARCHAR2(32) not null,
fpasscolor VARCHAR2(32),
flongitude NUMBER(14,8),
flatitude NUMBER(14,8),
fspeed NUMBER(14,4),
fbearing NUMBER(14,6),
fstatus VARCHAR2(255),
falarm VARCHAR2(255),
fstatusdate DATE,
gps_time DATE,
load_status NUMBER,
cllx VARCHAR2(20),
sys_time DATE,
rq NUMBER,
xs NUMBER,
geometry MDSYS.SDO_GEOMETRY
)
tablespace TBS_GPS
partition by range (GPS_TIME)
interval(NUMTODSINTERVAL(1,'day'))
(
PARTITION PART_20130101 VALUES LESS THAN (to_date('2013-01-02','YYYY-MM-DD'))
)
查询语句:
select * from GPS_CZC_MONTH t WHERE GPS_TIME >to_date('2013-10-16 9:19:33','YYYY-MM-DD HH24:MI:SS')
AND GPS_TIME
AND FPASSNO='浙ET8287'
执行计划
----------------------------------------------------------
Plan hash value: 2581807694
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71 | 10224 | 8318 (1)| 00:01:40 | | |
| 1 | PARTITION RANGE SINGLE| | 71 | 10224 | 8318 (1)| 00:01:40 | 289 | 289 |
|* 2 | TABLE ACCESS FULL | GPS_CZC_MONTH | 71 | 10224 | 8318 (1)| 00:01:40 | 289 | 289 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FPASSNO"='浙ET8287' AND "GPS_TIME"
hh24:mi:ss') AND "GPS_TIME">TO_DATE(' 2013-10-16 09:19:33', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
30102 consistent gets
0 physical reads
0 redo size
14783 bytes sent via SQL*Net to client
909 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72 rows processed
这是为什么执行分区不过分区呢???