Re: Where clause apparently failing
Date: Mon, 22 Sep 2008 16:33:28 -0600
I'll try to narrow it down for you... :-)
By looking at 10053 trace outputs when OPTIMIZER_FEATURES_ENABLE is set to first 10.2.0.3 and then to 9.2.0 (while running Oracle10gR2 10.2.0.3 software), I get the following differences in those parameters whose default settings are parameters influenced by O_F_E:
Parameter 10.2.0.3 9.2.0The parameters marked with "***" next to the name are new in 10.2.0.3 over 10.2.0.2. I didn't bother checking differences with 10.2.0.1 as nobody should be using that version, and I don't have access to a 10.2.0.4 database instance as yet.
========= ======== =====
_bloom_filter_enabled true false
_dimension_skip_null true false
_first_k_rows_dynamic_proration *** true false
_gby_hash_aggregation_enabled true false
_globalindex_pnum_filter_enabled true false
_local_communication_costing_enabled true false
_mmv_query_rewrite_enabled true false
_optimizer_better_inlist_costing all off
_optimizer_cbqt_no_size_restriction true false
_optimizer_complex_pred_selectivity true false
_optimizer_compute_index_stats true false
_optimizer_connect_by_cost_based true false
_optimizer_correct_sq_selectivity true false
_optimizer_cost_based_transformation linear off
_optimizer_cost_hjsmj_multimatch true false
_optimizer_dim_subq_join_sel true false
_optimizer_enhanced_filter_push true false
_optimizer_extended_cursor_sharing udo none
_optimizer_filter_pred_pullup true false
_optimizer_join_elimination_enabled true false
_optimizer_join_order_control 3 0
_optimizer_join_sel_sanity_check true false
_optimizer_or_expansion depth breadth
_optimizer_order_by_elimination_enabled true false
_optimizer_outer_to_anti_enabled true false
_optimizer_push_pred_cost_based true false
_optimizer_rownum_bind_default *** 10 0
_optimizer_rownum_pred_based_fkr true false
_optimizer_squ_bottomup true false
_optimizer_star_tran_in_with_clause true false
_optimizer_transitivity_retain true false
_optimizer_undo_cost_change 10.2.0.3 9.2.0
_partition_view_enabled true false
_push_join_union_view2 true false
_px_pwg_enabled true false
_query_rewrite_setopgrw_enable true false
_remove_aggr_subquery true false
_right_outer_hash_enable true false
_selfjoin_mv_duplicates true false
_sql_model_unfold_forloops run_time compile_time
optimizer_dynamic_sampling 2 1
optimizer_mode all_rows choose
query_rewrite_enabled true false
skip_unusable_indexes true false
Anyway, quite a few differences, but mostly because most of these parameters were new with Oracle10gR2, didn't exist in 9iR2.
But one (or more) of these would probably be your culprit. If you feel ambitious, you could individually change each one of these parameters and test the query, in order to narrow it down further?
Hope this helps!
Tim Gorman consultant - Evergreen Database Technologies, Inc. P.O. Box 630791, Highlands Ranch CO 80163-0791 website = http://www.EvDBT.com/ email = Tim@EvDBT.com mobile = +1-303-885-4526 fax = +1-303-484-3608 Yahoo IM = tim_evdbt
William Wagman wrote:
-- http://www.freelists.org/webpage/oracle-l Received on Mon Sep 22 2008 - 17:33:28 CDT
Running the query from sqlplus fails also. In that the database was recently upgraded from 9i to 10g and the queries were successful in 9i I set the init parameters
optimizer_mode = rule
optimizer_features_enable = "9.2.0"
And the problem resolved. We know the views are poorly designed the question is now one of narrowing down what has changed. I guess that is the fun part.
Univ. of California at Davis
IET Campus Data Center
Only the LIKE and NOT LIKE operators are even aware of wildcard characters. Operators such as "=", etc treat wildcard characters as data values...
SQL> select count(*) from dual where dummy like '%X%';
SQL> select count(*) from dual where dummy = '%X%';
Mark W. Farnham wrote:looks weird to me as if # is being taken as some kind of wildcard.
What tool or pathway is submitting the query from what environment? Somewindows access method isn't messing with the "#" is it?
If you urgently need to get the correct answer (as opposed to tracking downthe root cause first, which I'm not saying shouldn't be done eventually),then I'd try tacking on an
and study_number like '%128'
which should filter down your result set without changing your existing planto the worse.
Using an identical tool on a small table built for the exact purpose shouldquickly resolve whether this is a view problem. Perhaps create that table asselect with the query that returns your 8693 rows (though possibly just theone column or maybe one more that is unique so it is easy to track down thebackward looking rows through the view to the source.
-----Original Message-----On Behalf Of William WagmanSent: Friday, September 19, 2008 12:30 PMSubject: Where clause apparently failing
I'm running 64-bit Oracle 10.2.0.4.0 EE on Windows server 2003.
The select statement
select patient_id, study_numberfrom ucdv_cc_summaryaewhere study_number = 'UCDCC#128';
returns 8693 rows. Many of these rows have study_number other than ucdcc#128including ucdcc#157, ucdcc#159, ucdcc#165, ucdcc#171, etc.
ucdv_cc_summaryae is a view and the column study_number is varchar2(100). Iam perplexed and would appreciate any thoughts. I am still having difficultygetting to Oracle docs online.
Bill WagmanUniv. of California at DavisIET Campus Data Center(530) 754-6208--