原始语句如下：
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 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 - SEL5DA710D33−SEL683B0107 / VW_NSO_1@SEL5DA710D34−SEL683B0107
5 - SEL3/AA@SEL2
6 - SEL37−SEL7286615E / A@SEL38−SEL7286615E
11 - SEL7286615E/T@SEL5
12 - SEL7286615E/T@SEL5
15 - SEL5DA710D3/USERTRANSLOG@SEL1
16 - SEL5DA710D3/USERTRANSLOG@SEL1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("RN">=1)
6 - filter(ROWNUM 8 - filter(ROWNUM 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@SEL5(别名为T的查询块5)selectt.event||t.directionasEVENTINOUT,t.∗fromsilverb.USERTRANSLOGt2、X@SEL4(别名为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、EL7286615E此查询块名字为数据库自动生成，表示进行简单视图合并后的查询块10053中明确标示Registeredqb:SEL7286615E 0x5924a390 (VIEW MERGE SEL4;SEL5)
4、A@SEL3(别名为A的查询块3)SELECTa.∗,ROWNUMRNfrom(子查询块X)awhereROWNUM5、AA@SEL2(别名为AA的查询块2)
select AA.OUT_BIZ_NO from (子查询块A) AA where RN >= 1
6、SEL683B0107此查询块，名字为数据库自动生成，表示进行子复杂查询展开的查询块这里应该是使用了DISTINCT(AA.OUTBIZNO)来替换查询块AA@SEL2中的AA.OUT_BIZ_NO
这一点从执行计划中的HASH UNIQUE也得到了验证
10053 中明确标示
SU: Transform an ANY subquery to semi-join or distinct.
SEL683B01070x58e85660(SUBQINTOVIEWFORCOMPLEXUNNESTSEL2)
7、USER_TRANS_LOG@SEL1(表名为A的查询块1)selectBIZNO,...fromsilverb.USERTRANSLOGwhere1=1ANDevent=′CHARGE′ANDORDERNOin(子查询块A)andDIRECTION=′OUT′andcreatetime>=todate(′2015−01−0100:00:00′,′yyyy−mm−ddhh24:mi:ss′)andcreatetime8、SEL5DA710D3 此查询块，名字为数据库自动生成，表示进行子查询展开的查询块，此处将
子查询ORDER_NO in (子查询块A)转换NEST LOOP的方式进行
10053 中明确标示
SEL5DA710D30x591f6a80(SUBQUERYUNNESTSEL1; SEL2)[FINAL]所以本语句的执行顺序为1、查询块T@SEL5和X@SEL4先进行视图合并，生成查询块EL7286615E，对应执行计划的8-12步，并且A@SEL3分页查询的谓词ROWNUM推入到此视图中，形成了SORTORDERBYSTOPKEY，形成一个VIEW2、查询块@SEL3不能进行视图合并，因为包含了ROWNUM，对应执行计划的6-7步，使用到谓词ROWNUM 3、查询块@SEL2通过COMPLEXUNNEST形成了SEL683B0107，对应执行计划的4-5步，使用谓词"RN">=1和distinct(AA.OUT_BIZ_NO)，形成第三个VIEW（VW_NSO_1）
4、查询块USER_TRANS_LOG@SEL1与SEL683B0107最终进行子查询展开得到了最终的查询块SEL5DA710D3，进行了2次NETSLOOP进行完成了所有的步骤，注意这里并没有完全的展开，而是把子查询当做一个视图或进行了处理，因为视图本生是一个复杂的子查询得出，而是转换为JION的方式。这些操作实际上在OutlineData中也有体现MERGE(@"SEL5")
UNNEST(@"SEL2")关于查询块的说明：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
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 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 - SEL5DA710D33−SEL683B0107 / VW_NSO_1@SEL5DA710D34−SEL683B0107
5 - SEL3/AA@SEL2
6 - SEL37−SEL7286615E / A@SEL38−SEL7286615E
11 - SEL7286615E/T@SEL5
12 - SEL7286615E/T@SEL5
15 - SEL5DA710D3/USERTRANSLOG@SEL1
16 - SEL5DA710D3/USERTRANSLOG@SEL1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("RN">=1)
6 - filter(ROWNUM 8 - filter(ROWNUM 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@SEL5(别名为T的查询块5)selectt.event||t.directionasEVENTINOUT,t.∗fromsilverb.USERTRANSLOGt2、X@SEL4(别名为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、EL7286615E此查询块名字为数据库自动生成，表示进行简单视图合并后的查询块10053中明确标示Registeredqb:SEL7286615E 0x5924a390 (VIEW MERGE SEL4;SEL5)
4、A@SEL3(别名为A的查询块3)SELECTa.∗,ROWNUMRNfrom(子查询块X)awhereROWNUM5、AA@SEL2(别名为AA的查询块2)
select AA.OUT_BIZ_NO from (子查询块A) AA where RN >= 1
6、SEL683B0107此查询块，名字为数据库自动生成，表示进行子复杂查询展开的查询块这里应该是使用了DISTINCT(AA.OUTBIZNO)来替换查询块AA@SEL2中的AA.OUT_BIZ_NO
这一点从执行计划中的HASH UNIQUE也得到了验证
10053 中明确标示
SU: Transform an ANY subquery to semi-join or distinct.
SEL683B01070x58e85660(SUBQINTOVIEWFORCOMPLEXUNNESTSEL2)
7、USER_TRANS_LOG@SEL1(表名为A的查询块1)selectBIZNO,...fromsilverb.USERTRANSLOGwhere1=1ANDevent=′CHARGE′ANDORDERNOin(子查询块A)andDIRECTION=′OUT′andcreatetime>=todate(′2015−01−0100:00:00′,′yyyy−mm−ddhh24:mi:ss′)andcreatetime8、SEL5DA710D3 此查询块，名字为数据库自动生成，表示进行子查询展开的查询块，此处将
子查询ORDER_NO in (子查询块A)转换NEST LOOP的方式进行
10053 中明确标示
SEL5DA710D30x591f6a80(SUBQUERYUNNESTSEL1; SEL2)[FINAL]所以本语句的执行顺序为1、查询块T@SEL5和X@SEL4先进行视图合并，生成查询块EL7286615E，对应执行计划的8-12步，并且A@SEL3分页查询的谓词ROWNUM推入到此视图中，形成了SORTORDERBYSTOPKEY，形成一个VIEW2、查询块@SEL3不能进行视图合并，因为包含了ROWNUM，对应执行计划的6-7步，使用到谓词ROWNUM 3、查询块@SEL2通过COMPLEXUNNEST形成了SEL683B0107，对应执行计划的4-5步，使用谓词"RN">=1和distinct(AA.OUT_BIZ_NO)，形成第三个VIEW（VW_NSO_1）
4、查询块USER_TRANS_LOG@SEL1与SEL683B0107最终进行子查询展开得到了最终的查询块SEL5DA710D3，进行了2次NETSLOOP进行完成了所有的步骤，注意这里并没有完全的展开，而是把子查询当做一个视图或进行了处理，因为视图本生是一个复杂的子查询得出，而是转换为JION的方式。这些操作实际上在OutlineData中也有体现MERGE(@"SEL5")
UNNEST(@"SEL2")关于查询块的说明：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
