create table tb_test
(
a date,
b date
);
create index indx1 on tb_test (a);
create index indx1 on tb_test (b);select *
from
(
select a - b a_minus_b
from tb_test
)
where a_minus_b between 0 and 24这个查询没有使用到index,请问大家如何才能使用到index,谢谢!
(
a date,
b date
);
create index indx1 on tb_test (a);
create index indx1 on tb_test (b);select *
from
(
select a - b a_minus_b
from tb_test
)
where a_minus_b between 0 and 24这个查询没有使用到index,请问大家如何才能使用到index,谢谢!
SQL> select *
2 from
3 (
4 select a - b a_minus_b
5 from tb_test
6 )
7 where a_minus_b between 0 and 24 ;未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1135667662---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TB_TEST | 1 | 18 | 1 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | INDX12 | 1 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("A"-"B">=0 AND "A"-"B"<=24)Note
-----
- dynamic sampling used for this statementSQL>
from
((select a from tb_test)- (select b from tb_test)) a_minus_b
where a_minus_b between 0 and 24提示:对索引列进行运算会使索引失效,采取这种方法可以屏蔽不想用的索引,使其失效