在用户的一次例行巡检中，发现了一条sql语句的CPU利用率很高，这条语句在查询中使用了to_char函数将日期转为字符串，比如下面这样的形式，
select col1 from tablename where col2=XXXX and to_char(col3 , 'YYYY-MM-DD') = to_char(sysdate, 'YYYY-MM-DD')
对这样的语句进行优化的第一步是对where条件里各列的选择性进行分析，选择性强的列加上索引，如果列组合起来的选择性更强，则创建复合索引。这条语句由于对日期列使用了函数运算，如果需要在col3上创建索引，oracle数据库在执行这条语句时，普通的b-tree索引是无法使用的，这时需要创建函数索引或者是在表上加一个虚拟列，在此虚拟列上创建索引。比如下面这样的表
SQL> desc BENCHMARKSQL.BMSQL_HISTORY; Name Null? Type ----------------------------------------- -------- ---------------------------- H_C_ID NUMBER(38) H_C_D_ID NUMBER(38) H_C_W_ID NUMBER(38) H_D_ID NUMBER(38) H_W_ID NUMBER(38) H_DATE TIMESTAMP(6) H_AMOUNT NUMBER(6,2) H_DATA VARCHAR2(24)
运行下面的sql语句
select h_data from BENCHMARKSQL.BMSQL_HISTORY where H_C_ID=2758 and to_char(H_DATE,'YYYY-MM-DD')='2025-05-29';
1 没有任何索引的情况下的执行效率
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 342 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BMSQL_HISTORY | 1 | 29 | 342 (1)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 - filter("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-M M-DD')='2024-12-10')
oracle在执行这条语句时选择了全表扫描，执行的成本是342，全表扫描过滤后返回了表中的一行数据。
2 在其中的一列上加上索引
2.1 在H_C_ID列上创建索引
create index idx_id on BENCHMARKSQL.BMSQL_HISTORY(H_C_ID);
创建索引后对表进行分析，更新一下表的统计信息
analyze table BENCHMARKSQL.BMSQL_HISTORY compute statistics;
现在这条语句的执行计划如下
----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 52 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY | 1 | 29 | 52 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ | 50 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- 1 - filter(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2024-12-10') 2 - access("H_C_ID"=2758)
oracle在执行语句时选择了刚才创建的索引，在索引上执行了范围扫描，回表后根据H_DATE列进行了过滤，范围扫描的成本很低，这条语句的执行成本主要在id为1的操作，这个操作根据范围扫描得到的ROWID查找表中的数据，然后过滤。这里的数据库版本是Oracle 23AI，Oracle还是对这个操作进行了优化，这从操作后面的BATCHED 可以看出。回表的成本是比较高的，不做这条语句的总成本是52，比全表扫描时低了不少。
2.2 在H_DATE上创建索引
2.2.1 创建普通索引
create index idx_date on BENCHMARKSQL.BMSQL_HISTORY(H_DATE);
对表进行分析后，执行计划如下：
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 342 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BMSQL_HISTORY | 1 | 29 | 342 (1)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 - filter("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-M M-DD')='2025-05-29')
Oracle在执行这条语句时没有选择索引，而是进行了全表扫描，这时由于sql语句的选择条件对列进行了函数运算的缘故。
2.2.2 创建函数索引
create index idx_date_f on BENCHMARKSQL.BMSQL_HISTORY(to_char(H_DATE,'YYYY-MM-DD'));
执行前面的表分析语句对表进行分析后，语句的执行计划是这样的
Plan hash value: 750191248 -----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 32 | 342 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| BMSQL_HISTORY | 1 | 32 | 342 (1)| 00:00:01 |----------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 - filter("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-M M-DD')='2025-05-29')
Oracle并没有使用刚刚创建的函数索引，仍然选择了全表扫描。问题出在哪里呢？可能想到的是统计信息的问题，可是在创建索引后我们已经执行了表分析命令，更新了统计信息，使用hint强制Oracle使用刚才创建的函数索引，看一下执行计划
Plan hash value: 2550728382 -----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 35 | 352 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY | 1 | 35 | 352 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_DATE_F | 413 | | 4 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 - filter("H_C_ID"=2758) 2 - access(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2025-05-29') 15 rows selected.
这次Oracle数据库使用了刚才创建的函数索引，id为2的操作在创建的函数索引上进行了范围扫描，返回的行数为413行，查询数据表，依据语句中的where条件，确实返回了413行，这说明统计信息时准确的，Oracle因为回表成本过高而没有选择函数索引而选择了全表扫描。
2.2.3 换一条语句试试
刚才的语句数据库由于成本问题而没有使用函数索引，换一条语句试一下：
select count(*) from BENCHMARKSQL.BMSQL_HISTORY where to_char(H_DATE,'YYYY-MM-DD')='2025-05-29';
在有函数索引时，语句的执行计划如下：
--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | INDEX RANGE SCAN| IDX_DATE_F | 413 | 4543 | 4 (0)| 00:00:01 |-------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2025-05 -29')
Oracle执行语句时选择了函数索引，由于这里不用回表，语句的执行成本基本就是范围扫描的成本。
在没有函数索引时，语句的执行计划如下：
------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 | 343 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | TABLE ACCESS FULL| BMSQL_HISTORY | 1511 | 10577 | 343 (1)| 00:00:01 |------------------------------------------------------------------------------------ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - filter(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2025-05-29' )
全表扫描的成本为300多，远高于基于索引的范围扫描。
2.3 使用虚拟列
在创建函数索引时，Oracle会在相应的数据库表上创建一个隐藏列。这个列的信息在DBA_TAB_COLS视图里可以查询到。也可以使用虚拟列来达到和函数索引一样的效果。现在表上增加一个虚拟列，然后在此虚拟列上创建索引。在表上增加虚拟列前，要删除之前创建的列上的函数索引。否则，在增加虚拟列时会报重复的表达式错误，增加虚拟列会失败。在BMSQL_HISTORY表上增加虚拟列的语句如下：
alter table BENCHMARKSQL.BMSQL_HISTORY add (v_h_day as (to_char(H_DATE,'YYYY-MM-DD')) virtual);
增加虚拟列成功后，就可以在这个列上创建索引了。
create index idx_date_f on BENCHMARKSQL.BMSQL_HISTORY(to_char(H_DATE,'YYYY-MM-DD'));
然后再看一下前面的语句的执行计划
SQL> explain plan for select count(*) from BENCHMARKSQL.BMSQL_HISTORY where to_char(H_DATE,'YYYY-MM-DD')='2025-05-29'; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 913241418 ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | INDEX RANGE SCAN| IDX_V_H_DAY | 413 | 4543 | 4 (0)| 00:00:01 |--------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("BMSQL_HISTORY"."V_H_DAY"='2025-05-29') 14 rows selected.
Oracle使用了刚刚创建的索引进行了范围扫描。可以看到，在where条件中执行有对列的函数计算时，Oracle会自动检查相应的虚拟列，不需要我们把where条件改写为相应的虚拟列的形式。
虚拟列和函数索引的实现原理基本上相同的，具体选用哪种方式可以凭个人习惯和喜好决定，它们的实现的效果也相同。
3 创建复合索引
3.1 复合索引里没有函数表达式
从全表扫描的执行计划里，可以看到，在执行了where里面的筛选条件之后，返回的数据只有一行。从这一点可以看出，这两个条件组合起来，可能选出的行更少。如果想要得出具体的结论，需要对查询表，对表的数据的分布有更为准确的了解。在本例中这个表依据这两个条件的数据分布可用下面的语句来查询
select H_C_ID,to_char(H_DATE,'YYYY-MM-DD'),count(*) from BENCHMARKSQL.BMSQL_HISTORY group by H_C_ID,to_char(H_DATE,'YYYY-MM-DD') having count(*)>2;
查询出来的结果没有大于5的，因此，对于这条语句创建复合索引可以达到很好的优化效果
create index idx_id_date on BENCHMARKSQL.BMSQL_HISTORY(H_C_ID,H_DATE);
创建复合索引时，选择性较强的列放在前面，这里H_C_ID列的选择性更强，所以放在前面，对表分析后，执行计划如下：
----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY | 1 | 32 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID_DATE | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- 2 - access("H_C_ID"=2758) filter(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2024-12-10')
创建了复合索引后，语句的执行成本降低到3，这里id为2的操作显示依据H_C_ID列进行了范围扫描，由于h_date列也是复合索引的列，所以这里不用回表就可以依据h_date列进行过滤，减少了需要回表的rowid数量，降低了整个语句的执行成本。
3.2 复合索引里包含函数表达式
上一节的执行计划里，范围扫描依据的表达式是"H_C_ID"=2758，函数表达式条件则作为范围扫描的过滤条件。这里，可以做的更彻底一点，是函数表达式条件也作为范围扫描的依据。为此，创建以下索引
create index idx_func on BENCHMARKSQL.BMSQL_HISTORY(H_C_ID,to_char(H_DATE,'YYYY-MM-DD'));
索引创建后，Oracle并没有选择这个索引进行范围扫描，还是选择了上一节的索引。将上一节创建的索引drop掉后，上面的语句的执行计划如下
----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY | 1 | 32 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_FUNC | 1 | | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- 2 - access("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2024-12-10 ') 15 rows selected.
范围扫描的成本反而高于使用前面的索引时，在这个例子中，同时基于两个条件进行范围扫描的执行效率反而不如基于一个条件进行扫描然后进行过滤。想来是第二个条件过滤掉的数据比较少，达不到减少扫描数据的目的，反而影响了扫描效率。
