Re: Is Richard Niemiec Right

Things like:

    character_col = 9
and number_col = 23;

vs

    number_col = 23
and character_col = 9

The implicit

    to_number(character_col) = 9
may never see a problem because
everything that would break it is
filtered by the other predicate.

But if this predicate operates first,
because you happen to swap the
predicate order in the where clause,
then a query changes from giving the
right answer to failing with a conversion error.

But as I said, you have to start with
a bad design.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message
news:3f6dacae$0$18592$afc38c87_at_news.optusnet.com.au...


> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

news:bkk8m3$rul$1$8302bc10_at_news.demon.co.uk...


>

> >

> > It is quite easy with a single table query to switch between

> > the correct answer and a query failure (i.e. Oracle error) by

> > switching the order of predicates - both in RBO and CBO.

> > But it does require bad design in the first place, followed

> > by queries that then have to use type-coercion inappropriately

>

> This is interesting.  Is that to do with implicit type conversion

> or PK of one type and FK of another?  Can you point me to an example

> to make it clear?   You're not talking about switching left and

right


> sides of a logical comparison, are you?

>

>

> > > Now, IF you want to write your statements in a defensive fashion

so


> > > that they will work well with ot without the CBO (in case the

user


> > has

> > > CBO on but no stats), there MAY be a case for writing them using

the


> > old

> > > RBO rules.

> > >

> >

> > Not really - RBO works from bottom to top, CBO works from

> > top to bottom as far as 'tie-breaks' on join orders go.

>

> Whoa!  That's interesting.  Nasty...

> Haven't had it happen to me yet, but I usually avoid

> "tie break" joins like the plague! ;)

>

> --

> Cheers

> Nuno Souto

> wizofoz2k_at_yahoo.com.au.nospam

>

>