All tournaments with tournament ids to which user had made entries [ with indexing ]

gistfile1.txt

-- 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 ========================

Plan hash value: 1704707986
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |     6 | 49596 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                  |                          |       |       |            |          |
|*  2 |   VIEW                          |                          |     6 | 49596 |     3   (0)| 00:00:01 |
|   3 |    COUNT                        |                          |       |       |            |          |
|   4 |     VIEW                        |                          |     6 | 49518 |     3   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| TOURNAMENT               |     6 |  2910 |     3   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | SUBMISSION_PUBLISH_CLOSE |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=10)
   2 - filter("RN">0)
   5 - filter("T"."CANCELLED"=0 AND "T"."IS_PRIVATE"=0)
   6 - access("T"."PUBLISHED"=1 AND "T"."CLOSED"=0 AND 
              "T"."SUBMISSION_DEADLINE">=TO_TIMESTAMP('07/21/2015 10:30 AM') AND "T"."SUBMISSION_DEADLINE" IS NOT 
              NULL)