index_desc hint question

From: Julian Htun <jhtun_at_kc.rr.com>
Date: Sun, 25 Mar 2001 06:21:27 GMT
Message-ID: <3ABD8E4C.6CA9D28C@kc.rr.com>

Hi Gurus:

I'm using Oracle 7.3.3 and Rule Base optimization as default. I can do analyze with estimate statistics once a month. I have this index and assuming that I must use this index, nothing else. For each sale_person_id there are apprx 100K rows.

index sale_index
{

    pos 1: sale_person_id
    pos 2: sale_amount
    pos 3: sale_date

}

I want to find the top 10 sale_amount for this person for a specific date. The fastest way I found is without using SORT rather using INDEX_DESC. select * from
(
select /*+ INDEX_DESC(sale_index) */ sale_amount from

sale
where

sale_person_id = :1 and sale_date = :2 )
where rownum <= 10;

Here's the questions:

Can I 100% rely on Oracle that it will always use that index and scan in a DESC order?
What effect will I get if somehow analyze is not performed? Will Oracle still use this index and scan in DESC order?
Can you guys think of any reason why and how Oracle might not scan the index in DESC order? This is a pretty important system (sale is just an example), and out of order is really bad.

Thank you so much.
-Julian Received on Sun Mar 25 2001 - 00:21:27 CST