Re: Queries give impossible results

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