HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Ridiculous (well just for nonparticipants) behaviour of LEFT JOIN in Oracle 11g
ORACLE STUFFS
12-Apr-2011
There is great feature in Oracle 11g, named Optimizer join elimination. It's designed to reduce effort of database following way:
When there is a Left outer joined table in query, and no column from the table used in result set, optimizer simplifies query and doesn't access the outer joined table - as not necessary.
It sounds great, doesn't it?
Well, it would sound well, if there is no bug.

When you omit all columns from outer joined table and there is some complex join condition (especially "OR" or "IN()"), it could happen the evaluation goes wrong way. The outer join condition could cause it filters not just the outer joined part, but all resultset.


I strongly recommend to use:

alter session set "_optimizer_join_elimination_enabled"=false;


or adequate database settings to avoid the dangerous experiment.

By the way:
PARAMETER                                DESCRIPTION                                                      
---------------------------------------- -----------------------------------------------------------------
_db_file_optimizer_read_count            multiblock read count for regular clients                        
_optimizer_adjust_for_nulls              adjust selectivity for null values                               
_optimizer_autostats_job                 enable/disable auto stats collection job                         
_optimizer_better_inlist_costing         enable improved costing of index access using in-list(s)         
_optimizer_block_size                    standard block size used by optimizer                            
_optimizer_cache_stats                   cost with cache statistics                                       
_optimizer_cartesian_enabled             optimizer cartesian join enabled                                 
_optimizer_cbqt_factor                   cost factor for cost-based query transformation                  
_optimizer_cbqt_no_size_restriction      disable cost based transformation query size restriction         
_optimizer_ceil_cost                     CEIL cost in CBO                                                 
_optimizer_choose_permutation            force the optimizer to use the specified permutation             
_optimizer_complex_pred_selectivity      enable selectivity estimation for builtin functions              
_optimizer_compute_index_stats           force index stats collection on index creation/rebuild           
_optimizer_connect_by_combine_sw         combine no filtering connect by and start with                   
_optimizer_connect_by_cost_based         use cost-based transformation for connect by                     
_optimizer_correct_sq_selectivity        force correct computation of subquery selectivity                
_optimizer_cost_based_transformation     enables cost-based query transformation                          
_optimizer_cost_filter_pred              enables  costing of filter predicates in IO cost model           
_optimizer_cost_hjsmj_multimatch         add cost of generating result set when #rows per key > 1         
_optimizer_cost_model                    optimizer cost model                                             
_optimizer_degree                        force the optimizer to use the same degree of parallelism        
_optimizer_dim_subq_join_sel             use join selectivity in choosing star transformation dimensions  
_optimizer_disable_strans_sanity_checks  disable star transformation sanity checks                        
_optimizer_dyn_smp_blks                  number of blocks for optimizer dynamic sampling                  
_optimizer_enable_density_improvements   use improved density computation for selectivity estimation      
_optimizer_enhanced_filter_push          push filters before trying cost-based query transformation       
_optimizer_extended_cursor_sharing       optimizer extended cursor sharing                                
_optimizer_filter_pred_pullup            use cost-based flter predicate pull up transformation            
_optimizer_fkr_index_cost_bias           Optimizer index bias over FTS/IFFS under first K rows mode       
_optimizer_ignore_hints                  enables the embedded hints to be ignored                         
_optimizer_invalidation_period           time window for invalidation of cursors of analyzed objects      
_optimizer_join_elimination_enabled      optimizer join elimination enabled                               
_optimizer_join_order_control            controls the optimizer join order search algorithm               
_optimizer_join_sel_sanity_check         enable/disable sanity check for multi-column join selectivity    
_optimizer_max_permutations              optimizer maximum join permutations per query block              
_optimizer_min_cache_blocks              set minimum cached blocks                                        
_optimizer_mjc_enabled                   enable merge join cartesian                                      
_optimizer_mode_force                    force setting of optimizer mode for user recursive SQL also      
_optimizer_multiple_cenv                 generate and run plans using several compilation environments    
_optimizer_native_full_outer_join        execute full outer join using native implementaion               
_optimizer_new_join_card_computation     compute join cardinality using non-rounded input values          
_optimizer_or_expansion                  control or expansion approach used                               
_optimizer_or_expansion_subheap          Use subheap for optimizer or-expansion                           
_optimizer_order_by_elimination_enabled  Eliminates order bys from views before query transformation      
_optimizer_outer_to_anti_enabled         Enable transformation of outer-join to anti-join if possible     
_optimizer_percent_parallel              optimizer percent parallel                                       
_optimizer_push_down_distinct            push down distinct from query block to table                     
_optimizer_push_pred_cost_based          use cost-based query transformation for push pred optimization   
_optimizer_random_plan                   optimizer seed value for random plans                            
_optimizer_rownum_bind_default           Default value to use for rownum bind                             
_optimizer_rownum_pred_based_fkr         enable the use of first K rows due to rownum predicate           
_optimizer_save_stats                    enable/disable saving old versions of optimizer stats            
_optimizer_search_limit                  optimizer search limit                                           
_optimizer_self_induced_cache_cost       account for self-induced caching                                 
_optimizer_skip_scan_enabled             enable/disable index skip scan                                   
_optimizer_skip_scan_guess               consider index skip scan for predicates with guessed selectivity 
_optimizer_sortmerge_join_enabled        enable/disable sort-merge join method                            
_optimizer_sortmerge_join_inequality     enable/disable sort-merge join using inequality predicates       
_optimizer_squ_bottomup                  enables unnesting of subquery in a bottom-up manner              
_optimizer_star_tran_in_with_clause      enable/disable star transformation in with clause queries        
_optimizer_star_trans_min_cost           optimizer star transformation minimum cost                       
_optimizer_star_trans_min_ratio          optimizer star transformation minimum ratio                      
_optimizer_starplan_enabled              optimizer star plan enabled                                      
_optimizer_system_stats_usage            system statistics usage                                          
_optimizer_trace                         optimizer trace parameter                                        
_optimizer_transitivity_retain           retain equi-join pred upon transitive equality pred generation   
_optimizer_undo_changes                  undo changes to query optimizer                                  
_optimizer_undo_cost_change              optimizer undo cost change                                       
_optimizer_use_histograms                Controls whether to use histograms                               
_optimizer_use_subheap                   Enables physical optimizer subheap                               

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky