Home » RDBMS Server » Performance Tuning » INDEX_DESC Hint (Oracle 10.2.0.3)
INDEX_DESC Hint [message #364394] Mon, 08 December 2008 04:47 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
The INDEX_DESC hint instructs the optimizer to use a descending index scan for the specified table. If the statement uses an index range scan and the index is ascending, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition. For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order. Each parameter serves the same purpose as in "INDEX Hint". For example:

SELECT /*+ INDEX_DESC(e emp_name_ix) */ *
FROM employees e;


what will bethe difference if we use INDEX_DESC with the below query
SELECT /*+ INDEX_DESC(e emp_name_ix) */ *
FROM employees e order by emp_name desc;

Regards,
oli

Re: INDEX_DESC Hint [message #364400 is a reply to message #364394] Mon, 08 December 2008 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what will bethe difference

"order by emp_name desc"

Regards
Michel

[Updated on: Mon, 08 December 2008 05:11]

Report message to a moderator

Re: INDEX_DESC Hint [message #364435 is a reply to message #364400] Mon, 08 December 2008 08:12 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
That means no need to use ORDER BY EMP_NAME DESC
if using INDEX_DESC?
Re: INDEX_DESC Hint [message #364436 is a reply to message #364435] Mon, 08 December 2008 08:25 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No!

ORDER BY is the ONLY way to get a deterministic order.

Regards
Michel
Previous Topic: USE_HASH
Next Topic: temp tablespace stats
Goto Forum:
  


Current Time: Sun Dec 11 02:41:28 CST 2016

Total time taken to generate the page: 0.08124 seconds