优化案例

今天记录点啥呢，记个今天的工作优化案例吧

运维反馈语句执行慢，卡的出不来结果，具体sql如下：

select a.billingdate ,a.prodid ,a.branchid, b.abc ,a.invbalqty , b.outboundqty,b.outboundqty*3 qhx,a.ioid,a.ioname
from tgsc a,(
select a.prodid,a.outboundqty,nvl(b.sellabc ,'C') abc,a.branchid,a.ioid
from tgss a
left join tgsabc b  on a.branchid=b.branchid and a.prodid=b.prodid and a.ioid=b.ioid
where  exists (select 1 from tgys where prodid=a.prodid and branchid=a.branchid and ioid=a.ioid)
and a.branchid='FFD1L') b
where a.branchid=b.branchid and a.prodid=b.prodid and a.branchid='FFD1L' and a.ioid=b.ioid
    and b.abc like 'A%'
    and a.invbalqty<b.outboundqty*3;

执行计划

 Plan Hash Value  : 1926411222 

---------------------------------------------------------------------------------------------------
| Id   | Operation                        | Name                 | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                 |                      |    1 |   295 | 4676 | 00:00:57 |
|  * 1 |   FILTER                         |                      |      |       |      |          |
|    2 |    NESTED LOOPS OUTER            |                      |    1 |   295 | 4676 | 00:00:57 |
|    3 |     NESTED LOOPS                 |                      |    1 |   269 | 4676 | 00:00:57 |
|    4 |      MERGE JOIN CARTESIAN        |                      |    1 |   213 | 4676 | 00:00:57 |
|  * 5 |       TABLE ACCESS FULL          | TGSC                 |    1 |   167 | 4676 | 00:00:57 |
|    6 |       BUFFER SORT                |                      |    1 |    46 |    0 | 00:00:01 |
|    7 |        SORT UNIQUE               |                      |    1 |    46 |    0 | 00:00:01 |
|  * 8 |         INDEX RANGE SCAN         | IX_TGYS_BPI          |    1 |    46 |    0 | 00:00:01 |
|  * 9 |      TABLE ACCESS BY INDEX ROWID | TGSS                 |    1 |    56 |    0 | 00:00:01 |
| * 10 |       INDEX RANGE SCAN           | IX_TGSS_BPI          |    1 |       |    0 | 00:00:01 |
|   11 |     TABLE ACCESS BY INDEX ROWID  | TGSABC               |    1 |    26 |    0 | 00:00:01 |
| * 12 |      INDEX UNIQUE SCAN           | INDEX_UNIQUE         |    1 |       |    0 | 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(NVL("B"."SELLABC",'C') LIKE 'A%')
* 5 - filter("A"."BRANCHID"='FFD1L')
* 8 - access("BRANCHID"='FFD1L')
* 9 - filter("A"."INVBALQTY"<"A"."OUTBOUNDQTY"*3)
* 10 - access("BRANCHID"="A"."BRANCHID" AND "PRODID"="A"."PRODID" AND "IOID"="A"."IOID")
* 10 - filter("A"."BRANCHID"='FFD1L' AND "A"."BRANCHID"="A"."BRANCHID" AND "A"."PRODID"="A"."PRODID" AND "A"."IOID"="A"."IOID")
* 12 - access("B"."BRANCHID"(+)='FFD1L' AND "A"."PRODID"="B"."PRODID"(+) AND "A"."IOID"="B"."IOID"(+))

可以看出主要是TGSC表扫描全表导致，数据量大概两百多万，branchid和ioid去重后是个位数

根据执行计划和上述表的情况，尝试给出解决方案

create index index_lscs1 on (prodid,branchid,ioid) nologging;

优化后

 Plan Hash Value  : 4150093441 

-----------------------------------------------------------------------------------------------------
| Id   | Operation                          | Name                 | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                   |                      |    1 |   295 |   31 | 00:00:01 |
|    1 |   NESTED LOOPS                     |                      |    1 |   295 |   31 | 00:00:01 |
|    2 |    NESTED LOOPS                    |                      |    1 |   295 |   31 | 00:00:01 |
|  * 3 |     FILTER                         |                      |      |       |      |          |
|    4 |      NESTED LOOPS OUTER            |                      |    1 |   128 |    2 | 00:00:01 |
|    5 |       NESTED LOOPS                 |                      |    1 |   102 |    2 | 00:00:01 |
|    6 |        SORT UNIQUE                 |                      |    1 |    46 |    1 | 00:00:01 |
|  * 7 |         INDEX RANGE SCAN           | IX_TGYS_BPI          |    1 |    46 |    1 | 00:00:01 |
|    8 |        TABLE ACCESS BY INDEX ROWID | TGSS                 |    1 |    56 |    0 | 00:00:01 |
|  * 9 |         INDEX RANGE SCAN           | IX_TGSS_BPI          |    1 |       |    0 | 00:00:01 |
|   10 |       TABLE ACCESS BY INDEX ROWID  | TGSABC               |    1 |    26 |    0 | 00:00:01 |
| * 11 |        INDEX UNIQUE SCAN           | INDEX_UNIQUE         |    1 |       |    0 | 00:00:01 |
| * 12 |     INDEX RANGE SCAN               | INDEX_LSCS1          |    1 |       |    2 | 00:00:01 |
| * 13 |    TABLE ACCESS BY INDEX ROWID     | TGSC                 |    1 |   167 |   29 | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter(NVL("B"."SELLABC",'C') LIKE 'A%')
* 7 - access("BRANCHID"='FFD1L')
* 9 - access("BRANCHID"="A"."BRANCHID" AND "PRODID"="A"."PRODID" AND "IOID"="A"."IOID")
* 9 - filter("A"."BRANCHID"='FFD1L')
* 11 - access("B"."BRANCHID"(+)='FFD1L' AND "A"."PRODID"="B"."PRODID"(+) AND "A"."IOID"="B"."IOID"(+))
* 12 - access("A"."PRODID"="A"."PRODID" AND "A"."BRANCHID"='FFD1L' AND "A"."IOID"="A"."IOID")
* 12 - filter("A"."BRANCHID"="A"."BRANCHID")
* 13 - filter("A"."INVBALQTY"<"A"."OUTBOUNDQTY"*3)

结果：0.18秒执行完成