关于 INDEX
SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM ME_STRG_EC_IO WHERE (DAYHOUR = 2005093015) AND ((Y_MESSYU >= 35.663 AND Y_MESSYU < 35.664 AND X_MESSYU >= 139.673 AND X_MESSYU < 139.674)) 我建立的游标是CREATE INDEX XXXX ON ME_STRG_EC_IO(DAYHOUR ,Y_MESSYU ,x_MESSYU )
但是 我做如下操作 看不见INDEX
EXPLAIN PLAN FOR
SELECT COUNTVALUE,MINVALUE,MAXVALUE,AVGVALUE FROM
BTS_STRG_EC_IO WHERE DAYHOUR = 2005093015
2 analyze table BTS_STRG_EC_IO compute statistics for table
for all indexes
for all indexed columns;
3 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 结果是
1 Plan hash value: 1052544228
2
3 -----------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -----------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 91 | 17 (0)| 00:00:01 |
7 |* 1 | TABLE ACCESS FULL| ME_STRG_EC_IO | 1 | 91 | 17 (0)| 00:00:01 |
8 -----------------------------------------------------------------------------------
9
10 Predicate Information (identified by operation id):
11 ---------------------------------------------------
12
13 1 - filter("DAYHOUR"=2005093015 AND "Y_MESSYU">=35.663 AND
14 "Y_MESSYU"<=35.664 AND "X_MESSYU">=139.673 AND "X_MESSYU"<=139.674 AND
15 "Y_MESSYU"<>35.664 AND "X_MESSYU"<>139.674)
16
17 Note
18 -----
19 - dynamic sampling used for this statement 看执行计划的这句
Y_MESSYU"<>35.664 AND "X_MESSYU"<>139.674)
解释后的语句的谓语里面有 <> ,开区间，这样是不会走索引
为什么在你环境中和解释成这样，我现在看不出来
你强制使用索引试验一下
/*+ INDEX (ME_STRG_EC_IO XXXX )*/ 额.... 其实我把条件改为 BTWEEN AND 就不需要 <> 了
还有就是对于INDEX来说 MEASURE_DATE 有很多重复数据 所以我在想是不是不走INDEX的原因
或者解决办法把 MEASURE_DATE 放到 X MESYU 和 Y MESYU 后面 或者删除掉 MEASURE_DATE 就是 DAYHOUR
页:
[1]