SQL> Select dfId,
2 Id As Client_Id,
3 Login As Client_Name,
4 To_Char(Created_Date, 'yyyy-mm-dd') As Start_Date,
5 Sum(1) Over() As Total_Count
6 From (Select a.*
7 From tb_test1 a,
8 (Select Client_Id
9 From (Select Client_Id,
10 Event_Date,
11 Row_Number() Over(Partition By Client_Id Ord
er By Event_Date Desc) Rn
12 From tb_test1_Rolling_Daily_Repo)
13 Where Rn = 1
14 And Event_Date <
15 (Select Max(Event_Date) From tb_test1_abc_Repo
rt)) b
16 Where a.Id = b.Client_Id
17 And a.dfId = 4
18 Union
19 Select *
20 From tb_test1 a
21 Where a.dfId = 4
22 And a.Created_Date <
23 (Select Max(To_Date) From tb_test1_abc_Report) - 1
24 And a.Id Not In
25 (Select Distinct Client_Id From tb_test1_Rolling_Daily_Repo
))
26 Order By Login;
3185 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=24 Card=171 Bytes=10
602)
1 0 WINDOW (SORT) (Cost=24 Card=171 Bytes=10602)
2 1 VIEW (Cost=24 Card=171 Bytes=10602)
3 2 SORT (UNIQUE) (Cost=24 Card=171 Bytes=24700)
4 3 UNION-ALL
5 4 HASH JOIN (Cost=10 Card=19 Bytes=3268)
6 5 VIEW (Cost=3 Card=19 Bytes=665)
7 6 WINDOW (SORT PUSHED RANK) (Cost=3 Card=19 Byte
s=228)
8 7 INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_E
VENT' (UNIQUE) (Cost=1 Card=19 Bytes=228)
9 6 SORT (AGGREGATE)
10 9 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX5_711
' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)
11 5 TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=
3189 Bytes=436893)
12 4 HASH JOIN (ANTI) (Cost=8 Card=152 Bytes=21432)
13 12 TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=
159 Bytes=21783)
14 13 SORT (AGGREGATE)
15 14 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX4_101
' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)
16 12 INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_EVENT
' (UNIQUE) (Cost=1 Card=19 Bytes=76)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
268 consistent gets
0 physical reads
0 redo size
73882 bytes sent via SQL*Net to client
2828 bytes received via SQL*Net from client
214 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
3185 rows processed
用row_number() over 分析函数一致性读为268
SQL> Select dfId,
2 Id As Client_Id,
3 Login As Client_Name,
4 To_Char(Created_Date, 'yyyy-mm-dd') As Start_Date,
5 Sum(1) Over() As Total_Count
6 From (Select a.*
7 From tb_test1 a,
8 (select client_id
9 from (Select Client_Id, max(Event_Date) event_date
10 From tb_test1_Rolling_Daily_Repo
11 group by Client_Id)
12 where Event_Date <
13 (Select Max(Event_Date) From tb_test1_abc_Repo
rt)) b
14 Where a.Id = b.Client_Id
15 And a.dfId = 4
16 Union
17 Select *
18 From tb_test1 a
19 Where a.dfId = 4
20 And a.Created_Date <
21 (Select Max(To_Date) From tb_test1_abc_Report) - 1
22 And a.Id Not In
23 (Select Distinct Client_Id From tb_test1_Rolling_Daily_Repo
))
24 Order By Login;
3185 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=17 Card=153 Bytes=94
86)
1 0 WINDOW (SORT) (Cost=17 Card=153 Bytes=9486)
2 1 VIEW (Cost=17 Card=153 Bytes=9486)
3 2 SORT (UNIQUE) (Cost=17 Card=153 Bytes=21582)
4 3 UNION-ALL
5 4 NESTED LOOPS (Cost=2 Card=1 Bytes=150)
6 5 VIEW (Cost=1 Card=1 Bytes=13)
7 6 FILTER
8 7 SORT (GROUP BY) (Cost=1 Card=1 Bytes=12)
9 8 INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME
_EVENT' (UNIQUE) (Cost=1 Card=19 Bytes=228)
10 7 SORT (AGGREGATE)
11 10 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX5_7
11' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)
12 5 TABLE ACCESS (BY INDEX ROWID) OF 'tb_test1' (Co
st=1 Card=1 Bytes=137)
13 12 INDEX (UNIQUE SCAN) OF 'PK_tb_test1' (UNIQUE)
14 4 HASH JOIN (ANTI) (Cost=8 Card=152 Bytes=21432)
15 14 TABLE ACCESS (FULL) OF 'tb_test1' (Cost=6 Card=
159 Bytes=21783)
16 15 SORT (AGGREGATE)
17 16 INDEX (FULL SCAN (MIN/MAX)) OF 'FKINDEX4_101
' (NON-UNIQUE) (Cost=3 Card=2156978 Bytes=17255824)
18 14 INDEX (FULL SCAN) OF 'IDX_CRDR_CLIENT_GAME_EVENT
' (UNIQUE) (Cost=1 Card=19 Bytes=76)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
159 consistent gets
0 physical reads
0 redo size
73882 bytes sent via SQL*Net to client
2828 bytes received via SQL*Net from client
214 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
3185 rows processed
SQL>
用group by 函数一致性读为 159 减少了268-159=109 个一致性读
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-214932/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7199859/viewspace-214932/