各位大虾们,小弟有个问题不解,望各位给点帮助我在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 '%')这是为什么呢? 很困惑啊! 望大虾赐教……!!!
谢谢!
Plan hash value: 939785991
Plan hash value: 477241886
这表示两个执行计划不同。
http://www.cnblogs.com/tearelish/archive/2008/08/19/1271737.html