在生產環境中有一條sql語句的效能極差，在早晨非高峰時段執行抽取資料，平均要花費40分鐘，有時候竟然要跑10個多小時。
sql語句比較長，需要點耐心往下看。我對錶的資料量都做了簡單的說明。
首先拿到sql語句，一看到關聯的表有一長串，而且都是很大的表。如果效能很差，可能和走全表掃描有關，還有可能和多表關聯時，表的查取順序也有一定的關係。

SELECT DISTINCT CA.L9_CONVERGENCE_CODE AS ATB2,
CU.CUST_SUB_TYPE AS ACCOUNT_TYPE,
CST.DESCRIPTION AS ACCOUNT_TYPE_DESC,
SS.PRIM_RESOURCE_VAL AS MSISDN,
CA.BAN AS BAN_KEY,
to_char(MO.MEMO_DATE, 'YYYYMMDD') AS MEMO_DATE,
CU.L9_IDENTIFICATION AS THAI_ID,
SS.SUBSCRIBER_NO AS SUBS_KEY,
SS.DEALER_CODE AS SHOP_CODE,
CD.DESCRIPTION AS SHOP_NAME,
MOT.SHORT_DESC,
REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ;]+', 1, 3) STAFF_ID,
MO.OPERATOR_ID AS USER_ID,
MO.MEMO_SYSTEM_TEXT,
CO2.SOC_NAME AS FIRST_SOCNAME,
CO3.SOC_NAME AS PREVIOUS_SOCNAME,
CO.SOC_NAME AS CURRENT_SOCNAME,
REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ; ]+', 1, 1) NAME,
CO.SOC_DESCRIPTION AS CURRENT_PP_DESC,
CO3.SOC_DESCRIPTION AS PREV_PP_DESC,
CO.SOC_CD AS SOC_CD,
(SELECT sum(BR.AMOUNT)
FROM BL1_RC_RATES BR, CUSTOMER CU, SUBSCRIBER SS
WHERE BR.SERVICE_RECEIVER_ID = SS.SUBSCRIBER_NO
AND BR.RECEIVER_CUSTOMER = SS.CUSTOMER_ID
AND BR.EFFECTIVE_DATE <= BR.EXPIRATION_DATE
AND((SS. SUB_STATUS <> 'C' and SS.
SUB_STATUS <> 'T' and BR.EXPIRATION_DATE is null)
OR (SS. SUB_STATUS = 'C' and
BR.EXPIRATION_DATE like SS.EFFECTIVE_DATE))
AND BR.PP_IND = 'Y'
AND BR.CYCLE_CODE = CU.BILL_CYCLE) AS PP_RATE,
CU.BILL_CYCLE AS CYCLE_CODE,
to_char(NVL(SS.L9_TMV_ACT_DATE, SS.INIT_ACT_DATE),'YYYYMMDD') AS ACTIVATED_DATE,
to_char(CD.EFFECTIVE_DATE, 'YYYYMMDD') AS SHOP_EFFECTIVE_DATE,
CD.EXPIRATION_DATE AS SHOP_EXPIRED_DATE,
CA.L9_COMPANY_CODE AS COMPANY_CODE
FROM SERVICE_DETAILS S, --大分割槽表，千萬級資料量，存放著交易的明細資訊
PRODUCT CO, --產品配置表，大概幾萬條左右
CSM_PAY_CHANNEL CPC, --賬務相關表，百萬級
ACCOUNT CA,  --賬務相關表，百萬級
SUBSCRIBER SS, --使用者相關表，百萬級
CUSTOMER CU, --使用者相關表，百萬級
CUSTOMER_SUB_TYPE CST, --使用者配置表，幾千條資料
CSM_DEALER CD, --產品配置表，大概幾千條左右
SERVICE_DETAILS S2,
PRODUCT CO2,
SERVICE_DETAILS S3,
PRODUCT CO3,
MEMO MO , --交易備註表，資料量過億
MEMO_TYPE MOT, --配置表，資料量幾千
LOGICAL_DATE LO, --時間配置表，資料量1千多
CHARGE_DETAILS CHD --交易表，資料量千萬
WHERE SS.SUBSCRIBER_NO = CHD.AGREEMENT_NO
AND CPC.PYM_CHANNEL_NO = CHD.TARGET_PCN
AND CHD.CHG_SPLIT_TYPE = 'DR'
AND CHD.EXPIRATION_DATE IS NULL
AND S.SOC = CO.SOC_CD
AND CO.SOC_TYPE = 'P'
AND S.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND SS.PRIM_RESOURCE_TP = 'C'
AND CPC.PAYMENT_CATEGORY = 'POST'
AND CA.BAN = CPC.BAN
AND (CA.L9_COMPANY_CODE = 'RF' OR CA.L9_COMPANY_CODE = 'RM' OR
CA.L9_COMPANY_CODE = 'TM')
AND SS.CUSTOMER_ID = CU.CUSTOMER_ID
AND CU.CUST_SUB_TYPE = CST.CUST_SUB_TYPE
AND CU.CUSTOMER_TYPE = CST.CUSTOMER_TYPE
AND SS.DEALER_CODE = CD.DEALER
AND S2.EFFECTIVE_DATE= (SELECT MAX(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1, PRODUCT o1
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND co.soc_cd = sa1.soc
and co.soc_type = 'P'
)
AND S2.AGREEMENT_NO = S.AGREEMENT_NO
AND S2.SOC = CO2.SOC_CD
AND CO2.SOC_TYPE = 'P'
AND S2.EFFECTIVE_DATE = (SELECT MIN(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1, PRODUCT o1
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND co2.soc_cd = sa1.soc
and co.soc_type = 'P'
)
AND S3.AGREEMENT_NO = S.AGREEMENT_NO
AND S3.SOC = CO3.SOC_CD
AND CO3.SOC_TYPE = 'P'
AND S3.EFFECTIVE_DATE =
(SELECT MAX(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1, PRODUCT o1
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND SA1.EFFECTIVE_DATE <
(SELECT MAX(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1, PRODUCT o1
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
and co3.soc_cd = sa1.soc
and co3.soc_type = 'P'
)
and co3.soc_cd = sa1.soc
and o1.soc_type = 'P'
)
AND MO.ENTITY_ID = SS.SUBSCRIBER_NO
AND MO.ENTITY_TYPE_ID = 6
AND MO.MEMO_TYPE_ID = MOT.MEMO_TYPE_ID
AND TRUNC(MO.SYS_CREATION_DATE) = (select TRUNC(LO.LOGICAL_DATE - 1) from lo)
TRUNC(LO.LOGICAL_DATE - 1)
AND LO.EXPIRATION_DATE IS NULL
AND LO.LOGICAL_DATE_TYPE = 'B'
AND LO.EXPIRATION_DATE IS NULL
AND (MOT.SHORT_DESC = 'BCN' OR MOT.SHORT_DESC = 'BCNM' OR
............
  )

sql語句的執行計劃如下，可以看到基本沒有效能可言。在幾個大表上都做了全表掃描，而且連線的消耗極大。慶幸的是這條sql語句貌似已經使用sql profile調優過了，效能才保持在40左右。

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3445667740

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 551 | | 235K (1)| 00:47:03 | | |
| 1 | SORT AGGREGATE | | 1 | 56 | | | | | |
|* 2 | HASH JOIN | | 10G| 541G| 4840K| 305K (13)| 01:01:04 | | |
|* 3 | HASH JOIN | | 76218 | 3944K| 24M| 259K (1)| 00:51:59 | | |
| 4 | PARTITION RANGE ALL | | 601K| 17M| | 248K (1)| 00:49:38 | 1 | 11 |
|* 5 | TABLE ACCESS FULL | RC_RATES | 601K| 17M| | 248K (1)| 00:49:38 | 1 | 11 |
| 6 | TABLE ACCESS FULL | SUBSCRIBER | 1249K| 26M| | 8495 (1)| 00:01:42 | | |
| 7 | TABLE ACCESS FULL | CUSTOMER | 1226K| 3591K| | 7441 (1)| 00:01:30 | | |
| 8 | HASH UNIQUE | | 1 | 551 | | 235K (1)| 00:47:03 | | |
|* 9 | FILTER | | | | | | | | |
| 10 | NESTED LOOPS | | | | | | | | |
| 11 | NESTED LOOPS | | 1 | 551 | | 235K (1)| 00:47:03 | | |
| 12 | NESTED LOOPS | | 5 | 2485 | | 235K (1)| 00:47:03 | | |
| 13 | NESTED LOOPS | | 1 | 476 | | 235K (1)| 00:47:03 | | |
|* 14 | HASH JOIN | | 2 | 930 | | 235K (1)| 00:47:03 | | |
|* 15 | TABLE ACCESS FULL | LOGICAL_DATE | 1 | 18 | | 12 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 209 | 93423 | | 235K (1)| 00:47:02 | | |
| 17 | NESTED LOOPS | | 5 | 1690 | | 234K (1)| 00:46:59 | | |
| 18 | NESTED LOOPS | | 17 | 5406 | | 234K (1)| 00:46:59 | | |
| 19 | NESTED LOOPS | | 2 | 594 | | 234K (1)| 00:46:59 | | |
| 20 | NESTED LOOPS | | 3 | 858 | | 234K (1)| 00:46:59 | | |
| 21 | NESTED LOOPS | | 3 | 807 | | 234K (1)| 00:46:59 | | |
| 22 | NESTED LOOPS | | 8 | 1992 | | 234K (1)| 00:46:59 | | |
|* 23 | HASH JOIN | | 30 | 5850 | 187M| 234K (1)| 00:46:59 | | |
|* 24 | HASH JOIN | | 1059K| 175M| 33M| 119K (1)| 00:23:56 | | |
| 25 | VIEW | VW_SQ_1 | 1318K| 18M| | 86070 (1)| 00:17:13 | | |
| 26 | HASH GROUP BY | | 1318K| 37M| 50M| 86070 (1)| 00:17:13 | | |
|* 27 | HASH JOIN | | 1318K| 37M| | 75316 (1)| 00:15:04 | | |
|* 28 | MAT_VIEW ACCESS FULL | PRODUCT | 666 | 5994 | | 48 (0)| 00:00:01 | | |
| 29 | TABLE ACCESS FULL | SERVICE_DETAILS | 19M| 392M| | 75198 (1)| 00:15:03 | | |
|* 30 | HASH JOIN | | 1059K| 160M| | 23306 (1)| 00:04:40 | | |
| 31 | MAT_VIEW ACCESS FULL | CSM_DEALER | 16895 | 841K| | 40 (0)| 00:00:01 | | |
|* 32 | HASH JOIN | | 1099K| 113M| 71M| 23262 (1)| 00:04:40 | | |
|* 33 | HASH JOIN | | 1079K| 58M| | 7473 (1)| 00:01:30 | | |
| 34 | MAT_VIEW ACCESS FULL | CUSTOMER_SUB_TYPE | 59 | 1652 | | 3 (0)| 00:00:01 | | |
| 35 | TABLE ACCESS FULL | CUSTOMER | 1226K| 33M| | 7465 (1)| 00:01:30 | | |
|* 36 | TABLE ACCESS FULL | SUBSCRIBER | 1248K| 60M| | 8534 (1)| 00:01:43 | | |
| 37 | TABLE ACCESS FULL | SERVICE_DETAILS | 19M| 392M| | 75198 (1)| 00:15:03 | | |
|* 38 | MAT_VIEW ACCESS BY INDEX ROWID| PRODUCT | 1 | 54 | | 1 (0)| 00:00:01 | | |
|* 39 | INDEX RANGE SCAN | PRODUCT_1IX | 1 | | | 1 (0)| 00:00:01 | | |
|* 40 | TABLE ACCESS BY INDEX ROWID | CHARGE_DETAILS | 1 | 20 | | 1 (0)| 00:00:01 | | |
|* 41 | INDEX RANGE SCAN | CHARGE_DETAILS_1IX | 7 | | | 1 (0)| 00:00:01 | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | CSM_PAY_CHANNEL | 1 | 17 | | 1 (0)| 00:00:01 | | |
|* 43 | INDEX UNIQUE SCAN | CSM_PAY_CHANNEL_PK | 1 | | | 1 (0)| 00:00:01 | | |
|* 44 | TABLE ACCESS BY INDEX ROWID | CSM_ACCOUNT | 1 | 11 | | 1 (0)| 00:00:01 | | |
|* 45 | INDEX UNIQUE SCAN | CSM_ACCOUNT_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 46 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 8 | 168 | | 1 (0)| 00:00:01 | | |
|* 47 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 8 | | | 1 (0)| 00:00:01 | | |
|* 48 | MAT_VIEW ACCESS BY INDEX ROWID | PRODUCT | 1 | 20 | | 1 (0)| 00:00:01 | | |
|* 49 | INDEX RANGE SCAN | PRODUCT_1IX | 1 | | | 1 (0)| 00:00:01 | | |
| 50 | PARTITION RANGE ALL | | 42 | 4578 | | 57 (0)| 00:00:01 | 1 | 289 |
| 51 | TABLE ACCESS BY LOCAL INDEX ROWID | MEMO | 42 | 4578 | | 57 (0)| 00:00:01 | 1 | 289 |
|* 52 | INDEX RANGE SCAN | MEMO_1IX | 1 | | | 57 (0)| 00:00:01 | 1 | 289 |
|* 53 | MAT_VIEW ACCESS BY INDEX ROWID | MEMO_TYPE | 1 | 11 | | 1 (0)| 00:00:01 | | |
|* 54 | INDEX UNIQUE SCAN | MEMO_TYPE_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 55 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 8 | 168 | | 1 (0)| 00:00:01 | | |
|* 56 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 8 | | | 1 (0)| 00:00:01 | | |
|* 57 | INDEX RANGE SCAN | PRODUCT_1IX | 1 | | | 1 (0)| 00:00:01 | | |
|* 58 | MAT_VIEW ACCESS BY INDEX ROWID | PRODUCT | 1 | 54 | | 1 (0)| 00:00:01 | | |
| 59 | SORT AGGREGATE | | 1 | 30 | | | | | |
| 60 | NESTED LOOPS | | | | | | | | |
| 61 | NESTED LOOPS | | 2 | 60 | | 4 (0)| 00:00:01 | | |
| 62 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 8 | 168 | | 1 (0)| 00:00:01 | | |
|* 63 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 8 | | | 1 (0)| 00:00:01 | | |
|* 64 | INDEX RANGE SCAN | PRODUCT_1IX | 1 | | | 1 (0)| 00:00:01 | | |
|* 65 | MAT_VIEW ACCESS BY INDEX ROWID | PRODUCT | 1 | 9 | | 1 (0)| 00:00:01 | | |
| 66 | SORT AGGREGATE | | 1 | 30 | | | | | |
|* 67 | FILTER | | | | | | | | |
| 68 | NESTED LOOPS | | | | | | | | |
| 69 | NESTED LOOPS | | 2 | 60 | | 4 (0)| 00:00:01 | | |
| 70 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 8 | 168 | | 1 (0)| 00:00:01 | | |
|* 71 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 8 | | | 1 (0)| 00:00:01 | | |
|* 72 | INDEX RANGE SCAN | PRODUCT_1IX | 1 | | | 1 (0)| 00:00:01 | | |
|* 73 | MAT_VIEW ACCESS BY INDEX ROWID | PRODUCT | 1 | 9 | | 1 (0)| 00:00:01 | | |
| 74 | SORT AGGREGATE | | 1 | 30 | | | | | |
| 75 | NESTED LOOPS | | | | | | | | |
| 76 | NESTED LOOPS | | 2 | 60 | | 4 (0)| 00:00:01 | | |
| 77 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 8 | 168 | | 1 (0)| 00:00:01 | | |
|* 78 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 8 | | | 1 (0)| 00:00:01 | | |
|* 79 | INDEX RANGE SCAN | PRODUCT_1IX | 1 | | | 1 (0)| 00:00:01 | | |
|* 80 | MAT_VIEW ACCESS BY INDEX ROWID | PRODUCT | 1 | 9 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("BR"."CYCLE_CODE"="CU"."BILL_CYCLE")
3 - access("BR"."SERVICE_RECEIVER_ID"="SS"."SUBSCRIBER_NO" AND "BR"."RECEIVER_CUSTOMER"="SS"."CUSTOMER_ID")
filter("BR"."EXPIRATION_DATE" IS NULL AND "SS"."SUB_STATUS"<>'C' AND "SS"."SUB_STATUS"<>'T' OR "SS"."SUB_STATUS"='C' AND
INTERNAL_FUNCTION("BR"."EXPIRATION_DATE") LIKE INTERNAL_FUNCTION("SS"."EFFECTIVE_DATE"))
5 - filter("BR"."EFFECTIVE_DATE"<="BR"."EXPIRATION_DATE" AND "BR"."PP_IND"='Y')
9 - filter("S2"."EFFECTIVE_DATE"= (SELECT MIN("SA1"."EFFECTIVE_DATE") FROM "PRDREFSSCB"."PRODUCT"
"O1","PRDAPPO"."SERVICE_DETAILS" "SA1" WHERE "SA1"."AGREEMENT_NO"=:B1 AND "O1"."SOC_CD"="SA1"."SOC" AND "O1"."SOC_TYPE"='P') AND
"S3"."EFFECTIVE_DATE"= (SELECT MAX("SA1"."EFFECTIVE_DATE") FROM "PRDREFSSCB"."PRODUCT" "O1","PRDAPPO"."SERVICE_DETAILS" "SA1"
WHERE "SA1"."EFFECTIVE_DATE"< (SELECT MAX("SA1"."EFFECTIVE_DATE") FROM "PRDREFSSCB"."PRODUCT" "O1","PRDAPPO"."SERVICE_DETAILS"
"SA1" WHERE "SA1"."AGREEMENT_NO"=:B2 AND "O1"."SOC_CD"="SA1"."SOC" AND "O1"."SOC_TYPE"='P') AND "SA1"."AGREEMENT_NO"=:B3 AND
"O1"."SOC_CD"="SA1"."SOC" AND "O1"."SOC_TYPE"='P'))
14 - access(TRUNC(INTERNAL_FUNCTION("MO"."SYS_CREATION_DATE"))=TRUNC(INTERNAL_FUNCTION("LO"."LOGICAL_DATE")-1))
15 - filter("LO"."EXPIRATION_DATE" IS NULL AND "LO"."LOGICAL_DATE_TYPE"='B')
23 - access("S"."AGREEMENT_NO"="SS"."SUBSCRIBER_NO" AND "S"."EFFECTIVE_DATE"="MAX(SA1.EFFECTIVE_DATE)")
24 - access("ITEM_1"="SS"."SUBSCRIBER_NO")
27 - access("O1"."SOC_CD"="SA1"."SOC")
28 - filter("O1"."SOC_TYPE"='P')
30 - access("SS"."DEALER_CODE"="CD"."DEALER")
32 - access("SS"."CUSTOMER_ID"="CU"."CUSTOMER_ID")
33 - access("CU"."CUST_SUB_TYPE"="CST"."CUST_SUB_TYPE" AND "CU"."CUSTOMER_TYPE"="CST"."CUSTOMER_TYPE")
36 - filter("SS"."PRIM_RESOURCE_TP"='C')
38 - filter("CO"."SOC_TYPE"='P')
39 - access("S"."SOC"="CO"."SOC_CD")
40 - filter("CHD"."CHG_SPLIT_TYPE"='DR')
41 - access("S"."AGREEMENT_NO"="CHD"."AGREEMENT_NO" AND "CHD"."EXPIRATION_DATE" IS NULL)
42 - filter("CPC"."PAYMENT_CATEGORY"='POST')
43 - access("CPC"."PYM_CHANNEL_NO"="CHD"."TARGET_PCN")
44 - filter("CA"."L9_COMPANY_CODE"='RF' OR "CA"."L9_COMPANY_CODE"='RM' OR "CA"."L9_COMPANY_CODE"='TM')
45 - access("CA"."BAN"="CPC"."BAN")
47 - access("S2"."AGREEMENT_NO"="S"."AGREEMENT_NO")
48 - filter("CO2"."SOC_TYPE"='P')
49 - access("S2"."SOC"="CO2"."SOC_CD")
52 - access(SYS_OP_DESCEND("ENTITY_ID")=SYS_OP_DESCEND("SS"."SUBSCRIBER_NO") AND "MO"."ENTITY_TYPE_ID"=6)
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("ENTITY_ID"))="SS"."SUBSCRIBER_NO")
53 - filter("MOT"."SHORT_DESC"='BCN' OR "MOT"."SHORT_DESC"='BCNM' OR "MOT"."SHORT_DESC"='BCNR' OR "MOT"."SHORT_DESC"='C11' OR
。。。。。。。。')
54 - access("MO"."MEMO_TYPE_ID"="MOT"."MEMO_TYPE_ID")
56 - access("S3"."AGREEMENT_NO"="S"."AGREEMENT_NO")
57 - access("S3"."SOC"="CO3"."SOC_CD")
58 - filter("CO3"."SOC_TYPE"='P')
63 - access("SA1"."AGREEMENT_NO"=:B1)
64 - access("O1"."SOC_CD"="SA1"."SOC")
65 - filter("O1"."SOC_TYPE"='P')
67 - filter("SA1"."EFFECTIVE_DATE"< (SELECT MAX("SA1"."EFFECTIVE_DATE") FROM "PRDREFSSCB"."PRODUCT"
"O1","PRDAPPO"."SERVICE_DETAILS" "SA1" WHERE "SA1"."AGREEMENT_NO"=:B1 AND "O1"."SOC_CD"="SA1"."SOC" AND "O1"."SOC_TYPE"='P'))
71 - access("SA1"."AGREEMENT_NO"=:B1)
72 - access("O1"."SOC_CD"="SA1"."SOC")
73 - filter("O1"."SOC_TYPE"='P')
78 - access("SA1"."AGREEMENT_NO"=:B1)
79 - access("O1"."SOC_CD"="SA1"."SOC")
80 - filter("O1"."SOC_TYPE"='P')

Note
-----
- SQL profile "SYS_SQLPROF_0141a69ce4f40002" used for this statement

得到了執行計劃，和資料的情況。下面需要做的工作有以下幾步：
--查詢效能瓶頸，
根據反饋，查取的資料其實並不錯，可能在幾千條以內的樣子。但是有很多的查詢條件過濾。

如果有些大表走了索引，但是join的消耗很大，很可能就是表的查詢順序不當導致的。
有些情況下使用全表掃描的代價要比使用索引要低。
像這個例子，排查後，logical_date表中雖然有上千條記錄，但是實際上使用的只有一條記錄。
memo這個表是最大的表，由上億條記錄，走了索引。但是join的效率很差，根據排查,memo這個表是這個查詢的關鍵，需要根據時間來得到前一天的資料變化。

如果根據時間來過濾，可以過濾到絕大多數的資料。
上一條記錄過濾後只剩下 74811 rows selected.
如果關聯配置表memo_type查詢的資料就會一下子減少到1713條左右，這是對於效能極大的提升和關鍵。

但是問題就來了，如果按照時間來查詢，這個大表上沒有和時間相關的欄位，查詢走全表掃描會很長，大概在5分鐘左右。

--without parallel
74811 rows selected.
Elapsed: 00:03:23.10
這個時候如果只能走全表掃描，但是想使得速度能夠提升，可以考慮並行，加入並行後，查詢速度控制在了一分鐘以內。

--add table mo1_memo_type, with parllel 8
1713 rows selected.

--加上配置表的過濾條件，查取的資料更少了，速度也有了提升。
Elapsed: 00:00:41.85

如果是的Memo表作為首發，表的執行計劃就有了很大的不同，關聯時間時，會不停的去和Logical_date表做關聯，其實Logical_date表裡只需要一條記錄，檢視執行計劃卻走了笛卡爾積連線。

--去除笛卡爾積連線
-去除笛卡爾積連線可以考慮採用with的句式，把資料先快取起來，作為後續的查詢，就避免了反覆全表掃描的消耗。

--簡化sql
可以看到sql語句中存在著很多重複的過濾條件，需要考慮在不改變業務的情況下保證語句的簡單易讀。

--減少/去除全表掃描
嘗試減少或者去除全表掃描，保證效率。

--子查詢最大程度過濾結果集
可以考慮使用一些儘可能過濾較多資料的子查詢來提高效率。

--觀察執行計劃中表的查取順序。

明天繼續更新更多的細節。

生產環境大型sql語句調優實戰第一篇（一）

相關文章