Parameter                                 10.2.0.3    9.2.0
=========                                 ========    =====
_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

Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt

Tim,

 

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.

 

Thank you.

 

 

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208

From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Friday, September 19, 2008 3:25 PM
To: mwf@xxxxxxxx
Cc: William Wagman; oracle-l@xxxxxxxxxxxxx
Subject: Re: Where clause apparently failing

 

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%';

  COUNT(*)
----------
         1

SQL> select count(*) from dual where dummy = '%X%';

  COUNT(*)
----------
         0

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? Some

windows access method isn't messing with the "#" is it?

If you urgently need to get the correct answer (as opposed to tracking down

the 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 plan

to the worse.

Using an identical tool on a small table built for the exact purpose should

quickly resolve whether this is a view problem. Perhaps create that table as

select with the query that returns your 8693 rows (though possibly just the

one column or maybe one more that is unique so it is easy to track down the

backward looking rows through the view to the source.

Good luck!

mwf

-----Original Message-----

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]

On Behalf Of William Wagman

Sent: Friday, September 19, 2008 12:30 PM

To: oracle-l@xxxxxxxxxxxxx

Subject: Where clause apparently failing

Greetings,

I'm running 64-bit Oracle 10.2.0.4.0 EE on Windows server 2003.

The select statement

select patient_id, study_number

from ucdv_cc_summaryae

where study_number = 'UCDCC#128';

returns 8693 rows. Many of these rows have study_number other than ucdcc#128

including ucdcc#157, ucdcc#159, ucdcc#165, ucdcc#171, etc.

ucdv_cc_summaryae is a view and the column study_number is varchar2(100). I

am perplexed and would appreciate any thoughts. I am still having difficulty

getting to Oracle docs online.

Thanks.

Bill Wagman

Univ. of California at Davis

IET Campus Data Center

wjwagman@xxxxxxxxxxx

(530) 754-6208

--

//www.freelists.org/webpage/oracle-l

--

//www.freelists.org/webpage/oracle-l