Re: Inconsistent Query Results

hello,

what patchlevel does your db have ?

I remember there was a optimizer bug in Versions < 8.1.7.3 (somthing with <and> <or> combinations, so not your exacly your query), but who knows.

regards

s.kapitza

owski_at_hotmail.com (Adam Ruth) wrote in message news:<f0f51c80.0306240910.214a1534_at_posting.google.com>...
> Hello all,
>
> I've got what appears to be a bug in Oracle, but I don't want to make
> that judgement until I get someone's opinion who knows Oracle better
> than I.
>
> I'm running Oracle 8i on Solaris 7. I have a query that I get
> different result sets with and without a specific index. This is
> repeatable with these tables, though I haven't been able to isolate
> anything other than the index. I have the following output from a set
> of operations that shows the problem. As you can see, without the
> index, I get a set of records for a specific query, but when I add the
> index and do an analzye on the the table, I get no results from the
> same query.
>
> I didn't include any results from an explain plan, because it was
> huge, if anyone wants to see it, please let me know.
>
> Any feedback on this would be greatly appreciated,
>
> Adam Ruth
>
> drop index tcomputer_os_name
>
> Index dropped
>
> analyze table tcomputer compute statistics
>
> Table analyzed
>
> select * from ttcno_compliance tc where exists (select * from
> tcomputer where computer_sys_id = tc.computer_sys_id and os_name =
> 'SunOS')
>
> TCNO_ENTRY_ID COMPUTER_SYS_ID STATUS
> ------------- ---------------------------------------- ------
> 1 2159101373 X
> 61 2159101373 X
> 101 2159101373 X
> 121 2159101373 X
> 201 2159101373 X
> 1 2159101490 X
> 61 2159101490 X
> 101 2159101490 X
> 121 2159101490 X
> 201 2159101490 X
> 1 2164192390 X
> 61 2164192390 X
> 101 2164192390 X
> 121 2164192390 X
> 201 2164192390 X
> 1 2199466309 X
> 61 2199466309 X
> 101 2199466309 X
> 121 2199466309 X
> 201 2199466309 X
> 1 2198383241 X
> 61 2198383241 X
> 62 2198383241 P
> 81 2198383241 P
> 101 2198383241 X
> 121 2198383241 X
> 162 2198383241 P
> 201 2198383241 X
> 230 2198383241 X
> 230 2199466309 X
> 230 2159101490 X
> 230 2159101373 X
> 230 2164192390 X
> 33 rows selected
>
> create index tcomputer_os_name on tcomputer(os_name)
>
> Index created
>
> analyze table tcomputer compute statistics
>
> Table analyzed
>
> select * from ttcno_compliance tc where exists (select * from
> tcomputer where computer_sys_id = tc.computer_sys_id and os_name =
> 'SunOS')
>
> TCNO_ENTRY_ID COMPUTER_SYS_ID STATUS
> ------------- ---------------------------------------- ------
> 0 rows selected
Received on Wed Jun 25 2003 - 19:11:25 CEST