SQL性能优化策略之索引优化方法

SQL> select sum(cggzl) cggzl, sum(qbgzl) qbgzl  from (select case                 when zlxm_mc like '%2ê3?3￡1??ì2é%' then                  gzl                 else                  0               end cggzl,               case                 when zlxm_mc like '%?3±í?÷1ù%' then                  gzl                 else                  0               end qbgzl          from dictmanage.dict_zl_pro   b,               his.pat_inpat_order_info c,               pat_inpat_order_cost     d         where d.sfxm_id = b.zlxm_id           and c.yzjl_id = d.dyzy_yzjl_id           and zlxm_mc like '%2???%'           and c.yz_zxrq >= to_date(sysdate)           and c.yz_zxrq < to_date(sysdate + 1)           and d.fy_status in ('1', '2')           and sfxm_je > 0           and c.yz_zfrq is null           and c.zylsh = :in_zylsh)

SQL> select /*+ NO_MERGE LEADING(a b) */ b.owner, b.table_name, a.column_name, b.num_rows, a.num_distinct Cardinality, ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivity  from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner   and a.table_name = b.table_name   and a.owner = upper('his')   and a.table_name = upper('pat_inpat_order_cost')   and a.column_name = upper('fy_status');

SQL> select count(*), FY_STATUS  from his.pat_inpat_order_cost c group by FY_STATUS;

SQL> select sql_Id, name, datatype_string, last_captured, value_string  from v$sql_bind_capture where sql_id = '18rwad2bgcxfa';

SQL> select count(*)  from his.pat_inpat_order_info c where c.yz_zxrq >= to_date(sysdate)   and c.yz_zxrq < to_date(sysdate + 1)   and c.yz_zfrq is null   and c.zylsh = 72706;

SQL> create index IDX_SFXM_ID on PAT_INPAT_ORDER_COST (SFXM_ID);Plan hash value: 408580053------------------------------------------------------------------------------------------------| Id  | Operation               | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |                      |       |       |    12 (100)|          ||   1 |  SORT AGGREGATE         |                      |     1 |    68 |            |          ||*  2 |   FILTER                |                      |       |       |            |          ||   3 |    NESTED LOOPS         |                      |     1 |    68 |    12   (0)| 00:00:01 ||   4 |     NESTED LOOPS        |                      |     1 |    68 |    12   (0)| 00:00:01 ||   5 |      NESTED LOOPS       |                      |     1 |    39 |    11   (0)| 00:00:01 ||*  6 |       TABLE ACCESS BY GLOBAL INDEX ROWID                                | PAT_INPAT_ORDER_INFO |     1 |    21 |     5   (0)| 00:00:01 ||*  7 |        INDEX RANGE SCAN | YZ_ZXRQ_IDX          |     4 |       |     3   (0)| 00:00:01 ||*  8 |       TABLE ACCESS BY GLOBAL INDEX ROWID                                | PAT_INPAT_ORDER_COST |     6 |   108 |     6   (0)| 00:00:01 ||*  9 |        INDEX RANGE SCAN | IDX_DYZY_YZJL_ID     |     6 |       |     2   (0)| 00:00:01 ||* 10 |      INDEX UNIQUE SCAN  | DICT_ZL_PRO_PK       |     1 |       |     0   (0)|          ||* 11 |     TABLE ACCESS BY INDEX ROWID | DICT_ZL_PRO  |     1 |    29 |     1   (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(TO_DATE(TO_CHAR(SYSDATE@!+1))>TO_DATE(TO_CHAR(SYSDATE@!)))   6 - filter(("C"."ZYLSH"=TO_NUMBER(:IN_ZYLSH) AND "C"."YZ_ZFRQ" IS NULL))   7 - access("C"."YZ_ZXRQ">=TO_DATE(TO_CHAR(SYSDATE@!)) AND "C"."YZ_ZXRQ"<TO_DATE(TO_CHAR       (SYSDATE@!+1)))   8 - filter(("SFXM_JE">0 AND INTERNAL_FUNCTION("D"."FY_STATUS")))   9 - access("C"."YZJL_ID"="D"."DYZY_YZJL_ID")  10 - access("D"."SFXM_ID"="B"."ZLXM_ID")  11 - filter("ZLXM_MC" LIKE '%部位%')