How Indexing Can Slash Oracle Query Time from Minutes to Seconds

SQL optimization often overlooked; complex queries cause performance issues. This article presents a real‑world Oracle SQL case involving three tables (PAT_INPAT_ORDER_INFO, PAT_INPAT_ORDER_COST, DICT_ZL_PRO) and demonstrates how full‑table scans on PAT_INPAT_ORDER_COST dominate the cost.

Using AWR reports and execution plans (Fig 1, Fig 2) the high cost of Id 10 (full scan) is identified. The selectivity of the FY_STATUS column is poor, providing little filtering.

By analyzing column statistics (Fig 3) and selectivity (Fig 4), the article shows that creating an index on the SFXM_ID column of PAT_INPAT_ORDER_COST can turn the query from a 24‑minute operation into a 1‑second execution.

After creating the index, the new execution plan (shown below) confirms the expected cost reduction.

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> create index IDX_SFXM_ID on PAT_INPAT_ORDER_COST (SFXM_ID);

Thus, simple index creation is a crucial and effective technique for SQL performance tuning when application changes are not feasible.