[on 10.2.0.3 on 32-bit Windows (8KB Block Size) ]
Querying for My Private Library (fewer books) against the Public Library, with Complex View Merging enabled by default, I see that the tables MY_BOOK_LIST and LIBRARY_BOOK_LIST are joined. The implicit view in the Subquery that does gets the max(published_date) has actually been merged into the parent query.
SQL> REM --------------------------------------------------------------------------------------------------------------
> REM REM REM ******* With Complex View Merging ******
SQL>
SQL>
SQL>
SQL>
SQL> select m.author, m.book_name from my_book_list m
2 where
3 m.published_date
4 = (select max(l.published_date)
5 from library_book_list l
6 where l.author=m.author
7 and l.book_name=m.book_name)
8 order by 1,2
9 /
(deleting the output)
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2352755801
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2 146 149 (5) 00:00:02
* 1 FILTER
2 SORT GROUP BY 2 146 149 (5) 00:00:02
* 3 HASH JOIN 260 18980 148 (5) 00:00:02
4 TABLE ACCESS FULL MY_BOOK_LIST 260 9360 3 (0) 00:00:01
5 TABLE ACCESS FULL LIBRARY_BOOK_LIST 101K 3685K 143 (3) 00:00:02
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("M"."PUBLISHED_DATE"=MAX("L"."PUBLISHED_DATE"))
3 - access("L"."AUTHOR"="M"."AUTHOR" AND "L"."BOOK_NAME"="M"."BOOK_NAME")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
616 consistent gets
0 physical reads
0 redo size
4218 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
123 rows processed
Note that Oracle expected to retrieve only 2 rows at a "Cost" of 149.
This execution plan did 616 logical reads.
What happens if I disable Complex_View_Merging by using the "NO_MERGE" Hint ?
This is what I get :
SQL> REM --------------------------------------------------------------------------------------------------------------
> REM REM REM ******* WITHOUT Complex View Merging ******
SQL>
SQL>
SQL> select m.author, m.book_name from my_book_list m
2 where
3 m.published_date
4 = (select /*+ NO_MERGE */ max(l.published_date)
5 from library_book_list l
6 where l.author=m.author
7 and l.book_name=m.book_name)
8 order by 1,2
9 /
(deleting the output)
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3701741988
----------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3585K 525 (1) 00:00:07
1 SORT ORDER BY 101K 3585K 525 (1) 00:00:07
* 2 FILTER
3 TABLE ACCESS FULL MY_BOOK_LIST 260 9360 3 (0) 00:00:01
4 SORT AGGREGATE 1 37
5 TABLE ACCESS BY INDEX ROWID LIBRARY_BOOK_LIST 1 37 4 (0) 00:00:01
* 6 INDEX RANGE SCAN LIBRARY_BL_NDX 2 3 (0) 00:00:01
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("M"."PUBLISHED_DATE"= (SELECT /*+ NO_MERGE */ MAX("L"."PUBLISHED_DATE") FROM
"LIBRARY_BOOK_LIST" "L" WHERE "L"."BOOK_NAME"=:B1 AND "L"."AUTHOR"=:B2))
6 - access("L"."AUTHOR"=:B1 AND "L"."BOOK_NAME"=:B2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
396 consistent gets
0 physical reads
0 redo size
4218 bytes sent via SQL*Net to client
406 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
123 rows processed
SQL>
What we now see is that Oracle had executed the subquery (operations 4 to 6) and passed
AUTHOR and BOOK_NAME from each row fetched in the outer query (the FILTER
operation at Operation 2 from the Full Table Scan of MY_BOOK_LIST at Operation 3).
Thus, for each row retrieved from MY_BOOK_LIST, Oracle did an Indexed Range Scan
of LIBRARY_BOOK_LIST. Surprisingly, with this Execution Plan, Oracle thought it would retrieve 101 thousand rows at a "Cost" of 525.
It is no surpise, then, that the default behaviour of Complex View Merging which Oracle
thought had a lower cost (149 against 525) was chosen. However, if we look at the count
of logical reads in "consistent gets", the first plan was actually more "expensive" in that
it read 616 blocks while the second plan read 396 blocks.
My next pair of tests will be using the larger "FRIENDS_BOOK_LIST" table to drive
the subquery.
No comments:
Post a Comment