Complex View Merging -- 7

My final test. What happens if I disable Cost Based Transformation ?

SQL> alter session set "_optimizer_cost_based_transformation"=OFF;

Session altered.

SQL>
SQL> select /* disabled cost_based_trasnfm */ 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  /
(output deleted)
123 rows selected.


Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 1796690992                                                                                                       
                                                                                                                                  
-------------------------------------------------------------------------------------------------------                           
 Id   Operation              Name                   Rows   Bytes TempSpc Cost (%CPU) Time                                
-------------------------------------------------------------------------------------------------------                           
   0  SELECT STATEMENT                                101K  7666K         3315   (2) 00:00:40                            
   1   MERGE JOIN                                     101K  7666K         3315   (2) 00:00:40                            
   2    SORT JOIN                                     101K  3982K    10M  3310   (2) 00:00:40                            
   3     VIEW                VW_SQ_1                  101K  3982K         2250   (2) 00:00:28                            
   4      HASH GROUP BY                               101K  6969K    16M  2250   (2) 00:00:28                            
   5       TABLE ACCESS FULL LIBRARY_FAT_BOOK_LIST    101K  6969K          556   (2) 00:00:07                            
*  6    SORT JOIN                                     260   9620             5  (20) 00:00:01                            
   7     TABLE ACCESS FULL   MY_FAT_BOOK_LIST         260   9620             4   (0) 00:00:01                            
-------------------------------------------------------------------------------------------------------                           
                                                                                                                                  
Predicate Information (identified by operation id):                                                                               
---------------------------------------------------                                                                               
                                                                                                                                  
   6 - access("AUTHOR"="M"."AUTHOR" AND "BOOK_NAME"="M"."BOOK_NAME" AND                                                           
              "M"."PUBLISHED_DATE"="VW_COL_1")                                                                                    
       filter("BOOK_NAME"="M"."BOOK_NAME" AND "AUTHOR"="M"."AUTHOR" AND                                                           
              "M"."PUBLISHED_DATE"="VW_COL_1")                                                                                    


Statistics
----------------------------------------------------------                                                                        
          1  recursive calls                                                                                                      
          0  db block gets                                                                                                        
       2470  consistent gets                                                                                                      
          0  physical reads                                                                                                       
          0  redo size                                                                                                            
       5107  bytes sent via SQL*Net to client                                                                                     
        483  bytes received via SQL*Net from client                                                                               
         10  SQL*Net roundtrips to/from client                                                                                    
          2  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
        123  rows processed                                                                                                       

SQL>

Much more expensive. The 10.2 Cost Based Transformation WAS better.