本帖最后由 278676125 于 2013-10-25 21:50 编辑
建测试表:
create table emp_test as select to_char(empno,'00009999') eno,ename from emp t;
加主键:
ALTER TABLE emp_test ADD PRIMARY KEY (eno);
收集统计信息:
begin
dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEST');
end;
/
这样子是走索引的:
21:39:38 SCOTT@orcl> select * from emp_test where eno>='7900';
已用时间:00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1122673765
--------------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |16 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 |16 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN
| SYS_C0017539 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENO">='7900')复制代码
但是我们应用上的SQL是这样的: select * from emp_test where eno>=LPAD('7900',LENGTH(eno),'0');
这样就不走索引了
21:39:47 SCOTT@orcl> select * from emp_test where eno>=LPAD('7900',LENGTH(eno),'0');
已用时间:00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3124080142
------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT|
| 1 |16 | 3 (0)| 00:00:01 |
|*1 |TABLE ACCESS FULL| EMP_TEST | 1 |16 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENO">=LPAD('7900',LENGTH("ENO"),'0'))复制代码
我试过建LENGTH(eno)的函数索引,但是发现也不走,想建LPAD函数索引,但是不知道咋建,求大神支招。
|
