Search My Oracle Blog

Custom Search

12 April, 2008

Complex View Merging - 4,5,6

Continuing the tests that I did in postings 1 , 2 , 3 , I now use "fatter" tables, adding 128 bytes in the row size.

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016