Complex View Merging -- 1

Complex View Merging occurs in the Transformation phase -- before the actual Optimization which is the choosing of Access Paths.
In Oracle 9i, if Oracle can do a Transformation it will do so, without considering the costs. That is, costs are not considered before the Transformation.
In Oracle 10g, the optimizer will attempt to work out the costs before deciding on a Transformed version.

Thus, where a query uses a subquery (or a correlated subquery) with an Aggregation function, Oracle can determine that the subquery is a complex view and choose to either execute the joins having merged the subquery (aka "Complex View Merging") or treat the subquery as a seperate View and then execute the joins.

In my next few blog postings, I am going to take up a simulated case of Libraries : One Public Library holding very many books and two private libraries of different sizes and the SQL queries will attempt to determine the latest published books (from the "master" list in the Public Library) also present in the two private libraries.

The "Aggregation" here would be the max(published_date) operation.

Thus, this is the test data :

SQL> rem We have three tables
SQL> rem     LIBRARY_BOOK_list is the list of books in the local library
SQL> rem     MY_BOOK_list is the books that I own and have at home
SQL> rem     FRIENDS_BOOK_list is the much larger private collection of a friend I envy
SQL> rem     There are 2 published_dates for each book
SQL>
SQL> rem My first query is to identify the latest library books that I, too, have in my private collection
SQL> rem The second query is the same for my friend's collection
SQL>
SQL>
SQL>
SQL> drop table library_book_list;
SQL> drop table my_book_list;
SQL> drop table friends_book_list;
SQL>
SQL> create table library_book_list as
  2  select owner author, substr(object_name,1,30) book_name, trunc(created) published_date from dba_objects
  3  union
  4  select owner author, substr(object_name,1,30) book_name, trunc(created)+object_id published_date from dba_objects
  5  /
SQL>
SQL> create table my_book_list as
  2  select owner author, substr(object_name,1,30) book_name, 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, trunc(created)+object_id published_date from dba_objects where mod(object_id,400)=0
  5  /
SQL>
SQL> create table friends_book_list  as
  2  select owner author, substr(object_name,1,30) book_name, 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, trunc(created)+object_id from dba_objects where mod(object_id,80)=0
  5  /
SQL>
SQL>
SQL> create index library_bl_ndx on library_book_list(author,book_name);
SQL> create index my_bl_ndx on my_book_list(author,book_name);
SQL>
SQL> exec dbms_stats.gather_table_stats('','LIBRARY_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL> exec dbms_stats.gather_table_stats('','MY_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL> exec dbms_stats.gather_table_stats('','FRIENDS_BOOK_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>100,cascade=>TRUE);
SQL>
SQL> select count(*) from library_book_list;

  COUNT(*)                                                                                                                     
----------                                                                                                                     
    101986                                                                                                                     
SQL> select count(*) from my_book_list;

  COUNT(*)                                                                                                                     
----------                                                                                                                     
       260                                                                                                                     
SQL> select count(*) from friends_book_list;

  COUNT(*)                                                                                                                     
----------                                                                                                                     
      1294                                                                                                                     
SQL> select table_name, blocks, num_rows from user_tables where table_name in ('LIBRARY_BOOK_LIST','MY_BOOK_LIST','FRIENDS_BOOK_LIST') order by 1;

TABLE_NAME                         BLOCKS   NUM_ROWS                                                                           
------------------------------ ---------- ----------                                                                           
FRIENDS_BOOK_LIST                      11       1294                                                                           
LIBRARY_BOOK_LIST                     626     101986                                                                           
MY_BOOK_LIST                            5        260                                                                           
SQL>

and the test results on 10.2.0.3 on 32-bit Windows (8KB Block Size) are published in the next few posts.