SQL> REM --------------------------------------------------------------------------------------------------------------
> REM REM REM ******* With Complex View Merging ******
SQL>
SQL> select f.author, f.book_name from friends_book_list f
2 where
3 f.published_date
4 = (select max(l.published_date)
5 from library_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(deleted output)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2027338862
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2 148 151 (6) 00:00:02
* 1 FILTER
2 SORT GROUP BY 2 148 151 (6) 00:00:02
* 3 HASH JOIN 1294 95756 149 (5) 00:00:02
4 TABLE ACCESS FULL FRIENDS_BOOK_LIST 1294 47878 4 (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("F"."PUBLISHED_DATE"=MAX("L"."PUBLISHED_DATE"))
3 - access("L"."AUTHOR"="F"."AUTHOR" AND "L"."BOOK_NAME"="F"."BOOK_NAME")
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
625 consistent gets
0 physical reads
0 redo size
19721 bytes sent via SQL*Net to client
461 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628 rows processed
Oracle has chose to Join the two tables (it has "merged" the
subquery into the parent query).
So let's see what happens when I use the NO_MERGE Hint :
SQL> REM --------------------------------------------------------------------------------------------------------------
> REM REM REM ******* WITHOUT Complex View Merging ******
SQL>
SQL> select f.author, f.book_name from friends_book_list f
2 where
3 f.published_date
4 = (select /*+ NO_MERGE */ max(l.published_date)
5 from library_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(deleted output)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 258794817
----------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3685K 2596 (1) 00:00:32
1 SORT ORDER BY 101K 3685K 2596 (1) 00:00:32
* 2 FILTER
3 TABLE ACCESS FULL FRIENDS_BOOK_LIST 1294 47878 4 (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("F"."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
1966 consistent gets
0 physical reads
0 redo size
19721 bytes sent via SQL*Net to client
461 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628 rows processed
SQL>
Aah ! Something more complicated. Oracle has instantiated the subquery as a View.
However, this time we see that the NO_MERGE that we forced Oracle to do has significantly increased the total effort. The "COST" has gone up from 151 to 2,596 but, more importantly, the "consistent gets" is up from 625 to 1,966.
So, with a larger FRIENDS_BOOK_LIST table, Oracle was actually better off doing Complex View Merging !
There is no "hard-and-fast" rule as to whether you should have Complex View Merging enabled or not. Oracle 10g does evaluate costs when choosing between a Merged and an Instantiated representation of the Aggregation subquery ("complex view").
In the next few postings, I will explore a "fatter" table (more columns increasing the number of table blocks without increasing the number of rows at all) and also see if I can figure out how the cost based transformation works.
No comments:
Post a Comment