timestamp隐式转换问题导致索引列未使用问题分析

某系统的业务表重建后，次日用户反映业务系统缓慢，检查性能数据，发现业务SQL的执行效率下降，深入分析发现是timestamp数据列，未使用到列上的索引；通过对执行计划的分析，可以发现是发生了隐式转换；即使用idtime > systimestamp;时，实际执行的是filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("CREATION_DATE"))>TIMESTAMP' 2022-03-14 00:17:35.520486000')；

MOS文档上Index on a Timestamp Column Cannot be Accessed Properly (Doc ID 1321016.1)说了这个问题，给出的解决方案是： Change the column to "timestamp with time zone", then a function-based index is automatically created when you create index on this column.即改时间列的字段类型为"timestamp with time zone"，此时这会自动创建一个函数索引列。

我们直接人工创建一个函数索引列CREATE INDEX "abc"."abc_OBJECT_MAP_N1_FUNCTION" ON "abc"."abc_OBJECT_MAP" (SYS_EXTRACT_UTC("CREATION_DATE"))  local online;，之后可以用上索引，SQL效率恢复正常。

=====1.使用to_date的语句：
select count(*) from abc.abc_OBJECT_MAP  where creation_date>to_date('20110312 22:00', 'yyyymmdd hh24:mi')
  COUNT(*)
----------
         7


Execution Plan
----------------------------------------------------------
Plan hash value: 4292575048

------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |    13 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |                   |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ALL  |                   |     7 |    91 |     4   (0)| 00:00:01 |     1 |1048575|
|*  3 |    INDEX FAST FULL SCAN| abc_OBJECT_MAP_N1 |     7 |    91 |     4   (0)| 00:00:01 |     1 |1048575|
------------------------------------------------------------------------------------------------------------

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

   3 - filter("CREATION_DATE">TIMESTAMP' 2011-03-12 22:00:00')

##########
=====2.使用TIMESTAMP的语句：
SQL> select count(*) from abc.abc_OBJECT_MAP  where creation_date>TIMESTAMP'2022-03-14 00:17:35.520486 +00:00';

  COUNT(*)
----------
         4


Execution Plan
----------------------------------------------------------
Plan hash value: 413461469

-------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |     1 |    13 |    38   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE      |                |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|                |     5 |    65 |    38   (0)| 00:00:01 |     1 |1048575|
|*  3 |    TABLE ACCESS FULL | abc_OBJECT_MAP |     5 |    65 |    38   (0)| 00:00:01 |     1 |1048575|
-------------------------------------------------------------------------------------------------------

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

   3 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("CREATION_DATE"))>TIMESTAMP' 2022-03-14
              00:17:35.520486000')

########################
建索引后：
SQL> CREATE INDEX "abc"."abc_OBJECT_MAP_N1_FUNCTION" ON "abc"."abc_OBJECT_MAP" (SYS_EXTRACT_UTC("CREATION_DATE"))  local online;

Index created.

SQL> select index_name,index_type from dba_indexes where table_name='abc_OBJECT_MAP';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
SYS_IL0000074117C00002$$       LOB
abc_OBJECT_MAP_N1              NORMAL
HOM_N2                         NORMAL
abc_OBJECT_MAP_N1_FUNCTION     FUNCTION-BASED NORMAL  ====>>>函数索引
PK_abc_OBJECT_MAP              NORMAL

SQL> select segment_name,PARTITION_NAME ,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name='abc_OBJECT_MAP_N1_FUNCTION';

SEGMENT_NAME                   PARTITION_NAME       SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ -------------------- ------------------ ------------------------------
abc_OBJECT_MAP_N1_FUNCTION     SYS_P605             INDEX PARTITION    USERS
abc_OBJECT_MAP_N1_FUNCTION     SYS_P608             INDEX PARTITION    USERS
abc_OBJECT_MAP_N1_FUNCTION     SYS_P602             INDEX PARTITION    USERS
abc_OBJECT_MAP_N1_FUNCTION     SYS_P584             INDEX PARTITION    USERS
abc_OBJECT_MAP_N1_FUNCTION     SYS_P587             INDEX PARTITION    USERS
abc_OBJECT_MAP_N1_FUNCTION     SYS_P614             INDEX PARTITION    USERS
abc_OBJECT_MAP_N1_FUNCTION     SYS_P611             INDEX PARTITION    USERS

===
=====3.使用to_date的语句：
SQL> select count(*) from abc.abc_OBJECT_MAP  where creation_date>to_date('20110312 22:00', 'yyyymmdd hh24:mi');


  COUNT(*)
----------
         7


Execution Plan
----------------------------------------------------------
Plan hash value: 4292575048

------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |    13 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |                   |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ALL  |                   |     7 |    91 |     4   (0)| 00:00:01 |     1 |1048575|
|*  3 |    INDEX FAST FULL SCAN| abc_OBJECT_MAP_N1 |     7 |    91 |     4   (0)| 00:00:01 |     1 |1048575|
------------------------------------------------------------------------------------------------------------

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

   3 - filter("CREATION_DATE">TIMESTAMP' 2011-03-12 22:00:00')


=====4.使用TIMESTAMP的语句：
SQL> select count(*) from abc.abc_OBJECT_MAP  where creation_date>TIMESTAMP'2022-03-14 00:17:35.520486 +00:00';

  COUNT(*)
----------
         4


Execution Plan
----------------------------------------------------------
Plan hash value: 1155152824

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |     1 |    13 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE      |                            |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|                            |     4 |    52 |     4   (0)| 00:00:01 |     1 |1048575|
|*  3 |    INDEX RANGE SCAN  | abc_OBJECT_MAP_N1_FUNCTION |     4 |    52 |     4   (0)| 00:00:01 |     1 |1048575|
-------------------------------------------------------------------------------------------------------------------

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

   3 - access(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("CREATION_DATE"))>TIMESTAMP' 2022-03-14
              00:17:35.520486000')