虚拟列创建索引

针对where子句中upper(name)类似的子句，除了创建函数索引外，11G中还可以使用虚拟列和索引相结合的方法。
1、虚拟列和虚拟索引
[code]SQL> select count(*) from t_test;

COUNT(*)
----------
11879

SQL> desc t_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)

SQL> alter table t_test add(up_name generated always as (UPPER(OBJECT_NAME)) virtual);

Table altered.

SQL> desc t_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
UP_NAME VARCHAR2(128)

SQL> set auto trace traceonly
SQL> set lines 150
SQL> select object_name from t_test where upper(object_name)='T_TEST';


Execution Plan
----------------------------------------------------------
Plan hash value: 2796558804

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 119 | 10115 | 47 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_TEST | 119 | 10115 | 47 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T_TEST"."UP_NAME"='T_TEST')



SQL> create index ind_obj_up_name on t_test(up_name);

Index created.

SQL> select object_name from t_test where upper(object_name)='T_TEST';


Execution Plan
----------------------------------------------------------
Plan hash value: 1293050481

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 119 | 10115 | 21 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 119 | 10115 | 21 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJ_UP_NAME | 48 | | 1 (0)| 00:00:01 |

SQL> select * from t_test where up_name=upper('aa');

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1293050481

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 119 | 10472 | 21 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 119 | 10472 | 21 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJ_UP_NAME | 48 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
[/code]
2、函数索引
[code]SQL> create index ind_obj_name on t_test(UPPER(OBJECT_NAME));
create index ind_obj_name on t_test(UPPER(OBJECT_NAME))
*
ERROR at line 1:
ORA-54018: A virtual column exists for this expression

SQL> alter table t_test drop (up_name);

Table altered.

SQL> create index ind_obj_name on t_test(UPPER(OBJECT_NAME));

Index created.

SQL> select object_name from t_test where upper(object_name)='T_TEST';


Execution Plan
----------------------------------------------------------
Plan hash value: 3052505044

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 119 | 10115 | 21 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 119 | 10115 | 21 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJ_NAME | 48 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
[/code]