Inconsistent Query Results

[Adam Ruth]

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

[LKBrwn_DBA]

Did you check that OS_NAME is not NULL???

NULLs in any OS_NAME will produce invalid results in your query.

--
Posted via http://dbforums.com

[sybrandb]

LKBrwn_DBA <me*********@dbforums.com> wrote in message news:<30****************@dbforums.com>...

Did you check that OS_NAME is not NULL???

NULLs in any OS_NAME will produce invalid results in your query.

Where did u learn that utter nonsense?
He isn't including NULLs in his query is he?
The records with NULL os_names will be excluded, and also he isn't
asking for them, so nothing wrong.

Sybrand Bakker
Senior Oracle DBA

[Adam Ruth]

LKBrwn_DBA <me*********@dbforums.com> wrote in message news:<30****************@dbforums.com>...

Did you check that OS_NAME is not NULL???

NULLs in any OS_NAME will produce invalid results in your query.

There are no nulls in the os_name column. Even so, I disagree with
your assertion. Just because row x has a null value doesn't change
the fact that row y exists. select ... where os_name = 'SunOS' should
always return the same 5 records (in my table) no matter how many
other records have a null os_name.

In any event, nulls or not, an index shouldn't change the query
results, should it?

Adam Ruth

[LKBrwn_DBA]

True about the nulls.

I could not re-produce your error:

SQL>drop index tcomputer_os_name
2 /

Index dropped.

SQL>select * from ttcno_compliance tc
2 where exists (
3 select * from tcomputer
4 where computer_sys_id = tc.computer_sys_id
5 and os_name = 'SunOS')
6 /

TCNO_ENTRY_ID COMPUTER_SYS S
------------- ------------ -
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
230 2159101490 X
230 2159101373 X

12 rows selected.

SQL>create index tcomputer_os_name on tcomputer(os_name)
2 /

Index created.

SQL>analyze table tcomputer compute statistics
2 /

Table analyzed.

SQL>select * from ttcno_compliance tc
2 where exists (
3 select * from tcomputer
4 where computer_sys_id = tc.computer_sys_id
5 and os_name = 'SunOS')
6 /

TCNO_ENTRY_ID COMPUTER_SYS S
------------- ------------ -
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
230 2159101490 X
230 2159101373 X

12 rows selected.

SQL>

--
Posted via http://dbforums.com

[s.kapitza]

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
ow***@hotmail.com (Adam Ruth) wrote in message news:<f0**************************@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

[Adam Ruth]

sk******@volcanomail.com (s.kapitza) wrote in message news:<26**************************@posting.google. com>...

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
ow***@hotmail.com (Adam Ruth) wrote in message news:<f0**************************@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.

Oops, I meant that most of the databases here are 8i, and this one is
9i. Typing too fast...

[LKBrwn_DBA]

ABOUT NULLS
to: sybrandb

"When an indexed column is NULL, or when all columns in a
concatenated index are NULL, then the row concerned will NOT have an
entry in the index"

Quote from:
Oracle SQL High-Performance Tuning
Second Edition
Page 120, paragraph 8 "NULL VALUES"
Author:Guy Harrison
(c) 2001 - Prentice Hall PTR

--
Posted via http://dbforums.com

[Adam Ruth]

LKBrwn_DBA <me*********@dbforums.com> wrote in message news:<30****************@dbforums.com>...

ABOUT NULLS
to: sybrandb

"When an indexed column is NULL, or when all columns in a
concatenated index are NULL, then the row concerned will NOT have an
entry in the index"

Quote from:
Oracle SQL High-Performance Tuning
Second Edition
Page 120, paragraph 8 "NULL VALUES"
Author:Guy Harrison
(c) 2001 - Prentice Hall PTR

That's true, but it's not going to affect a query that uses the index.
If a query is using the index, then it's only going to search for
rows that have a value, since to use the index there will need to be a
where clause on that column.

In the end, the presence of nulls in a column is NOT going to affect
the results that are returned, whether or not the column is indexed.

The statement "NULLs in any OS_NAME will produce invalid results in
your query." is inaccurate.

Adam Ruth