Inconsistent Query Results

**LKBrwn_DBA** · Jun 27 '08, 04:47 PM

Re: Inconsistent Query Results

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@yahoo.com** · Jun 27 '08, 04:48 PM

Re: Inconsistent Query Results

LKBrwn_DBA <member30625@db forums.comwrote in message news:<3038469.1 056484024@dbfor ums.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** · Jun 27 '08, 04:48 PM

Re: Inconsistent Query Results

LKBrwn_DBA <member30625@db forums.comwrote in message news:<3038469.1 056484024@dbfor ums.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** · Jun 27 '08, 04:48 PM

Re: Inconsistent Query Results

True about the nulls.

I could not re-produce your error:

SQL>drop index tcomputer_os_na me
2 /

Index dropped.

SQL>select * from ttcno_complianc e 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_na me on tcomputer(os_na me)
2 /

Index created.

SQL>analyze table tcomputer compute statistics
2 /

Table analyzed.

SQL>select * from ttcno_complianc e 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** · Jun 27 '08, 04:48 PM

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<orcombinat ions, so not your exacly
your query), but who knows.

regards

s.kapitza

owski@hotmail.c om (Adam Ruth) wrote in message news:<f0f51c80. 0306240910.214a 1534@posting.go ogle.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_na me
>
Index dropped
>
analyze table tcomputer compute statistics
>
Table analyzed
>
select * from ttcno_complianc e 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_na me on tcomputer(os_na me)
>
Index created
>
analyze table tcomputer compute statistics
>
Table analyzed
>
select * from ttcno_complianc e 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** · Jun 27 '08, 04:48 PM

Re: Inconsistent Query Results

skapitza@volcan omail.com (s.kapitza) wrote in message news:<26703915. 0306250911.3a2a 8b2d@posting.go ogle.com>...

hello,
>
what patchlevel does your db have ?
>
I remember there was a optimizer bug in Versions < 8.1.7.3
(somthing with <and<orcombinat ions, so not your exacly
your query), but who knows.
>
regards
>
s.kapitza
>
>
owski@hotmail.c om (Adam Ruth) wrote in message news:<f0f51c80. 0306240910.214a 1534@posting.go ogle.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** · Jun 27 '08, 04:49 PM

Re: Inconsistent Query Results

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** · Jun 27 '08, 04:51 PM

Re: Inconsistent Query Results

LKBrwn_DBA <member30625@db forums.comwrote in message news:<3054222.1 056815889@dbfor ums.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

Inconsistent Query Results

Inconsistent Query Results

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment