This is the Test Data Creation :
SQL> drop table library_fat_book_list;
SQL> drop table my_fat_book_list;
SQL> drop table friends_fat_book_list;
SQL>
SQL> create table library_fat_book_list as
2 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created) published_date from dba_objects
3 union
4 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created)+object_id published_date from dba_objects
5 /
SQL>
SQL> create table my_fat_book_list as
2 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created) published_date from dba_objects where mod(object_id,400)=0
3 union
4 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created)+object_id published_date from dba_objects where mod(object_id,400)=0
5 /
SQL>
SQL> create table friends_fat_book_list as
2 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created) published_date from dba_objects where mod(object_id,80)=0
3 union
4 select owner author, substr(object_name,1,30) book_name, rpad('a',128,'f') other_cols, trunc(created)+object_id from dba_objects where mod(object_id,80)=0
5 /
SQL>
SQL>
SQL> create index library_bl_ndx on library_fat_book_list(author,book_name);
SQL> create index my_bl_ndx on my_fat_book_list(author,book_name);
SQL>
SQL> exec dbms_stats.gather_table_stats('','LIBRARY_FAT_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL> exec dbms_stats.gather_table_stats('','MY_FAT_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL> exec dbms_stats.gather_table_stats('','FRIENDS_FAT_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL>
SQL> select count(*) from library_fat_book_list;
COUNT(*)
----------
101968
SQL> select count(*) from my_fat_book_list;
COUNT(*)
----------
260
SQL> select count(*) from friends_fat_book_list;
COUNT(*)
----------
1296
SQL> select table_name, blocks, num_rows from user_tables where table_name in ('LIBRARY_FAT_BOOK_LIST','MY_FAT_BOOK_LIST','FRIENDS_FAT_BOOK_LIST') order by 1;
TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
FRIENDS_FAT_BOOK_LIST 36 1296
LIBRARY_FAT_BOOK_LIST 2502 101968
MY_FAT_BOOK_LIST 10 260
SQL>
Notice that the Row Count is almost exactly the same as in the first two test. However,
the Block Count is 4 times as much as earlier (see the first posting).
So, what do I see for MY_FAT_BOOK_LIST vis-a-vis MY_BOOK_LIST ?
SQL> select m.author, m.book_name from my_fat_book_list m
2 where
3 m.published_date
4 = (select max(l.published_date)
5 from library_fat_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: 4124939080
--------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3684K 526 (1) 00:00:07
1 SORT ORDER BY 101K 3684K 526 (1) 00:00:07
* 2 FILTER
3 TABLE ACCESS FULL MY_FAT_BOOK_LIST 260 9620 4 (0) 00:00:01
4 SORT AGGREGATE 1 39
5 TABLE ACCESS BY INDEX ROWID LIBRARY_FAT_BOOK_LIST 1 39 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 MAX("L"."PUBLISHED_DATE") FROM
"LIBRARY_FAT_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
405 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
Now, that is surprising ! The Cardinality Estimate for MY_FAT_BOOK_LIST is the same
as was for MY_BOOK_LIST. But Oracle now decides to use the FILTER operation against the subquery and *not* merge the subquery ! It's estimate of the resulting rowset count (101K) is way off ! Also, note the expected Byte count against that for the MY_BOOK_LIST query. Expanding the row size has made a significant difference.
And what happens with an explicit NO_MERGE Hint ?
SQL> select m.author, m.book_name from my_fat_book_list m
2 where
3 m.published_date
4 = (select /*+ NO_MERGE */ max(l.published_date)
5 from library_fat_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: 4124939080
--------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3684K 526 (1) 00:00:07
1 SORT ORDER BY 101K 3684K 526 (1) 00:00:07
* 2 FILTER
3 TABLE ACCESS FULL MY_FAT_BOOK_LIST 260 9620 4 (0) 00:00:01
4 SORT AGGREGATE 1 39
5 TABLE ACCESS BY INDEX ROWID LIBRARY_FAT_BOOK_LIST 1 39 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_FAT_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
405 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>
)
Yes, it is the same. So, Oracle had chosen NOT to merge the view even if I didn't provide
the Hint ! (could this be the "cost based transformation" that 9i doesn't do ?)
What happens with FRIENDS_FAT_BOOK_LIST ? How does that fare against FRIENDS_BOOK_LIST ?
Let's see :
SQL> select f.author, f.book_name from friends_fat_book_list f
2 where
3 f.published_date
4 = (select max(l.published_date)
5 from library_fat_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(deleting output)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 700107581
----------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2 240 569 (2) 00:00:07
* 1 FILTER
2 SORT GROUP BY 2 240 569 (2) 00:00:07
* 3 HASH JOIN 1296 151K 568 (2) 00:00:07
4 TABLE ACCESS FULL FRIENDS_FAT_BOOK_LIST 1296 64800 10 (0) 00:00:01
5 TABLE ACCESS FULL LIBRARY_FAT_BOOK_LIST 101K 6970K 556 (2) 00:00:07
----------------------------------------------------------------------------------------------
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
2496 consistent gets
0 physical reads
0 redo size
19720 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>
Hmm ! This is similar to FRIENDS_BOOK_LIST. Oracle *did* decide to merge the subquery
and use a Hash Join !
Let's see the NO_MERGE against MY_FRIENDS_BOOK_LIST.
SQL> select f.author, f.book_name from friends_fat_book_list f
2 where
3 f.published_date
4 = (select /*+ NO_MERGE */ max(l.published_date)
5 from library_fat_book_list l
6 where l.author=f.author
7 and l.book_name=f.book_name)
8 order by 1,2
9 /
(output deleted)
628 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1487416122
--------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 3783K 2607 (1) 00:00:32
1 SORT ORDER BY 101K 3783K 2607 (1) 00:00:32
* 2 FILTER
3 TABLE ACCESS FULL FRIENDS_FAT_BOOK_LIST 1296 49248 10 (0) 00:00:01
4 SORT AGGREGATE 1 39
5 TABLE ACCESS BY INDEX ROWID LIBRARY_FAT_BOOK_LIST 1 39 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_FAT_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
2010 consistent gets
0 physical reads
0 redo size
19720 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>
The expected Cardinality and Cost have changed, along with Byte count.
No comments:
Post a Comment