Leaderboard Data query with indexing

gistfile1.txt

/*  Leaderboard Data query */

EXPLAIN PLAN FOR 
SELECT userId, firstName, lastName, userName, email, entryId, totalPayouts FROM (   
     SELECT rownum as rn, a.* FROM (
         SELECT u.user_id userId, u.first_name firstName, u.last_name lastName, 
             u.user_name userName, tep.entry_id entryId, 
             NVL( SUM(p.payouts), 0 ) totalPayouts ,u.email email 
         FROM USERS u, TOURNAMENT_USERS_ENTRY tue, TOURNAMENT_ENTRY_PICK tep 
             LEFT JOIN PICK_PAYOUT p ON tep.pick_id = p.pick_id 
         WHERE u.user_id = tue.user_id AND 
             tue.tournament_id = 110 AND 
             tep.entry_id = tue.entry_id 
         GROUP BY u.user_id, u.first_name, u.last_name, u.user_name, tep.entry_id , u.email  
         ORDER BY totalPayouts DESC, firstName, lastName 
     ) a
  ) WHERE rownum <= 10 AND rn >  (1 - 1) * 10;

  
  ==================================== OUTPUT ==============================================
  
  Plan hash value: 1089195683
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                               |     4 |   640 |    14  (22)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |                               |       |       |            |          |
|*  2 |   VIEW                               |                               |     4 |   640 |    14  (22)| 00:00:01 |
|   3 |    COUNT                             |                               |       |       |            |          |
|   4 |     VIEW                             |                               |     4 |   588 |    14  (22)| 00:00:01 |
|   5 |      SORT ORDER BY                   |                               |     4 |   372 |    14  (22)| 00:00:01 |
|   6 |       HASH GROUP BY                  |                               |     4 |   372 |    14  (22)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER            |                               |     4 |   372 |    12   (9)| 00:00:01 |
|*  8 |         HASH JOIN                    |                               |     2 |   170 |     8  (13)| 00:00:01 |
|   9 |          NESTED LOOPS                |                               |       |       |            |          |
|  10 |           NESTED LOOPS               |                               |     1 |    77 |     3   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | TOURNAMENT_USER_ENTRY_ID      |     1 |    14 |     2   (0)| 00:00:01 |
|* 12 |            INDEX UNIQUE SCAN         | SYS_C0021928                  |     1 |       |     0   (0)| 00:00:01 |
|  13 |           TABLE ACCESS BY INDEX ROWID| USERS                         |     1 |    63 |     1   (0)| 00:00:01 |
|  14 |          INDEX FAST FULL SCAN        | TOURNAMENT_USER_ENTRY_PICK_ID |  2121 | 16968 |     4   (0)| 00:00:01 |
|  15 |         TABLE ACCESS BY INDEX ROWID  | PICK_PAYOUT                   |     2 |    16 |     3   (0)| 00:00:01 |
|* 16 |          INDEX RANGE SCAN            | TOURNAMENT_PICK_PAYOUT_ID1    |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=10)
   2 - filter("RN">0)
   8 - access("TEP"."ENTRY_ID"="TUE"."ENTRY_ID")
  11 - access("TUE"."TOURNAMENT_ID"=110)
  12 - access("U"."USER_ID"="TUE"."USER_ID")
  16 - access("TEP"."PICK_ID"="P"."PICK_ID"(+))