Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Queries give impossible results

Re: Queries give impossible results

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 15 Nov 2002 21:17:14 +1000
Message-ID: <BD3B9.76954$g9.217090@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 - 05:17:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US