Oracle Database Discussions

I'm using a slight modification of the pagination query from over at Ask Tom's: [http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html]

Mine looks like this when fetching the first 100 rows of all members with last name Smith, ordered by join date:

SELECT members.*
FROM members,
(
    SELECT RID, rownum rnum
    FROM
    (
        SELECT rowid as RID 
        FROM members
        WHERE last_name = 'Smith'
        ORDER BY joindate
    ) 
    WHERE rownum <= 100 
) 
WHERE rnum >= 1 
         and RID = members.rowid

The difference between this and the one at Ask Tom's is that my innermost query just returns the ROWID. Then in the outermost query we join the ROWIDs returned to the members table, after we have pruned the ROWIDs down to only the chunk of 100 we want. This makes it MUCH faster (verifiably) on our large tables, as it is able to use the index on the innermost query (well... read on).
The problem I have is this:

SELECT rowid as RID 
FROM members
WHERE last_name = 'Smith'
ORDER BY joindate

This will use the index for the predicate column (last_name) instead of the unique index I have defined for the joindate column (joindate, sequence). (Verifiable with explain plan). It is much slower this way on a large table. So I can hint it using either of the following methods:

SELECT /*+ index(members, joindate_idx) */ rowid as RID 
FROM members
WHERE last_name = 'Smith'
ORDER BY joindate

SELECT /*+ first_rows(100) */ rowid as RID 
FROM members
WHERE last_name = 'Smith'
ORDER BY joindate

Either way, it now uses the index of the ORDER BY column (joindate_idx), so now it is much faster as it does not have to do a sort (remember, VERY large table, millions of records). So that seems good. But now, on my outermost query, I join the rowid with the meaningful columns of data from the members table, as commented below:

SELECT members.*      -- Select all data from members table
FROM members,           -- members table added to FROM clause 
(
    SELECT RID, rownum rnum
    FROM
    (
        SELECT /*+ index(members, joindate_idx) */ rowid as RID   -- Hint is ignored now that I am joining in the outer query
        FROM members
        WHERE last_name = 'Smith'
        ORDER BY joindate
    ) 
    WHERE rownum <= 100 
) 
WHERE rnum >= 1 
        and RID = members.rowid           -- Merge the members table on the rowid we pulled from the inner queries

Once I do this join, it goes back to using the predicate index (last_name) and has to perform the sort once it finds all matching values (which can be a lot in this table, there is high cardinality on some columns).

So my question is, in the full query above, is there any way I can get it to use the ORDER BY column for indexing to prevent it from having to do a sort? The join is what causes it to revert back to using the predicate index, even with hints. Remove the join and just return the ROWIDs for those 100 records and it flies, even on 10 million records.

It'd be great if there was some generic hint that could accomplish this, such that if we change the table/columns/indexes, we don't need to change the hint (the FIRST_ROWS hint is a good example of this, while the INDEX hint is the opposite), but any help would be appreciated. I can provide explain plans for any of the above if needed.

Thanks!