Re: Why are "order by ...desc" results different from "order by ... asc"?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 08 Sep 1999 18:52:37 +0800
Message-ID: <37D63FF5.60CB@yahoo.com>

Glenn Heinze wrote:
>
> When selecting from a table with no "order by" clause, there are 2 rows
> returned.
> Same select with an "order by ... ASC" returns same two rows.
> Same select with an "order by ... DESC" returns 20 rows.
> If the index is dropped, 2 rows are returned in all cases.
> There is only one index on the table.
> Is something is wrong with index, or what am I missing?!!
>
> Thanks in advance for any help,
> -Glenn
>
> ==================================
> SQL> select rental_id, amendment_counter
> 2 from CLASS.EDL_RENTAL_AMENDMENT
> 3 where rental_id = 6200
> 4 and amendment_counter > 0;
>
> RENTAL_ID AMENDMENT_COUNTER
> ---------- -----------------
> 6200 1
> 6200 2
>
> ==================================
> SQL> select rental_id, amendment_counter
> 2 from CLASS.EDL_RENTAL_AMENDMENT
> 3 where rental_id = 6200
> 4 and amendment_counter > 0
> 5 order by amendment_counter asc;
>
> RENTAL_ID AMENDMENT_COUNTER
> ---------- -----------------
> 6200 1
> 6200 2
>
> ==================================
> SQL> select rental_id, amendment_counter
> 2 from CLASS.EDL_RENTAL_AMENDMENT
> 3 where rental_id = 6200
> 4 and amendment_counter > 0
> 5 order by amendment_counter desc;
>
> RENTAL_ID AMENDMENT_COUNTER
> ---------- -----------------
> 6294 1
> 6292 1
> 6287 1
> 6279 1
> 6273 8
> 6273 7
> 6273 6
> 6273 5
> 6273 4
> 6273 3
> 6273 2
>
> RENTAL_ID AMENDMENT_COUNTER
> ---------- -----------------
> 6273 1
> 6272 1
> 6266 1
> 6265 1
> 6261 1
> 6224 1
> 6210 1
> 6200 2
> 6200 1
>
> 20 rows selected.
>
> ==================================
> SQL> select count(*)
> 2 from CLASS.EDL_RENTAL_AMENDMENT
> 3 where rental_id = 6200
> 4 and amendment_counter > 0;
>
> COUNT(*)
> ----------
> 2
>
> ==================================
> CREATE UNIQUE INDEX class.xpkrental_amendment
> ON class.edl_rental_amendment
> ( rental_id,
> amendment_counter )
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> TABLESPACE index_space
> STORAGE (
> INITIAL 40960
> NEXT 90112
> PCTINCREASE 1
> MINEXTENTS 1
> MAXEXTENTS 505
> )

What version are you on ?

There were some bugs with 7.3.x when oracle chose to select from an index in descending order...
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Wed Sep 08 1999 - 05:52:37 CDT