[20220331]为什么不使用索引.txt

--//生产系统一条语句存在性能问题，导致3个表都全表扫描，分析看看。

1.环境：

> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.分析：
> @ sql_id 017n1f8tkgczv
--SQL_ID = 017n1f8tkgczv
select * from xxxxxx_yyy.etc_zy_listDetail where fphm = :FPHM ;

--//语句很简单，xxxxxx_yyy.etc_zy_listDetail是一个视图，fphm表示发票号码，检查索引也存在，但是就是全表扫描。
--//执行计划如下：
Plan hash value: 2654262995
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |        |       |   626K(100)|          |       |       |          |
....
|* 24 |  VIEW                          | ETC_ZY_LISTDETAIL |   2723K|   693M|   626K  (1)| 00:00:25 |       |       |          |
|  25 |   COUNT                        |                   |        |       |            |          |       |       |          |
|* 26 |    HASH JOIN                   |                   |   2723K|   262M| 28376   (1)| 00:00:02 |  1695K|  1695K| 1643K (0)|
|  27 |     TABLE ACCESS FULL          | GY_SFXM           |     60 |   660 |     2   (0)| 00:00:01 |       |       |          |
|* 28 |     HASH JOIN                  |                   |   2723K|   233M| 28366   (1)| 00:00:02 |  1486K|  1486K| 1729K (0)|
|  29 |      TABLE ACCESS FULL         | ZY_ZYJS           |   7211 |   140K|    39   (0)| 00:00:01 |       |       |          |
|  30 |      TABLE ACCESS FULL         | ZY_FYMX_JS        |   2755K|   183M| 28319   (1)| 00:00:02 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(3=:B1)
   3 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
   5 - access("YK_YPML"."YPXH"=:B1)
   6 - filter(3=:B1)
   8 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
  10 - access("YK_YPML"."YPXH"=:B1)
  11 - filter(3=:B1)
  14 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
  16 - access("YK_YPML"."YPXH"=:B1)
  18 - access("T4"."YPSX"="YK_YPML"."YPSX")
  19 - filter(3=:B1)
  21 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
  23 - access("YK_YPML"."YPXH"=:B1)
  24 - filter("FPHM"=:FPHM)
  ~~~~~~~~~~~~~~~~~~~~~~~~~
  26 - access("T2"."FYXM"="T3"."SFXM")
  28 - access("T1"."ZYH"="T2"."ZYH" AND "T1"."JSCS"="T2"."JSCS")
 
--//注意id＝24的操作，为什么放在最后呢，仔细看执行计划还存在一个count在id = 25,为什么呢？
Column Projection Information (identified by operation id):
-----------------------------------------------------------
...
  25 - "T3"."SFMC"[VARCHAR2,20], "T2"."FYXM"[NUMBER,22], "T1"."FPHM"[VARCHAR2,12],
       "T2"."JLXH"[NUMBER,22], "T2"."JGID"[NUMBER,22], "T2"."FYXH"[NUMBER,22],
       "T2"."FYMC"[VARCHAR2,100], "T2"."FYSL"[NUMBER,22], "T2"."FYDJ"[NUMBER,22],
       "T2"."ZJJE"[NUMBER,22], "T2"."ZFJE"[NUMBER,22], "T2"."YPLX"[NUMBER,22],
       "T2"."JLXH"[NUMBER,22], "T2"."JGID"[NUMBER,22], "T2"."FYXM"[NUMBER,22],
       "T2"."FYXH"[NUMBER,22], "T2"."FYMC"[VARCHAR2,100], "T2"."FYSL"[NUMBER,22],
       "T2"."FYDJ"[NUMBER,22], "T2"."ZJJE"[NUMBER,22], "T2"."ZFJE"[NUMBER,22],
       "T2"."YPLX"[NUMBER,22], ROWNUM[8]

--//检查视图定义发现如下：
       ROWNUM AS sortno ,/*序号*/
--//注：Column Projection Information实际上最后ROWNUM[8]，自己很少注意这个细节。
--//昏，整个视图里面存在一个毫无意义的取顺序号rownum的情况，竟然不定义在开始，也没有出现在结尾，而是在中间靠后的某个位置.
--//导致无法使用建立的索引，很简单我写一个包括全部字段仅仅没有sortno的/查询：

select
BUSNO, LISTDETAILNO, CHARGECODE, CHARGENAME, PRESCRIBECODE,
 LISTTYPECODE, LISTTYPENAME, CODE, NAME, FORM,
 SPECIFICATION, UNIT, STD, NUMBER1, AMT,
 SELFAMT, RECEIVABLEAMT, MEDICALCARETYPE, MEDCAREITEMTYPE, MEDREIMBURSERATE,
 REMARK,  CHRGTYPE, FPHM
 from xxxxxx_yyy.etc_zy_listDetail where fphm = :FPHM ;

--//几乎是瞬间完成。
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                         |        |       |  1709 (100)|          |       |       |          |
....
|* 24 |  HASH JOIN                             |                         |    378 | 38178 |     9   (0)| 00:00:01 |  1695K|  1695K| 1635K (0)|
|  25 |   TABLE ACCESS FULL                    | GY_SFXM                 |     60 |   660 |     2   (0)| 00:00:01 |       |       |          |
|  26 |   NESTED LOOPS                         |                         |    378 | 34020 |     7   (0)| 00:00:01 |       |       |          |
|  27 |    NESTED LOOPS                        |                         |    378 | 34020 |     7   (0)| 00:00:01 |       |       |          |
|  28 |     TABLE ACCESS BY INDEX ROWID BATCHED| ZY_ZYJS                 |      1 |    20 |     2   (0)| 00:00:01 |       |       |          |
|* 29 |      INDEX RANGE SCAN                  | I_ZY_ZYJS_FPHM          |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|* 30 |     INDEX RANGE SCAN                   | IDX_ZY_FYMX_JS_ZYH_JSCS |     58 |       |     2   (0)| 00:00:01 |       |       |          |
|  31 |    TABLE ACCESS BY INDEX ROWID         | ZY_FYMX_JS              |    378 | 26460 |     5   (0)| 00:00:01 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
...
  24 - access("T2"."FYXM"="T3"."SFXM")
  29 - access("T1"."FPHM"=:FPHM)
  30 - access("T1"."ZYH"="T2"."ZYH" AND "T1"."JSCS"="T2"."JSCS")

--//看着这样的开发团队，真心很无语，上线前测试没有。
--//他们应该好好感谢oracle ODA,exadata,它们才是真正的先进工作者。