一次union all 的优化
-- 客户生产环境反应一个job跑不出结果，我在应用程序log中找到了大量类似的语句
select count(*)
from (select LOAN_ID,
CUSTOMER_REF,
PROD_TYPE,
SITE_CODE,
COMPANY_CODE,
LOANASSET_TYPE,
LOANASSET_ID,
LOANASSET_STATUS,
LOAN_REF,
LOAN_PROD_CODE,
LOAN_CCY,
LOAN_AMT,
LOAN_OUTS_AMT,
LOAN_EFFECTIVEDATE,
LOAN_MATURITYDATE,
LOAN_STATUS,
OBLIGOR_CODE,
CUSTOMER_CODE,
UPDATE_TIME
from ((select l.LOAN_ID,
o.ASSET_NAME as CUSTOMER_REF,
o.PROD_TYPE as PROD_TYPE,
l.SITE_CODE,
l.COMPANY_CODE,
l.LOANASSET_TYPE,
l.LOANASSET_ID,
l.LOANASSET_STATUS,
l.LOAN_REF,
l.LOAN_PROD_CODE,
l.LOAN_CCY,
l.LOAN_AMT,
l.LOAN_OUTS_AMT,
l.LOAN_EFFECTIVEDATE,
l.LOAN_MATURITYDATE,
l.LOAN_STATUS,
l.OBLIGOR_CODE,
l.CUSTOMER_CODE,
l.UPDATE_TIME
from TPOI_LOAN l, TPOI_PO_ASSET o
where l.Loanasset_Type = 'O' and L.Loanasset_Id = o.po_asset_id)
union all (select l.LOAN_ID,
a.ASSET_NAME as CUSTOMER_REF,
a.PROD_TYPE as PROD_TYPE,
l.SITE_CODE,
l.COMPANY_CODE,
l.LOANASSET_TYPE,
l.LOANASSET_ID,
l.LOANASSET_STATUS,
l.LOAN_REF,
l.LOAN_PROD_CODE,
l.LOAN_CCY,
l.LOAN_AMT,
l.LOAN_OUTS_AMT,
l.LOAN_EFFECTIVEDATE,
l.LOAN_MATURITYDATE,
l.LOAN_STATUS,
l.OBLIGOR_CODE,
l.CUSTOMER_CODE,
l.UPDATE_TIME
from TPOI_LOAN l, TPOI_ASSET a
where l.Loanasset_Type = 'A' and L.Loanasset_Id = a.ASSET_ID)
union all (select l.LOAN_ID,
p.POOL_NAME as CUSTOMER_REF,
'' as PROD_TYPE,
l.SITE_CODE,
l.COMPANY_CODE,
l.LOANASSET_TYPE,
l.LOANASSET_ID,
l.LOANASSET_STATUS,
l.LOAN_REF,
l.LOAN_PROD_CODE,
l.LOAN_CCY,
l.LOAN_AMT,
l.LOAN_OUTS_AMT,
l.LOAN_EFFECTIVEDATE,
l.LOAN_MATURITYDATE,
l.LOAN_STATUS,
l.OBLIGOR_CODE,
l.CUSTOMER_CODE,
l.UPDATE_TIME
from TPOI_LOAN l, TPOI_POOL p
where l.Loanasset_Type = 'P' and L.Loanasset_Id = p.POOL_ID)) t
where (SITE_CODE = 'BANK' or COMPANY_CODE = 'SELLERWANG' ) XT10
Plan hash value: 4089048245
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 23 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 642 | | 23 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | 335 | 9045 | 11 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | TPOI_LOAN | 335 | 7705 | 11 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | TPOI_PO_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 306 | 8262 | 11 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | TPOI_LOAN | 306 | 7038 | 11 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | TPOI_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 1 | 36 | 1 (0)| 00:00:01 |
| 12 | INDEX FULL SCAN | TPOI_POOL_AK2 | 1 | 13 | 0 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | TPOI_LOAN_IDX1 | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| TPOI_LOAN | 1 | 23 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("L"."LOANASSET_TYPE"='O' AND ("L"."SITE_CODE"='TCBANK' OR
"L"."COMPANY_CODE"='SELLERWANG'))
6 - access("L"."LOANASSET_ID"="O"."PO_ASSET_ID")
8 - filter("L"."LOANASSET_TYPE"='A' AND ("L"."SITE_CODE"='TCBANK' OR
"L"."COMPANY_CODE"='SELLERWANG'))
9 - access("L"."LOANASSET_ID"="A"."ASSET_ID")
13 - access("L"."LOANASSET_TYPE"='P' AND "L"."LOANASSET_ID"="P"."POOL_ID")
14 - filter("L"."SITE_CODE"='TCBANK' OR "L"."COMPANY_CODE"='SELLERWANG')
-- 上面的执行是在测试环境中的结果，和开发人员沟通，以上sql可能在同一时间执行几千次，看到这个sql太TM坑爹了，于是我做了下面的改写
SELECT COUNT(*)
FROM (SELECT l.loan_id,
t.customer_ref,
t.prod_type,
l.site_code,
l.company_code,
l.loanasset_type,
l.loanasset_id,
l.loanasset_status,
l.loan_ref,
l.loan_prod_code,
l.loan_ccy,
l.loan_amt,
l.loan_outs_amt,
l.loan_effectivedate,
l.loan_maturitydate,
l.loan_status,
l.obligor_code,
l.customer_code,
l.update_time
FROM tpoi_loan l
INNER JOIN (SELECT po_asset_id,
asset_name AS customer_ref,
prod_type AS prod_type,
'O' AS loanasset_type
FROM tpoi_po_asset o
UNION ALL
SELECT asset_id AS po_asset_id,
a.asset_name AS customer_ref,
a.prod_type AS prod_type,
'A' AS loanasset_type
FROM tpoi_asset a
UNION ALL
SELECT pool_id AS po_asset_id,
pool_name AS customer_ref,
'' AS prod_type,
'P' AS loanasset_type
FROM tpoi_pool p) t
ON t.po_asset_id = l.loanasset_id
AND t.loanasset_type = l.loanasset_type
WHERE l.loanasset_type IN ('O', 'A', 'P')
AND (l.site_code = 'BANK' OR l.company_code = 'SELLERWANG')) xt100
Plan hash value: 973488319
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | NESTED LOOPS | | 215 | 6450 | 11 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TPOI_LOAN | 652 | 14996 | 11 (0)| 00:00:01 |
|* 4 | VIEW | | 1 | 7 | 0 (0)| 00:00:01 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
|* 6 | INDEX UNIQUE SCAN | TPOI_PO_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | TPOI_ASSET_PK | 1 | 4 | 0 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | TPOI_POOL_AK2 | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("L"."LOANASSET_TYPE"='A' OR "L"."LOANASSET_TYPE"='O' OR
"L"."LOANASSET_TYPE"='P') AND ("L"."SITE_CODE"='TCBANK' OR
"L"."COMPANY_CODE"='SELLERWANG'))
4 - filter("T"."LOANASSET_TYPE"="L"."LOANASSET_TYPE")
6 - access("PO_ASSET_ID"="L"."LOANASSET_ID")
7 - access("ASSET_ID"="L"."LOANASSET_ID")
8 - access("POOL_ID"="L"."LOANASSET_ID")转载于:https://blog.51cto.com/5073392/1340894