Analysis of unused index columns caused by implicit conversion of timestamp

After the business table of a system is rebuilt , The next day, the user reported that the business system was slow , Check performance data , Discover business SQL The execution efficiency of , In depth analysis found that timestamp Data columns , Index not used on column ; Through the analysis of the implementation plan , It can be found that there is an implicit conversion ; That is to use idtime > systimestamp; when , What is actually carried out is filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("CREATION_DATE"))>TIMESTAMP' 2022-03-14 00:17:35.520486000');

MOS Document Index on a Timestamp Column Cannot be Accessed Properly (Doc ID 1321016.1) Having said this question , The solution given is ： Change the column to "timestamp with time zone", then a function-based index is automatically created when you create index on this column. That is, change the field type of the time column to "timestamp with time zone", This will automatically create a function index column .

We directly create a functional index column manually CREATE INDEX "abc"."abc_OBJECT_MAP_N1_FUNCTION" ON "abc"."abc_OBJECT_MAP" (SYS_EXTRACT_UTC("CREATION_DATE"))  local online;, Then you can use the index ,SQL Efficiency returns to normal .

=====1. Use to_date The sentence of ：
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. Use TIMESTAMP The sentence of ：
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')

########################
 After indexing ：
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  ====>>> Function index 
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. Use to_date The sentence of ：
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. Use TIMESTAMP The sentence of ：
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')