数据库：19.12
系统：rhel 8.4
实验目标：测试函数索引
SQL> select count(*) from emp;
COUNT(*)
----------
20
SQL> create index idx_emp_lastname on emp(last_name);
Index created.
SQL> select * from emp where last_name='ABEL';
no rows selected
SQL> explain plan
2 for
3 select * from emp where last_name='ABEL';
Explained.
SQL> set line 200
SQL> r
1* SELECT * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 4100358275
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_LASTNAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='ABEL')
14 rows selected.
说明：根据执行计划，可以看到此处select走的是索引 index range scan.
现在last_name字段前添加upper函数，再观察查询是否走索引.
SQL> select * from emp where upper(last_name)='ABEL';
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-12 SA_REP 11000 .3 149 80
SQL> explain plan
2 for
3 select * from emp where upper(last_name)='ABEL';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 67 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("LAST_NAME")='ABEL')
13 rows selected.
说明：换成upper(last_name)后，执行计划就是全表扫描.
此处因为表不大，所以体验不是很明显.但生产环境中，遇到非常大的表，如果不走索引，会显得非常慢.
那么如果解决这个问题呢？
解决方案：给upper(last_name)创建索引,这种索引叫做函数索引.
实际演示：
SQL> create index fdx_emp_lastname on emp(UPPER("LAST_NAME"));
Index created.
SQL> explain plan
2 for
3 select * from emp where upper(last_name)='ABEL';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2870390459
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 67 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FDX_EMP_LASTNAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LAST_NAME")='ABEL')
14 rows selected.
结论：此处可以看到，添加完函数索引后，执行计划中查询为索引范围扫描.
