Search My Oracle Blog

Custom Search

07 April, 2008

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.

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