Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 7.3.3 for NT BUG?

Re: Oracle 7.3.3 for NT BUG?

From: <jhy_at_earthling.net>
Date: 24 Sep 1998 17:08:59 GMT
Message-ID: <360A7CAC.2B97A2DF@earthling.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US