接着在做一些测试：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> select SYS_NC00009$ ,SYS_NC00010$, a.* from emp a where lower(ename)='smith';
SYS_NC0000 SYS_NC0001 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
smith SMITH 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 988m890j54r0d, child number 0
-------------------------------------
select SYS_NC00009$ ,SYS_NC00010$, a.* from emp a where lower(ename)='smith'
Plan hash value: 87375588
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 2 | INDEX RANGE SCAN | IF_ENAME | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SYS_NC00009$"='smith')
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
25 rows selected.
SQL> select SYS_NC00009$ ,SYS_NC00010$, a.* from emp a where SYS_NC00009$='smith';
SYS_NC0000 SYS_NC0001 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
smith SMITH 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 559qyttr4pujx, child number 0
-------------------------------------
select SYS_NC00009$ ,SYS_NC00010$, a.* from emp a where SYS_NC00009$='smith'
Plan hash value: 87375588
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 2 | INDEX RANGE SCAN | IF_ENAME | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."SYS_NC00009$"='smith')
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
25 rows selected.
--可以发现使用SYS_NC00009$='smith'以及lower(ename)='smith'都可以使用索引。
