Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=309 Card=1 Bytes=113
) 1 0 SORT (ORDER BY) (Cost=309 Card=1 Bytes=113)
2 1 NESTED LOOPS (Cost=307 Card=1 Bytes=113)
3 2 HASH JOIN (OUTER) (Cost=305 Card=1 Bytes=93)
4 3 TABLE ACCESS (FULL) OF 'TBL_FLIGHT_PRODUCT_REDEEM' (
Cost=273 Card=1 Bytes=80) 5 3 TABLE ACCESS (FULL) OF 'TBL_NETPAY_ORDER_HISTORY' (C
ost=31 Card=24423 Bytes=317499) 6 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_MEMBER_IN
FO' (Cost=2 Card=1 Bytes=20) 7 6 INDEX (UNIQUE SCAN) OF 'PK_TBL_MEMBER_INFO' (UNIQUE)
(Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4244 consistent gets
4204 physical reads
0 redo size
1683 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed不知道这是啥意思
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=309 Card=1 Bytes=113
) 1 0 SORT (ORDER BY) (Cost=309 Card=1 Bytes=113)
2 1 NESTED LOOPS (Cost=307 Card=1 Bytes=113)
3 2 HASH JOIN (OUTER) (Cost=305 Card=1 Bytes=93)
4 3 TABLE ACCESS (FULL) OF 'TBL_FLIGHT_PRODUCT_REDEEM' (
Cost=273 Card=1 Bytes=80) 5 3 TABLE ACCESS (FULL) OF 'TBL_NETPAY_ORDER_HISTORY' (C
ost=31 Card=24423 Bytes=317499) 6 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_MEMBER_IN
FO' (Cost=2 Card=1 Bytes=20) 7 6 INDEX (UNIQUE SCAN) OF 'PK_TBL_MEMBER_INFO' (UNIQUE)
(Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4244 consistent gets
4204 physical reads
0 redo size
1683 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed不知道这是啥意思
1, 用hash join关联表('TBL_FLIGHT_PRODUCT_REDEEM' ,'TBL_NETPAY_ORDER_HISTORY' ), 这个过程中对这两个表的访问都是全表扫描, 这有点问题.
2, 用nested loop关联表(步骤1得到的中间结果,TBL_MEMBER_INFO' ), 这里对TBL_MEMBER_INFO的访问是通过唯一索引实现的, 这里的效率较高.
3, 对步骤2得到的结果排序.
----------------------------------------------------------
0 recursive calls (对数据字典的调用次数)
0 db block gets (请求的块在缓冲区中的块数)
4244 consistent gets (块请求次数)
4204 physical reads (物理IO次数)
0 redo size
1683 bytes sent via SQL*Net to client (传送给客户端的字节数)
503 bytes received via SQL*Net from client (从客户端得到的字节数, 主要指sql语句长度)
2 SQL*Net roundtrips to/from client
1 sorts (memory) (在内存中的排序, )
0 sorts (disk)
9 rows processed (结果行数.)
从右往左,从上往下。解释一下:
从缩进最多的行看起;缩进量相同的,从上往下看。PS:一看就知道是Oracle的执行计划,找一本SQL调优的书学习吧,比你在论坛里得到的答案更系统。