案例：如何改写语句，让优化器使用上索引？

--1  客户的环境，发现语句耗时54秒，
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
WHERE (FIsSuccess = 1 AND (  (    ( TRUNC( CAST(SYSDATE AS DATE), 'MI') - TRUNC( CAST(FDATETIME AS DATE), 'MI')) * 24 * 60) < 60  )  )

Global Information
Status    :    DONE (ALL ROWS)
Instance ID    :    2
Session    :    CLOUDBUS (557:9505)
SQL ID    :    g6cc195k3wjyc
SQL Execution ID    :    33554566
Execution Started    :    03/21/2022 17:10:51
First Refresh Time    :    03/21/2022 17:10:55
Last Refresh Time    :    03/21/2022 17:11:41
Duration    :    50s
Module/Action    :    w3wp.exe/-
Service    :    k3cloud
Program    :    w3wp.exe
Fetch Calls    :    1

Global Stats   Elapsed          Time(s)    Cpu   Time(s)IO   Waits(s)    Cluster Waits(s) FetchCalls    Buffer   Gets    Read   Reqs    ReadBytes
               54               10          34      11            1             162K             22283            877MB



--2  查看表的索引情况，发现索引 IDX_BAS_YUNZHIJIALOG，有两个索引键，第1个为主键字段 FID，
select a.table_name, a.index_name, a.index_type, a.uniqueness, a.visibility, a.last_analyzed, a.status, a.tablespace_name,
       b.column_name, b.column_position pos, c.constraint_name, c.constraint_type
from user_indexes a, user_ind_columns b, user_constraints c
where a.table_name in (upper(trim('&tname')))
  and a.index_name=b.index_name
  and a.index_name=c.index_name(+)
  7  order by a.table_name, a.index_name, b.column_position;
Enter value for tname: T_BAS_YUNZHIJIALOG
old   4: where a.table_name in (upper(trim('&tname')))
new   4: where a.table_name in (upper(trim('T_BAS_YUNZHIJIALOG')))

TABLE_NAME                     INDEX_NAME                     INDEX_TYPE UNIQUENESS         VISIBILITY         LAST_ANALYZED       STATUS TABLESPACE_NAME         COLUMN_NAME
------------------------------ ------------------------------ ---------- ------------------ ------------------ ------------------- ------ ------------------------- -------------------------
POS CONSTRAINT_NAME           CO
--- ------------------------- --
T_BAS_YUNZHIJIALOG             IDX_BAS_YUNZHIJIALOG           NORMAL     NONUNIQUE          VISIBLE            2022-03-21 01:42:49 VALID  CLOUDBUS_INDEX_TS       FID
  1

T_BAS_YUNZHIJIALOG             IDX_BAS_YUNZHIJIALOG           NORMAL     NONUNIQUE          VISIBLE            2022-03-21 01:42:49 VALID  CLOUDBUS_INDEX_TS       FDATETIME
  2

T_BAS_YUNZHIJIALOG             PK_T_BAS_YUNZHIJIALOG          NORMAL     UNIQUE             VISIBLE            2022-03-21 01:42:49 VALID  CLOUDBUS_INDEX_TS       FID
  1 PK_T_BAS_YUNZHIJIALOG     P


Elapsed: 00:00:10.44
CLOUDBUS@k3cloud1>


--3   改写SQL，发现语句走跳跃索引，由于索引IDX_BAS_YUNZHIJIALOG 的第一个字段为主键，具有唯一性，识别度很高，这种情况走跳跃索引效率也不好，
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
WHERE (FIsSuccess = '1' AND (  FDATETIME > TRUNC ( CAST(SYSDATE AS DATE), 'MI') - 1/24 )  )

CLOUDBUS@k3cloud1>explain plan for
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
  3  WHERE (FIsSuccess = '1' AND (  FDATETIME > TRUNC ( CAST(SYSDATE AS DATE), 'MI') - 1/24 )  )
  4  /

Explained.

Elapsed: 00:00:00.01
SQL>select * from display;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3000645360

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |  1652 |   339   (0)| 00:00:05 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_BAS_YUNZHIJIALOG   |     1 |  1652 |   339   (0)| 00:00:05 |
|*  2 |   INDEX SKIP SCAN           | IDX_BAS_YUNZHIJIALOG |     2 |       |   338   (0)| 00:00:05 |      -- SKIP 跳跃索引
----------------------------------------------------------------------------------------------------

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

   1 - filter("FISSUCCESS"='1')
   2 - access("FDATETIME">TRUNC(CAST(SYSDATE@! AS
              DATE),'fmmi')-.0416666666666666666666666666666666666667)
       filter("FDATETIME">TRUNC(CAST(SYSDATE@! AS
              DATE),'fmmi')-.0416666666666666666666666666666666666667)

18 rows selected.

Elapsed: 00:00:00.02
SQL>


--4  测试字段和同一边的其他列做计算操作时的执行计划，语句走全表扫描
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
WHERE (FIsSuccess = '1' AND ( 1/24 > TRUNC ( CAST(SYSDATE AS DATE), 'MI') -  FDATETIME   )  )

SQL>explain plan for
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
  3  WHERE (FIsSuccess = '1' AND ( 1/24 > TRUNC ( CAST(SYSDATE AS DATE), 'MI') -  FDATETIME   )  )   --FDATETIME和SYSDATE做计算，
  4  /

Explained.

Elapsed: 00:00:00.10
SQL>select * from display;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2875309593

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |   525 |   846K| 44073   (1)| 00:08:49 |
|*  1 |  TABLE ACCESS FULL| T_BAS_YUNZHIJIALOG |   525 |   846K| 44073   (1)| 00:08:49 |  -- 不走索引，走全表扫描
----------------------------------------------------------------------------------------

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

   1 - filter("FISSUCCESS"='1' AND TRUNC(CAST(SYSDATE@! AS
              DATE),'fmmi')-"FDATETIME"<.0416666666666666666666666666666666666667)

14 rows selected.

Elapsed: 00:00:00.07
SQL>



--5  删除索引
SQL>drop index IDX_BAS_YUNZHIJIALOG;

Index dropped.

Elapsed: 00:00:00.34

--6  重建索引，去掉主键字段，只保留时间字段，
SQL>create index IDX_BAS_YUNZHIJIALOG on T_BAS_YUNZHIJIALOG (FDATETIME) online;

Index created.

Elapsed: 00:00:00.92


--7   再度解析测试，这次，索引走范围扫描，而不是之前的跳跃扫描，COST从之前跳跃索引的339，降低到3。
SQL>explain plan for
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
  3  WHERE (FIsSuccess = '1' AND (  FDATETIME > TRUNC ( CAST(SYSDATE AS DATE), 'MI') - 1/24 )  )
  4  /

Explained.

Elapsed: 00:00:00.01
SQL>select * from display;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1281773738

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |  1652 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_BAS_YUNZHIJIALOG   |     1 |  1652 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_BAS_YUNZHIJIALOG |     2 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("FISSUCCESS"='1')
   2 - access("FDATETIME">TRUNC(CAST(SYSDATE@! AS
              DATE),'fmmi')-.0416666666666666666666666666666666666667)

16 rows selected.

Elapsed: 00:00:00.02


--8  再度解析，让字段FDATETIME和SYSDATE字段做计算，发现语句还是选择走全表扫描，
SQL>explain plan for
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
  3  WHERE (FIsSuccess = '1' AND ( 1/24 > TRUNC ( CAST(SYSDATE AS DATE), 'MI') -  FDATETIME   )  )
  4  /

Explained.

Elapsed: 00:00:00.01
SQL>select * from display;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2875309593

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |   525 |   846K| 44073   (1)| 00:08:49 |
|*  1 |  TABLE ACCESS FULL| T_BAS_YUNZHIJIALOG |   525 |   846K| 44073   (1)| 00:08:49 |
----------------------------------------------------------------------------------------

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

   1 - filter("FISSUCCESS"='1' AND TRUNC(CAST(SYSDATE@! AS
              DATE),'fmmi')-"FDATETIME"<.0416666666666666666666666666666666666667)

14 rows selected.

Elapsed: 00:00:00.01
SQL>

--9  测试FDATETIME 字段和其他列做计算时的执行计划，走全表扫描
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
  2  WHERE (FIsSuccess = '1' AND ( 1/24 > TRUNC ( CAST(SYSDATE AS DATE), 'MI') -  FDATETIME   )  );

no rows selected

Elapsed: 00:00:00.21

Execution Plan
----------------------------------------------------------
Plan hash value: 2875309593

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |   525 |   846K| 44073   (1)| 00:08:49 |
|*  1 |  TABLE ACCESS FULL| T_BAS_YUNZHIJIALOG |   525 |   846K| 44073   (1)| 00:08:49 |
----------------------------------------------------------------------------------------

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

   1 - filter("FISSUCCESS"='1' AND TRUNC(CAST(SYSDATE@! AS
              DATE),'fmmi')-"FDATETIME"<.0416666666666666666666666666666666666667)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     162440  consistent gets
          0  physical reads
          0  redo size
        828  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

--10  测试FDATETIME单独为关系符号一端时的执行计划，走索引范围扫描。
SELECT FID, FMSGSOURCE, FINTERFACETYPE, FDATETIME, FMSGINFO, FIsSuccess FROM T_BAS_YUNZHIJIALOG
  2  WHERE (FIsSuccess = '1' AND (  FDATETIME > TRUNC ( CAST(SYSDATE AS DATE), 'MI') - 1/24 )  )
  3  /

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1281773738

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |  1652 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_BAS_YUNZHIJIALOG   |     1 |  1652 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_BAS_YUNZHIJIALOG |     2 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("FISSUCCESS"='1')
   2 - access("FDATETIME">TRUNC(CAST(SYSDATE@! AS
              DATE),'fmmi')-.0416666666666666666666666666666666666667)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        141  consistent gets
          0  physical reads
          0  redo size
        828  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>


总结：要让语句走索引，WHERE选项里的索引键字段，不能加函数，也不应该和其他表达式做计算，简单点说，就只能“单独”在关系符合 (>,=,<) 的一端，此时，才可能用上索引。