Re: index_desc hint question

"Julian Htun" <jhtun_at_kc.rr.com> wrote in message news:3ABD8E4C.6CA9D28C_at_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:
>
> 1) Can I 100% rely on Oracle that it will always use that index and scan
> in a DESC order?
> 2) What effect will I get if somehow analyze is not performed? Will
> Oracle still use this index and scan in DESC order?
> 3) 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
>

1 Oracle 7.3.3 has been desupported ages ago 2 There's no use in analyzing tables and indexes if you are using Rule based optimization. Your statistics will simply be *completely* ignored, and using hints doesn't make any sense at all, as they are going the same route: Oracle ignores them.

Regards,

Sybrand Bakker, Oracle DBA Received on Sun Mar 25 2001 - 01:17:30 CST