Re: SQL help

On Wed, 29 Oct 1997 21:32:37 GMT, "Dan Clamage" <clamage_at_mime.dw.lucent.com> wrote:

>> select id, date
>> from visits
>> where rank = (select max(rank) from visits);
>> If there are several rows with the same highest rank, they will all be
>> selected.
>Explain Plan for this with no index:
>SELECT STATEMENT
> FILTER
> TABLE ACCESS FULL VISITS
> SORT AGGREGATE
> TABLE ACCESS FULL VISITS
>Explain Plan with index on rank, date, id:
>SELECT STATEMENT
> INDEX RANGE SCAN RANK_IDX
> SORT AGGREGATE
> INDEX RANGE SCAN RANK_IDX
>
>If you use a hint,
>select /*+ INDEX_DESC (VISITS RANK_IDX) */
> id, date
>FROM visits
>WHERE rownum = 1;
>Will return the first row with the highest rank, date, id and stop.
>The explain plan is:
>SELECT STATEMENT
> COUNT STOPKEY
> INDEX RANGE SCAN DESCENDING RANK_IDX
>Which is more efficient than the former, requiring only one scan and no
>sort.
>- djc

There's too many unaswered questions to know whether an index is appropriate or not. That's why I give a generic answer.

1. Is there even an index on table?
2. How big is the table. If it's tiny an index could INCREASE the total query time. Who cares about using an index if the entire statement completes with 1 or 2 physical reads?
3. Is it a one time job? If so, creating the index will introduce the same full scan and sort that you're trying to avoid. In addition to the overhead of allocating space for the index, writing it, etc.
4. How frequently is the table updated? Each index on a table increases the time it takes for each update to complete. Is the gain worth the cost in this case?

Like I said, too many unanswered questions.

--
Chuck Hamilton
Xchuckh_at_dvol.com
In an effort to reduce spam my return email address hasbeen changed.
To reply, remove the X from the beginning.

If at first you don't succeed, sky diving isn't for you.