1. 首页
  2. IT资讯

一条SQL语句查询块分解及查询转换

原始语句如下:
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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-1456171/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183719.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code