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