Order by in cursor?

Hi,

Can order by be used in a cursor? How to create an index to order the column in descending order so that I can eliminate using order by in select statement? Thanks!

what is the version of your DB ???
1) yes, order by can be used in a cursor
2) an index is always created with ascending values, nevertheless you can build an index and use the INDEX_DESC hint to have Oracle use it in descending order

Quote:

---

Originally posted by pipo
2) an index is always created with ascending values, nevertheless you can build an index and use the INDEX_DESC hint to have Oracle use it in descending order

---

In 8.1.7 (I'm not sure about previous releases of 8i) you can create index in DESCending oreder and Oracle will actually create it in descending order of values. However oRACLE treats it as a kind of function based index, so it is only able to use it if you use cost based optimizer and the index and the table has been analized.

CREATE INDEX emp_sal ON scott.emp (sal DESC);

in 7 you could already do that, but it was only for DB2 syntax compatibility (as said in doc)

Yes, but index was created in ASCending order, like any other index. But now in 8i index is actualy created in DESCending order and optimizer can use it without any hints.

Hi,

I tried to create a desc index. However when I try to select from the table, the column is not in descending order. I have tried using the hint INDEX DESC too, but it is not working. My database version is Oracle 8.1.5.0.0. Thanks.

It should work with 8.1.5, I've just checked the docs. Have you analyze the table and the index? What is your optimizer mode?

thx for the info Jurij ;)
BTW, it's often the same problem : when asking a question, please include OS, RDBMS version, and don't forget optimizer mode and last_analyzed if related to the problem :]

You can create a view of the table that matches it exactly except that you have it in desc order. I had to do this for one of my programs.