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