Re: Queries give impossible results

Jake,
You ask for help but don't provide rudimentary details (like the explain plan). An explain plan isn't going to reveal any company secrets. (I really can't see how the table names or index names are going to "reveal" some company secret and if you feel they are just edit the darn thing.) We don't know what company (and probably no one cares). Given the simple task (generating an explain plan) takes less effort than all this excuses why not.

Gentlemen,
I think we have a canard.
Jim

"Jake S" <nospam_at_nospam.net> wrote in message news:Po4B9.149$Ph.6060_at_read2.inet.fi...
> > I can think of a couple of ways to MAKE this happen
> > in a live demo - but I'm assuming you're not trying to
> > wind people up.
> >
> > So would you post a full execution path for each
> > of the four queries - (not just the autotrace thing)
> > and check that the execution plan Oracle reports
> > is the one it actually uses.
> >
>
> I certainly don't use my working hours to fool others if that's what you
> meant. I appreciate all the help all of you have provided. The next step
> would be to give you the execution plan as many of you have requested.
> However, I'm reluctant to do that because if I follow the rules of my
> complany I shouldn't post any messages that concern our business or
> production.
>
> Anyway, I'm thinking that we are in the wrong tracks. Indexes are not the
> problem. I think this is some really weird character set problem because
the
> data is originated from a legacy system. However, that shouldn't be
possible
> because we (not me) used Java+JDBC to send data. Anyway, there are several
> conversions along the way (don't ask). The next step we are going to take
is
> to send all this data to another database and see if we have the same
> problems.
>
> In any case that could be related to character sets because all the 4
> customers we are discussed has been inserted with GUI i.e. different way
> than all the others. That supports my theory because it's possible to get
> right result several ways (sorry I didn't send these earlier):
>
> SQL> SELECT count(*) FROM ci_customer WHERE customer_state = 'ACTIVE' AND
> UPPER(customer_type) <> 'PERSON';
>
> COUNT(*)
> ----------
> 135321
>
> SQL> SELECT count(*) FROM ci_customer WHERE customer_type LIKE 'COMPANY'
AND
> customer_state LIKE 'ACTIVE';
>
> COUNT(*)
> ----------
> 135321
>
> - Jake -
>
>
Received on Fri Nov 15 2002 - 09:23:01 CST