各位大虾们,小弟有个问题不解,望各位给点帮助我在Table_name 表中建立索引
create index I_VEHICLEPASS_COM2 on Table_name (PASSTIME,crosslsh,PLATEINFO)
create bitmap index I_VEHICLEPASS_COM1 on Table_name (crosslsh);select count(1)
from TABLE_NAME v
where passTime >=
to_timestamp('2011-5-1 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
and passTime <=
to_timestamp('2011-5-30 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
and CROSSLSH in (21)
and plateInfo like '%'
1 Plan hash value: 939785991
2
3 -----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 23 | 26558 (2)| 00:05:19 |
7 | 1 | SORT AGGREGATE | | 1 | 23 | | |
8 |* 2 | FILTER | | | | | |
9 |* 3 | INDEX RANGE SCAN| I_VEHICLEPASS_COM2 | 293K| 6588K| 26558 (2)| 00:05:19 |
10 -----------------------------------------------------------------------------------------
11
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 2 - filter(TO_TIMESTAMP('2011-5-1 00:00:00',:B1)<=TO_TIMESTAMP('2011-5-30
16 23:59:59',:B2))
17 3 - access("PASSTIME">=TO_TIMESTAMP('2011-5-1 00:00:00',:B1) AND
18 "CROSSLSH"=21 AND "PASSTIME"<=TO_TIMESTAMP('2011-5-30 23:59:59',:B2))
19 filter("CROSSLSH"=21 AND "PLATEINFO" LIKE '%')
这里用来索引
但是我再加一个条件 就不用索引,而是进行全部扫描
select count(1)
from TABLE_NAME v
where passTime >=
to_timestamp('2011-5-1 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
and passTime <=
to_timestamp('2011-5-30 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
and CROSSLSH in (21)
and plateInfo like '%'
and drivewayNumber = '1'
1 Plan hash value: 477241886
2
3 -------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 26 | 205K (3)| 00:41:06 |
7 | 1 | SORT AGGREGATE | | 1 | 26 | | |
8 |* 2 | FILTER | | | | | |
9 |* 3 | TABLE ACCESS FULL| TABLE_NAME | 24445 | 620K| 205K (3)| 00:41:06 |
10 -------------------------------------------------------------------------------------------
11
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 2 - filter(TO_TIMESTAMP('2011-5-1 00:00:00',:B1)<=TO_TIMESTAMP('2011-5-30
16 23:59:59',:B2))
17 3 - filter("CROSSLSH"=21 AND "DRIVEWAYNUMBER"=1 AND
18 "PASSTIME"<=TO_TIMESTAMP('2011-5-30 23:59:59',:B1) AND
19 "PASSTIME">=TO_TIMESTAMP('2011-5-1 00:00:00',:B2) AND "PLATEINFO" LIKE '%')这是为什么呢? 很困惑啊! 望大虾赐教……!!!
谢谢!
create index I_VEHICLEPASS_COM2 on Table_name (PASSTIME,crosslsh,PLATEINFO)
create bitmap index I_VEHICLEPASS_COM1 on Table_name (crosslsh);select count(1)
from TABLE_NAME v
where passTime >=
to_timestamp('2011-5-1 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
and passTime <=
to_timestamp('2011-5-30 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
and CROSSLSH in (21)
and plateInfo like '%'
1 Plan hash value: 939785991
2
3 -----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 23 | 26558 (2)| 00:05:19 |
7 | 1 | SORT AGGREGATE | | 1 | 23 | | |
8 |* 2 | FILTER | | | | | |
9 |* 3 | INDEX RANGE SCAN| I_VEHICLEPASS_COM2 | 293K| 6588K| 26558 (2)| 00:05:19 |
10 -----------------------------------------------------------------------------------------
11
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 2 - filter(TO_TIMESTAMP('2011-5-1 00:00:00',:B1)<=TO_TIMESTAMP('2011-5-30
16 23:59:59',:B2))
17 3 - access("PASSTIME">=TO_TIMESTAMP('2011-5-1 00:00:00',:B1) AND
18 "CROSSLSH"=21 AND "PASSTIME"<=TO_TIMESTAMP('2011-5-30 23:59:59',:B2))
19 filter("CROSSLSH"=21 AND "PLATEINFO" LIKE '%')
这里用来索引
但是我再加一个条件 就不用索引,而是进行全部扫描
select count(1)
from TABLE_NAME v
where passTime >=
to_timestamp('2011-5-1 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
and passTime <=
to_timestamp('2011-5-30 23:59:59', 'yyyy-mm-dd HH24:MI:SS')
and CROSSLSH in (21)
and plateInfo like '%'
and drivewayNumber = '1'
1 Plan hash value: 477241886
2
3 -------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 26 | 205K (3)| 00:41:06 |
7 | 1 | SORT AGGREGATE | | 1 | 26 | | |
8 |* 2 | FILTER | | | | | |
9 |* 3 | TABLE ACCESS FULL| TABLE_NAME | 24445 | 620K| 205K (3)| 00:41:06 |
10 -------------------------------------------------------------------------------------------
11
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 2 - filter(TO_TIMESTAMP('2011-5-1 00:00:00',:B1)<=TO_TIMESTAMP('2011-5-30
16 23:59:59',:B2))
17 3 - filter("CROSSLSH"=21 AND "DRIVEWAYNUMBER"=1 AND
18 "PASSTIME"<=TO_TIMESTAMP('2011-5-30 23:59:59',:B1) AND
19 "PASSTIME">=TO_TIMESTAMP('2011-5-1 00:00:00',:B2) AND "PLATEINFO" LIKE '%')这是为什么呢? 很困惑啊! 望大虾赐教……!!!
谢谢!
解决方案 »
- split partition 用法
- 分区表能建成索引组织表嘛?
- oracle执行存储过程怎么能返回多条记录?
- oracle8i的 DBA studio 无法连接上本机的oracle10g
- PL SQL中如何使用变量作为修改语句中的列名?
- 怎么最快速的查找一个表里有,但另一个表里没有的数据?
- 我的os是2000,安装了oracle817后,新建sid但是不能用dba管理
- 初学者请教:为何select * from salgrade where max(losal);这里不能用分组函数?
- 怎样验证sql语句的正确性(只是语法检查,不执行)
- xp客户端通过程式无法访问oracle数据库,DNS解析和tnsping也可以通
- oracle查询参数问题
- sql语句转换 存储过程 问题
Plan hash value: 939785991
Plan hash value: 477241886
这表示两个执行计划不同。
http://www.cnblogs.com/tearelish/archive/2008/08/19/1271737.html