Wow, I was absolutely certain that I was right, when the error that I
made was so glaring. You and Matthias were absolutely right. The count
must be at least 135321. I am gonna go stand at the conner in the back
of the room for the rest of the day.... My high school teacher was
right. I should have paid more attention to the set theory.
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<BD3B9.76954$g9.217090_at_newsfeeds.bigpond.com>...
> "Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
> news:130ba93a.0211150121.5d7732e2_at_posting.google.com...
> > Maybe by now you have figured out what is going with your data and
> > that ORACLE, after all, did not give you wrong results. I don't think
> > ORACLE is capable of giving wrong results for simple queries like
> > these.
> >
> > > Using common sense it's easy to understand that there are more
> > > than 4 customers that are not 'PERSON' but are 'ACTIVE'.
> >
> > I failed to see how this is common sense.
> > Did I misunderstand you or did you come to this conclusion :
> >
> > count( customer_state = 'ACTIVE and customer_type <>
> > 'PERSON')
> > must be much larger than 4 because both these counts are so high
> > count(customer_state = 'ACTIVE) : 135324
> > count(customer_type <> 'PERSON') : 138292
> > ?
> >
> > (123450000444555666667890123456780xxxx)
> >
> > (xxxxabcdefghijklmmmmmmmnnnoppppptyuuuuu)
> >
> > The two groups above can each have many, many members. But there may
> > be only a few members (the xxxx) that belong to both groups ( the
> > "and" operator in your case).
> >
> > You can even try a count on the "or"
> > count( customer_state = 'ACTIVE' or customer_type <>
> > 'PERSON')
> > The result should be 135324 + 138292 - 4.
>
> Hi Jusung,
>
> I think you've failed to notice that there are only 138295 rows in the
> table. Therefore your result above is somewhat unlikely (actually, make that
> impossible).
>
> If there are only 138295 rows of which customer_state = 'ACTIVE' is 135324
> and customer_type <> 'PERSON' is 138292, then the value of customer_state =
> 'ACTIVE' AND customer_type <> 'PERSON' *MUST* be between 135321 and 135324
> inclusive (I think ;)
>
> Therefore a value of just 4 can only be deemed as highly unfortunate (or to
> put in another way, "what the ....".
>
> Something is amiss.
>
> I personally would like to see the execution plan and see what the optimizer
> "thinks" it's doing. It certainly looks like the CBO has chucked a wobbly,
> or some structure that the optimizer is trying to use (such as an index) is
> corrupt in some way.
>
> A search of metalink might shed some light.
>
> Cheers
>
> Richard
Received on Fri Nov 15 2002 - 12:06:39 CST
