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

From: Glenn Heinze <glenn.heinze_at_city.kitchener.on.ca>
Date: Wed, 8 Sep 1999 09:53:07 -0400
Message-ID: <7r5q5m$nrv$1@cougar.golden.net>

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
   ) Received on Wed Sep 08 1999 - 08:53:07 CDT