Re: Oracle 7.3.3 for NT BUG?

We ran into the same problem with two different production databases. It's bug # 511898.
The optimizer screws up the results when it uses a concatenated index in a descending
order while a where clause was set on the index leading part. As a workaround, both
offending indexes were dropped, but an upgrade to 7.3.4 is better.

According to Oracle's bug database, it was solved with 7.3.3.3.

sleclerc_at_magrit.com

> -----Original Message-----
> From: jhy_at_earthling.net [SMTP:jhy_at_earthling.net]
> Posted At: Thursday, September 24, 1998 1:09 PM
> Posted To: server
> Conversation: Oracle 7.3.3 for NT BUG?
> Subject: 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.
>
>
> -- Demonstrate 7.3.3 bug
>
> 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;
>
> -- This should only return one row
>
> 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 Fri Sep 25 1998 - 07:29:36 CDT