| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Why are "order by ...desc" results different from "order by ... asc"?
Glenn Heinze wrote:
>
> Connor McDonald wrote in message <37D63FF5.60CB_at_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."
>
> --g--> You are right, we are using 7.3.3 So I guess we can't use the
> "order by ... DESC" clause. Thanks for your reply, it is appreciated
> -Glenn
To see if you are having the bug, explain the query and see if oracle is
using INDEX DESCENDING...If it is, delete the statistics from the table
such that rule optimisation will be used as opposed to cost...this
should resolve it
--
"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 09 1999 - 06:52:25 CDT
 |
 |