Home -> Usenet -> comp.databases.oracle -> Inconsistent Query Results

Inconsistent Query Results

From: Adam Ruth <owski_at_hotmail.com>
Date: 24 Jun 2003 10:10:21 -0700
Message-ID: <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 Tue Jun 24 2003 - 19:10:21 CEST












Original text of this message