> 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
Received on Wed Oct 29 1997 - 00:00:00 CST
