Re: Specifying an INDEX in a SELECT statement

On Thu, 29 Jan 1998 22:07:59 GMT, "Chuck Bayes" <chuck.bayes_at_compaq.com> wrote:

>I know it's a simple task (or at least it is in Sybase), but can anyone
>tell me how to force a query to use a specific index.

Well - one of the majour advanges of SQL was that as a programmer you weren't suppose to think about indexes no more (yeah I know - please don't drop dead laughing!).

Oracle uses hints for that purpose. Look at the index hint (rather obvious huh?). But I would recommend you to take a look at the optimizer manual (Developers guide) because the hint system is rather complex. You can choose to scan ascending, lookup or deselect indexes - you have a lot of options. This is one example of usage of the above INDEX hint:

select /*+ index(idx_a_special_index) */ col1, col2, col3 from many_index_table
where col3='<something>';

But I would say that in most cases Oracle handles this pretty fine. Only in advanced situations where the optimizer might be confused or you have too old information in your table analize data, the force of rulebased or index usage can be of any help.

Anyway, always try to analize all your tables before enforcing an index. Always bear in mind, that in normal situations, if you scan more than 10% of the total set of values in your table, a full table scan usually performs FASTER than an indexscan. Indexes ARE an overhead.

Regards
Peter H. Larsen (petlars_at_pip.dknet.dk)
Oracle Consultant (not affiliated with Oracle) Received on Fri Jan 30 1998 - 00:00:00 CST