Leaderboard Data query without 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: 3492330374
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |     4 |   640 |    19  (16)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |                        |       |       |            |          |
|*  2 |   VIEW                               |                        |     4 |   640 |    19  (16)| 00:00:01 |
|   3 |    COUNT                             |                        |       |       |            |          |
|   4 |     VIEW                             |                        |     4 |   588 |    19  (16)| 00:00:01 |
|   5 |      SORT ORDER BY                   |                        |     4 |   372 |    19  (16)| 00:00:01 |
|   6 |       HASH GROUP BY                  |                        |     4 |   372 |    19  (16)| 00:00:01 |
|*  7 |        HASH JOIN OUTER               |                        |     4 |   372 |    17   (6)| 00:00:01 |
|*  8 |         HASH JOIN                    |                        |     2 |   170 |    12   (9)| 00:00:01 |
|   9 |          NESTED LOOPS                |                        |       |       |            |          |
|  10 |           NESTED LOOPS               |                        |     1 |    77 |     4   (0)| 00:00:01 |
|* 11 |            TABLE ACCESS FULL         | TOURNAMENT_USERS_ENTRY |     1 |    14 |     3   (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 |          TABLE ACCESS FULL           | TOURNAMENT_ENTRY_PICK  |  2121 | 16968 |     7   (0)| 00:00:01 |
|  15 |         TABLE ACCESS FULL            | PICK_PAYOUT            |  5179 | 41432 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=10)
   2 - filter("RN">0)
   7 - access("TEP"."PICK_ID"="P"."PICK_ID"(+))
   8 - access("TEP"."ENTRY_ID"="TUE"."ENTRY_ID")
  11 - filter("TUE"."TOURNAMENT_ID"=110)
  12 - access("U"."USER_ID"="TUE"."USER_ID")