原始語句如下：
select BIZ_NO,
OUT_BIZ_NO,
ORDER_NO,
USER_ID,
user_name,
CARD_NO,
ACCOUNT_NO,
ACCOUNT_BIZ_NO,
USER_TYPE,
EVENT,
GID,
TRADE_AMOUNT,
DATA_BIZ_TYPE,
DIRECTION,
DATA_STATUS,
CURRENCY,
ACCOUNT_DAY,
COUNTER_PARTY_USER_ID,
counter_party_user_name,
COUNTER_PARTY_CARD_NO,
COUNTER_PARTY_ACCOUNT_NO,
product_code,
category,
origin,
TRADE_TYPE,
TRADE_BIZ_PRODUCT_CODE,
MERCHANT_USER_ID,
MERCHANT_CARD_NO,
MERCHANT_ACC_NO,
MERCHANT_ORDER_BIZ_NO,
INLET,
TRADE_FROM,
BIZ_IDENTITY,
bank_Code,
BANK_ACCOUNT_NO,
BANK_ACCOUNT_NAME,
CREATE_TIME,
FINISHED_TIME,
TRADE_NAME,
TRADE_MEMO,
BANK_CNAPS_NO,
BANK_NAME,
SETTLE_BIZ_NO,
SETTLE_CHANNEL_API,
settle_time,
relation_no,
retention_bank_no,
retention_bank_code,
refund_reason,
EXTEND_CONTEXT,
RAW_ADD_TIME,
RAW_UPDATE_TIME,
MEMO,
relation_time
from silverb.USER_TRANS_LOG
where 1 = 1
AND event = 'CHARGE'
AND ORDER_NO in
(select AA.OUT_BIZ_NO
from (SELECT a.*, ROWNUM RN
from (select OUT_BIZ_NO
from (select t.event || t.direction as EVENTINOUT,
t.*
from silverb.USER_TRANS_LOG t) X
where 1 = 1
and X.DATA_BIZ_TYPE in
('FAST_PAY',
'TRANSFER',
'REMITTANCE',
'CARDTOCARD',
'ESCROWTRADE',
'EBANK',
'OFFLINE',
'DEDUCT',
'QUICK',
'CERTIFY',
'VERIFY',
'WITHDRAW_SINGLE',
'WITHDRAW_FORCEING',
'WITHDRAW_OFFLINE',
'DEPOSITBACK')
and X.eventinout in ('DEPOSITBACKIN',
'WITHDRAW_FLUSHES_WRITEOFFOUT',
'DEPOSIT_FLUSHES_FILLACCOUNTSIN',
'TRADE_SHARE_REFUNDOUT',
'TRADE_REFUNDOUT',
'WITHDRAW_FLUSHES_FILLACCOUNTSOUT',
'TRADE_FLUSHES_WRITEOFFOUT',
'WITHDRAWOUT',
'DISHONOUROUT',
'DEPOSITIN',
'TRADE_FLUSHES_FILLACCOUNTSOUT',
'DEPOSITBACK_FLUSHES_WRITEOFFIN',
'DEPOSIT_FLUSHES_WRITEOFFIN',
'DEPOSITBACK_FLUSHES_FILLACCOUNTSIN',
'TRADEOUT')
and X.event not in ('TRADE_SHARE', 'CHARGE')
and X.MERCHANT_USER_ID = '20140730020001144381'
and X.create_time >=
to_date('2015-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss')
and X.create_time <
to_date('2015-02-18 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
order by create_time desc) a
where ROWNUM <= 100) AA
where RN >= 1)
and DIRECTION = 'OUT'
and create_time >=
to_date('2015-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and create_time <
to_date('2015-03-18 23:59:59', 'yyyy-mm-dd hh24:mi:ss')

執行計劃
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 470 (100)| | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 8 | 4152 | 470 (7)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 8 | 336 | 253 (9)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 8 | 120 | | | | |
|* 5 | VIEW | | 8 | 120 | 253 (9)| 00:00:01 | | |
|* 6 | COUNT STOPKEY | | | | | | | |
| 7 | VIEW | | 8 | 152 | 253 (9)| 00:00:01 | | |
|* 8 | SORT ORDER BY STOPKEY | | 8 | 552 | 253 (9)| 00:00:01 | | |
| 9 | PARTITION RANGE ITERATOR | | 8 | 552 | 252 (9)| 00:00:01 | 34 | 33 |
| 10 | PARTITION HASH ALL | | 8 | 552 | 252 (9)| 00:00:01 | 4 | 1 |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| USER_TRANS_LOG | 8 | 552 | 252 (9)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | ME_L_IND | 1770 | | 27 (12)| 00:00:01 | 136 | 12
| 13 | PARTITION RANGE ITERATOR | | 1 | | 25 (4)| 00:00:01 | 33 | 35
| 14 | PARTITION HASH ALL | | 1 | | 25 (4)| 00:00:01 | 1 | 4 |
|* 15 | INDEX RANGE SCAN | ORDER_L_IND | 1 | | 25 (4)| 00:00:01 | 129 |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | USER_TRANS_LOG | 1 | 477 | 27 (4)| 00:00:0
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5DA710D3
3 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
4 - SEL$683B0107
5 - SEL$3 / AA@SEL$2
6 - SEL$3
7 - SEL$7286615E / A@SEL$3
8 - SEL$7286615E
11 - SEL$7286615E / T@SEL$5
12 - SEL$7286615E / T@SEL$5
15 - SEL$5DA710D3 / USER_TRANS_LOG@SEL$1
16 - SEL$5DA710D3 / USER_TRANS_LOG@SEL$1

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

5 - filter("RN">=1)
6 - filter(ROWNUM<=100)
8 - filter(ROWNUM<=100)
11 - filter(("T"."EVENT"<>'CHARGE' AND ("T"."EVENT"||"T"."DIRECTION"='DEPOSITBACKIN' OR
"T"."EVENT"||"T"."DIRECTION"='WITHDRAW_FLUSHES_WRITEOFFOUT' OR
"T"."EVENT"||"T"."DIRECTION"='DEPOSIT_FLUSHES_FILLACCOUNTSIN' OR "T"."EVENT"||"T"."DIRECTION"
OR "T"."EVENT"||"T"."DIRECTION"='TRADE_REFUNDOUT' OR "T"."EVENT"||"T"."DIRECTION"='WITHDRAW_F
OR "T"."EVENT"||"T"."DIRECTION"='TRADE_FLUSHES_WRITEOFFOUT' OR "T"."EVENT"||"T"."DIRECTION"='
"T"."EVENT"||"T"."DIRECTION"='DISHONOUROUT' OR "T"."EVENT"||"T"."DIRECTION"='DEPOSITIN' OR
"T"."EVENT"||"T"."DIRECTION"='TRADE_FLUSHES_FILLACCOUNTSOUT' OR
"T"."EVENT"||"T"."DIRECTION"='DEPOSITBACK_FLUSHES_WRITEOFFIN' OR
"T"."EVENT"||"T"."DIRECTION"='DEPOSIT_FLUSHES_WRITEOFFIN' OR
"T"."EVENT"||"T"."DIRECTION"='DEPOSITBACK_FLUSHES_FILLACCOUNTSIN' OR "T"."EVENT"||"T"."DIRECT
INTERNAL_FUNCTION("T"."DATA_BIZ_TYPE") AND "T"."CREATE_TIME" "T"."EVENT"<>'TRADE_SHARE'))
12 - access("T"."MERCHANT_USER_ID"='20140730020001144381')
15 - access("ORDER_NO"="OUT_BIZ_NO")
16 - filter(("EVENT"='CHARGE' AND "DIRECTION"='OUT' AND "CREATE_TIME"


分解：
1、T@SEL$5(別名為T的查詢塊5)
select t.event || t.direction as EVENTINOUT,t.* from silverb.USER_TRANS_LOG t
2、X@SEL$4(別名為X的查詢塊4)雖然執行計劃中沒有出現這個查詢塊，但是在10053TRACE是有的，其實他是做的檢視合併的結果
select OUT_BIZ_NO from (子查詢塊T) where X.DATA_BIZ_TYPE in('FAST_PAY',...)
and X.eventinout in ('DEPOSITBACKIN',...)
and X.event not in ('TRADE_SHARE', 'CHARGE')
and X.MERCHANT_USER_ID = '20140730020001144381'
and X.create_time >=to_date('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and X.create_time order by create_time desc
3、EL$7286615E 此查詢塊名字為資料庫自動生成，表示進行簡單檢視合併後的查詢塊
10053 中明確標示
Registered qb: SEL$7286615E 0x5924a390 (VIEW MERGE SEL$4; SEL$5)
4、A@SEL$3(別名為A的查詢塊3)
SELECT a.*, ROWNUM RN from (子查詢塊X) a where ROWNUM <= 100
5、AA@SEL$2(別名為AA的查詢塊2)
select AA.OUT_BIZ_NO from (子查詢塊A) AA where RN >= 1
6、SEL$683B0107 此查詢塊，名字為資料庫自動生成，表示進行子複雜查詢展開的查詢塊
這裡應該是使用了DISTINCT(AA.OUT_BIZ_NO) 來替換查詢塊AA@SEL$2中的AA.OUT_BIZ_NO
這一點從執行計劃中的HASH UNIQUE也得到了驗證
10053 中明確標示
SU: Transform an ANY subquery to semi-join or distinct.
SEL$683B0107 0x58e85660 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$2)
7、USER_TRANS_LOG@SEL$1(表名為A的查詢塊1)
select BIZ_NO,... from silverb.USER_TRANS_LOG
where 1 = 1 AND event = 'CHARGE' AND ORDER_NO in
(子查詢塊A)
and DIRECTION = 'OUT'
and create_time >= to_date('2015-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and create_time < to_date('2015-03-18 23:59:59', 'yyyy-mm-dd hh24:mi:ss')

8、SEL$5DA710D3 此查詢塊，名字為資料庫自動生成，表示進行子查詢展開的查詢塊，此處將
子查詢ORDER_NO in (子查詢塊A)轉換NEST LOOP的方式進行
10053 中明確標示
SEL$5DA710D3 0x591f6a80 (SUBQUERY UNNEST SEL$1; SEL$2) [FINAL]

所以本語句的執行順序為
1、查詢塊T@SEL$5和X@SEL$4先進行檢視合併，生成查詢塊EL$7286615E，對應執行計劃的8-12步，並且A@SEL$3分頁查詢的謂詞ROWNUM <= 100
推入到此檢視中，形成了SORT ORDER BY STOPKEY，形成一個VIEW
2、查詢塊@SEL$3不能進行檢視合併，因為包含了ROWNUM，對應執行計劃的6-7步，使用到謂詞ROWNUM <= 100，形成第二個VIEW
3、查詢塊@SEL$2透過COMPLEX UNNEST形成了SEL$683B0107，對應執行計劃的4-5步，使用謂詞"RN">=1和distinct(AA.OUT_BIZ_NO)，形成第三個VIEW（VW_NSO_1）
4、查詢塊USER_TRANS_LOG@SEL$1與SEL$683B0107最終進行子查詢展開得到了最終的查詢塊SEL$5DA710D3，進行了2次NETS LOOP 進行完成了所有的步驟，
注意這裡並沒有完全的展開，而是把子查詢當做一個檢視或進行了處理，因為檢視本生是一個複雜的子查詢得出，而是轉換為JION的方式。

這些操作實際上在Outline Data中也有體現
MERGE(@"SEL$5")
UNNEST(@"SEL$2")

關於查詢塊的說明：
ORACLE查詢塊是語義上完整的查詢語句，原則上是從內到外的型別$數字的命名方式，數字從1向上遞增（來源：基於ORACLE的最佳化法則）
關於10053中關於查詢轉換的說明：
10053中的第一部分就列出執行了哪些查詢轉換其型別也在其中有說明如下
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition

一條SQL語句查詢塊分解及查詢轉換

相關文章