Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 7.3.3 for NT BUG?
Yeah, how about that. I saw something that sounds like the same thing. Here's a
simple script that reproduces the problem I saw on Oracle NT 7.3.3. If you drop
the index or delete the statistics then the query works correctly.
The problem is fixed in 7.3.4. I think Oracle NT 8.0.3 also has the same problem, and I also believe it was fixed in 8.0.4.
DROP TABLE TEST; CREATE TABLE TEST (
SEQ NUMBER(10), LASTMODIFIED DATE, LASTUSER VARCHAR2(30), GROUP_NUM NUMBER(10), PROBLEM_NUM NUMBER(10), PDATE DATE
INSERT INTO TEST VALUES (0, SYSDATE, 'SMITH', 0, 1200, SYSDATE); INSERT INTO TEST VALUES (1, SYSDATE, 'SMITH', 0, 1201, SYSDATE); CREATE INDEX IDX ON TEST (PROBLEM_NUM, SEQ); ANALYZE TABLE TEST COMPUTE STATISTICS;
SELECT SEQ, PROBLEM_NUM
FROM TEST
WHERE PROBLEM_NUM = 1200
ORDER BY SEQ DESC;
ryanm6741_at_my-dejanews.com wrote:
> I have a strange situation that I believe is an Oracle Bug...
>
> I have two tables (t1, t2 for this example) on which I have analyzed with
> compute statistics. Running a query such as:
>
> select t1.c1 from t1, t2
> where t1.c2=t2.c2 and
> t1.c1=27000
> order by t1.c3 desc
>
> ...I should only get one resulting row. When I run this query, I get results
> that are really WHACKY!!! Removing the 'desc', droping the indexes, or
> deleting statistics results in valid results. Otherwise, I get 100+ rows
> resulting with data that does not look as if it came from the selected table.
>
> What could be going on? Is this a know issue; is it resolved in 7.3.4?
>
> This makes NO sense to me...HELP!
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Thu Sep 24 1998 - 12:08:59 CDT