[20111221]关于11G Virtual columns[补充].txt

接着在做一些测试：

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'都可以使用索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-713699/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-713699/