pradeepwebonise

/*  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 

pradeepwebonise

/*  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 

pradeepwebonise

/*  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 

pradeepwebonise

-- getting all tournaments with tournament ids to which user had made entries-------------

EXPLAIN PLAN FOR
SELECT * FROM (   
   SELECT rownum as rn, a.* FROM (
       SELECT * FROM Tournament t WHERE t.published = 1 AND t.closed = 0 AND t.cancelled = 0 AND t.submission_deadline >= '20-05-15 06:45:00.000000000 AM' AND ( t.is_private = 0 OR (t.tournament_id IN (132, 131) OR t.allowed_user_emails LIKE '%bkhanna@drf.com') ) ORDER BY submission_deadline ASC 
   ) a
) WHERE rownum <= 10 AND rn >  (1 - 1) * 10

============ Output ========================

pradeepwebonise

/* All tournaments for non logged in users */


 EXPLAIN PLAN FOR
  SELECT * FROM (   
   SELECT rownum as rn, a.* FROM (
       SELECT * FROM Tournament t WHERE t.published = 1 AND t.closed = 0 AND t.cancelled = 0 AND t.submission_deadline >= '07/21/2015 10:30 AM' AND t.is_private = 0 ORDER BY submission_deadline ASC 
   ) a
) WHERE rownum <= 10 AND rn >  (1 - 1) * 10

pradeepwebonise

/*  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