title: How to optimize a complex oracle query using pagination
body: <p>I need to get data from an oracle DB to paint some sort of matrix in an html table. The query I needed turned out to be very complex and slow, so I decided to implement pagination.</p>
  
  <p>I followed many articles and posts, but the one I liked the most was this one: <a href="https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results" rel="nofollow noreferrer">On rownum and limiting results</a></p>
  
  <p>But it doesn't seem to work, the execution takes as long time as if there was no rows restriction, sometimes it even takes longer.</p>
  
  <p>Please keep in mind than I'm new with oracle, I'm used to recent versions of MS SQL</p>
  
  <p>Here's a brief example of what my query looks like:</p>
  
  <pre><code>    SELECT * FROM (
          SELECT /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum FROM (
              WITH T1 AS (
                  SELECT ... FROM VIEW_1
                  INNER JOIN ...
                  WHERE ...
                  GROUP BY ...
              ),
              T2 AS (
                  SELECT ... FROM VIEW_2
                  INNER JOIN ...
                  WHERE ...
                  GROUP BY ...
              ),
              T3 AS (
                  SELECT ... FROM VIEW_3
                  LEFT JOIN ...
                  WHERE ...
                  GROUP BY ...
              )
              SELECT
                  T1.LVL,
                  T1.CRSE,
                  T2.ID,
                  T3.GRADE
              FROM T1
              INNER JOIN T2 ON ...
  
              LEFT JOIN T3 ON ...
  
              ORDER BY T2.ID DESC, T1.LVL, T1.CRSE) a
          WHERE ROWNUM &lt;= (PAGE * 50))
      WHERE rnum &gt;= ((PAGE-1) * 50);
  </code></pre>
  
  <p>My actual query is quite larger, and I mostly use views I don't have access to.</p>
  
  <p>I don't know if the /*+ FIRST_ROWS(n) */ works like that... and also don't think I can set a variable there...</p>
  
  <p>I was expecting the execution time to be much lower that without the ROWNUM check, but it just doesn't work, and I've read its because of the GROUP BY usage. Can someone please help me make my pagination faster?</p>
  
  <p>EDIT:
  Here's the EXPLAIN PLAN from my query, I hope it helps:</p>
  
  <pre><code>    Plan hash value: 3394899150
  
  -----------------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                                   | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                            |                             |  3950 |   648K|       | 17918   (1)| 00:03:36 |
  |*  1 |  VIEW                                       |                             |  3950 |   648K|       | 17918   (1)| 00:03:36 |
  |*  2 |   COUNT STOPKEY                             |                             |       |       |       |            |          |
  |   3 |    VIEW                                     |                             |  8424 |  1275K|       | 17918   (1)| 00:03:36 |
  |   4 |     TEMP TABLE TRANSFORMATION               |                             |       |       |       |            |          |
  |   5 |      LOAD AS SELECT                         | SYS_TEMP_0FD9D6FD2_17D15EA9 |       |       |       |            |          |
  |   6 |       HASH UNIQUE                           |                             |    36 |  2052 |       |   250   (1)| 00:00:03 |
  |   7 |        VIEW                                 | PS_UAG_ISBI_MAT_VW          |    36 |  2052 |       |   249   (1)| 00:00:03 |
  |*  8 |         HASH JOIN                           |                             |    36 |  3744 |       |   249   (1)| 00:00:03 |
  |   9 |          NESTED LOOPS                       |                             |    34 |  2312 |       |    14   (0)| 00:00:01 |
  |  10 |           NESTED LOOPS                      |                             |    44 |  2312 |       |    14   (0)| 00:00:01 |
  |  11 |            NESTED LOOPS                     |                             |     1 |    46 |       |     5   (0)| 00:00:01 |
  |  12 |             TABLE ACCESS BY INDEX ROWID     | PS_ACAD_PROG_TBL            |     1 |    29 |       |     4   (0)| 00:00:01 |
  |* 13 |              INDEX SKIP SCAN                | PS1ACAD_PROG_TBL            |     1 |       |       |     3   (0)| 00:00:01 |
  |* 14 |             INDEX RANGE SCAN                | PS1ACAD_ORG_TBL             |     1 |    17 |       |     1   (0)| 00:00:01 |
  |* 15 |            INDEX RANGE SCAN                 | PS2CRSE_OFFER               |    44 |       |       |     1   (0)| 00:00:01 |
  |* 16 |           TABLE ACCESS BY INDEX ROWID       | PS_CRSE_OFFER               |    33 |   726 |       |     9   (0)| 00:00:01 |
  |  17 |          VIEW                               |                             | 12227 |   429K|       |   235   (1)| 00:00:03 |
  |* 18 |           HASH JOIN RIGHT OUTER             |                             | 12227 |   573K|       |   235   (1)| 00:00:03 |
  |* 19 |            INDEX FAST FULL SCAN             | PS_CRSE_ATTRIBUTES          |  7690 |   135K|       |    98   (0)| 00:00:02 |
  |  20 |            TABLE ACCESS FULL                | PS_CRSE_CATALOG             | 12227 |   358K|       |   136   (0)| 00:00:02 |
  |  21 |      LOAD AS SELECT                         | SYS_TEMP_0FD9D6FD3_17D15EA9 |       |       |       |            |          |
  |  22 |       VIEW                                  |                             |   682 | 36146 |       |  1091   (1)| 00:00:14 |
  |* 23 |        HASH JOIN                            |                             |   682 | 41602 |       |  1091   (1)| 00:00:14 |
  |* 24 |         TABLE ACCESS FULL                   | TYM_TRAYECTORIA             |   682 | 17050 |       |   513   (1)| 00:00:07 |
  |  25 |         INDEX FAST FULL SCAN                | PS0PERSONAL_DATA            |   255K|  8985K|       |   577   (1)| 00:00:07 |
  |  26 |      LOAD AS SELECT                         | SYS_TEMP_0FD9D6FD4_17D15EA9 |       |       |       |            |          |
  |  27 |       HASH GROUP BY                         |                             |   107 |  1605 |       | 16164   (1)| 00:03:14 |
  |  28 |        VIEW                                 |                             |   107 |  1605 |       | 16163   (1)| 00:03:14 |
  |* 29 |         HASH JOIN                           |                             |   107 |  2461 |       | 16163   (1)| 00:03:14 |
  |  30 |          VIEW                               |                             |   682 |  5456 |       |     3   (0)| 00:00:01 |
  |  31 |           TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6FD3_17D15EA9 |   682 | 36146 |       |     3   (0)| 00:00:01 |
  |  32 |          VIEW                               | PS_UAG_ISBI_CAL_VW          |  6411 | 96165 |       | 16160   (1)| 00:03:14 |
  |  33 |           SORT UNIQUE                       |                             |  6411 |  7004K|  1960K| 16160   (1)| 00:03:14 |
  |  34 |            UNION-ALL                        |                             |       |       |       |            |          |
  |* 35 |             HASH JOIN                       |                             |  6342 |  1839K|       | 15289   (1)| 00:03:04 |
  |* 36 |              INDEX FAST FULL SCAN           | PS_CRSE_ATTRIBUTES          |  7690 |   135K|       |    98   (0)| 00:00:02 |
  |* 37 |              HASH JOIN                      |                             |  4943 |  1346K|       | 15191   (1)| 00:03:03 |
  |* 38 |               HASH JOIN                     |                             |  4943 |  1168K|       | 10790   (1)| 00:02:10 |
  |  39 |                INDEX FAST FULL SCAN         | PS1TERM_TBL                 |  1256 | 31400 |       |     4   (0)| 00:00:01 |
  |* 40 |                HASH JOIN                    |                             |  4943 |  1047K|       | 10786   (1)| 00:02:10 |
  |  41 |                 INDEX FAST FULL SCAN        | PS0CRSE_CATALOG             | 12227 |   358K|       |    31   (0)| 00:00:01 |
  |* 42 |                 HASH JOIN                   |                             |  4985 |   910K|       | 10755   (1)| 00:02:10 |
  |  43 |                  TABLE ACCESS FULL          | PS_ACAD_ORG_TBL             |   549 | 23058 |       |     7   (0)| 00:00:01 |
  |* 44 |                  HASH JOIN                  |                             |  5297 |   750K|  2856K| 10748   (1)| 00:02:09 |
  |  45 |                   TABLE ACCESS FULL         | PS_CLASS_TBL                | 52112 |  2239K|       |   650   (1)| 00:00:08 |
  |* 46 |                   HASH JOIN                 |                             |   100K|  9893K|       |  9422   (1)| 00:01:54 |
  |  47 |                    TABLE ACCESS FULL        | PS_ACAD_PROG_TBL            |   313 | 18467 |       |     5   (0)| 00:00:01 |
  |* 48 |                    TABLE ACCESS FULL        | PS_STDNT_ENRL               |   986K|    39M|       |  9414   (1)| 00:01:53 |
  |  49 |               TABLE ACCESS FULL             | PS_PERSONAL_DATA            |   255K|  9235K|       |  4400   (1)| 00:00:53 |
  |  50 |             NESTED LOOPS                    |                             |    69 | 19596 |       |   462   (1)| 00:00:06 |
  |  51 |              NESTED LOOPS                   |                             |    69 | 19596 |       |   462   (1)| 00:00:06 |
  |* 52 |               HASH JOIN                     |                             |    69 | 17043 |       |   324   (1)| 00:00:04 |
  |* 53 |                HASH JOIN                    |                             |    69 | 15318 |       |   320   (1)| 00:00:04 |
  |* 54 |                 HASH JOIN                   |                             |   105 | 21420 |       |   222   (1)| 00:00:03 |
  |* 55 |                  HASH JOIN                  |                             |   101 | 17574 |       |   191   (1)| 00:00:03 |
  |* 56 |                   HASH JOIN                 |                             |   101 | 14443 |       |    88   (0)| 00:00:02 |
  |* 57 |                    HASH JOIN                |                             |   314 | 31714 |       |    12   (0)| 00:00:01 |
  |  58 |                     TABLE ACCESS FULL       | PS_ACAD_PROG_TBL            |   313 | 18467 |       |     5   (0)| 00:00:01 |
  |  59 |                     TABLE ACCESS FULL       | PS_ACAD_ORG_TBL             |   549 | 23058 |       |     7   (0)| 00:00:01 |
  |  60 |                    VIEW                     |                             |  7540 |   309K|       |    76   (0)| 00:00:01 |
  |* 61 |                     HASH JOIN               |                             |  7540 |   493K|       |    76   (0)| 00:00:01 |
  |  62 |                      TABLE ACCESS FULL      | PS_TRNS_CRSE_SCH            |   597 | 16119 |       |     6   (0)| 00:00:01 |
  |  63 |                      TABLE ACCESS FULL      | PS_TRNS_CRSE_DTL            |  7540 |   294K|       |    70   (0)| 00:00:01 |
  |  64 |                   TABLE ACCESS FULL         | PS_CRSE_OFFER               | 12227 |   370K|       |   102   (0)| 00:00:02 |
  |  65 |                  INDEX FAST FULL SCAN       | PS0CRSE_CATALOG             | 12227 |   358K|       |    31   (0)| 00:00:01 |
  |* 66 |                 INDEX FAST FULL SCAN        | PS_CRSE_ATTRIBUTES          |  7690 |   135K|       |    98   (0)| 00:00:02 |
  |  67 |                INDEX FAST FULL SCAN         | PS1TERM_TBL                 |  1256 | 31400 |       |     4   (0)| 00:00:01 |
  |* 68 |               INDEX UNIQUE SCAN             | PS_PERSONAL_DATA            |     1 |       |       |     1   (0)| 00:00:01 |
  |  69 |              TABLE ACCESS BY INDEX ROWID    | PS_PERSONAL_DATA            |     1 |    37 |       |     2   (0)| 00:00:01 |
  |* 70 |      SORT ORDER BY STOPKEY                  |                             |  8424 |  1628K|  1736K|   413   (1)| 00:00:05 |
  |* 71 |       HASH JOIN RIGHT OUTER                 |                             |  8424 |  1628K|       |    46   (5)| 00:00:01 |
  |  72 |        VIEW                                 |                             |   108 |  2700 |       |     2   (0)| 00:00:01 |
  |  73 |         TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6FD4_17D15EA9 |   108 |  1620 |       |     2   (0)| 00:00:01 |
  |* 74 |        HASH JOIN RIGHT OUTER                |                             |    78 | 13494 |       |    44   (5)| 00:00:01 |
  |  75 |         VIEW                                |                             |    26 |   390 |       |    24   (5)| 00:00:01 |
  |  76 |          HASH GROUP BY                      |                             |    26 |   702 |       |    24   (5)| 00:00:01 |
  |  77 |           VIEW                              |                             |    47 |  1269 |       |    24   (5)| 00:00:01 |
  |  78 |            HASH GROUP BY                    |                             |    47 |  2256 |       |    24   (5)| 00:00:01 |
  |* 79 |             HASH JOIN OUTER                 |                             |    47 |  2256 |       |    23   (0)| 00:00:01 |
  |* 80 |              HASH JOIN OUTER                |                             |    47 |  1081 |       |    21   (0)| 00:00:01 |
  |  81 |               VIEW                          |                             |    36 |   360 |       |     2   (0)| 00:00:01 |
  |  82 |                TABLE ACCESS FULL            | SYS_TEMP_0FD9D6FD2_17D15EA9 |    36 |  2052 |       |     2   (0)| 00:00:01 |
  |  83 |               TABLE ACCESS FULL             | PS_RQ_GRP_DETL_TBL          |  2702 | 35126 |       |    19   (0)| 00:00:01 |
  |  84 |              VIEW                           |                             |   108 |  2700 |       |     2   (0)| 00:00:01 |
  |  85 |               TABLE ACCESS FULL             | SYS_TEMP_0FD9D6FD4_17D15EA9 |   108 |  1620 |       |     2   (0)| 00:00:01 |
  |* 86 |         HASH JOIN RIGHT OUTER               |                             |    78 | 12324 |       |    20   (5)| 00:00:01 |
  |  87 |          TABLE ACCESS FULL                  | PS_UAG_PRECARG_SUBJECT      |    12 |   168 |       |     3   (0)| 00:00:01 |
  |* 88 |          HASH JOIN RIGHT OUTER              |                             |    78 | 11232 |       |    17   (6)| 00:00:01 |
  |  89 |           VIEW                              |                             |     1 |    20 |       |    12   (9)| 00:00:01 |
  |  90 |            HASH GROUP BY                    |                             |     1 |    30 |       |    12   (9)| 00:00:01 |
  |  91 |             VIEW                            | PS_UAG_OFERCRSE_VW          |     1 |    30 |       |    12   (9)| 00:00:01 |
  |  92 |              HASH UNIQUE                    |                             |     1 |   213 |       |    12   (9)| 00:00:01 |
  |  93 |               NESTED LOOPS OUTER            |                             |     1 |   213 |       |    11   (0)| 00:00:01 |
  |  94 |                NESTED LOOPS OUTER           |                             |     1 |   177 |       |     9   (0)| 00:00:01 |
  |  95 |                 NESTED LOOPS OUTER          |                             |     1 |   139 |       |     6   (0)| 00:00:01 |
  |* 96 |                  TABLE ACCESS BY INDEX ROWID| PS_CLASS_TBL                |     1 |    69 |       |     4   (0)| 00:00:01 |
  |* 97 |                   INDEX RANGE SCAN          | PSDCLASS_TBL                |     1 |       |       |     3   (0)| 00:00:01 |
  |  98 |                  TABLE ACCESS BY INDEX ROWID| PS_CLASS_MTG_PAT            |     1 |    70 |       |     2   (0)| 00:00:01 |
  |* 99 |                   INDEX RANGE SCAN          | PS_CLASS_MTG_PAT            |     1 |       |       |     1   (0)| 00:00:01 |
  | 100 |                 TABLE ACCESS BY INDEX ROWID | PS_CLASS_INSTR              |     1 |    38 |       |     3   (0)| 00:00:01 |
  |*101 |                  INDEX RANGE SCAN           | PS_CLASS_INSTR              |     1 |       |       |     2   (0)| 00:00:01 |
  | 102 |                TABLE ACCESS BY INDEX ROWID  | PS_PERSONAL_DATA            |     1 |    36 |       |     2   (0)| 00:00:01 |
  |*103 |                 INDEX UNIQUE SCAN           | PS_PERSONAL_DATA            |     1 |       |       |     1   (0)| 00:00:01 |
  | 104 |           VIEW                              |                             |    78 |  9672 |       |     5   (0)| 00:00:01 |
  |*105 |            HASH JOIN                        |                             |    78 | 10140 |       |     5   (0)| 00:00:01 |
  | 106 |             VIEW                            |                             |    36 |  2988 |       |     2   (0)| 00:00:01 |
  | 107 |              TABLE ACCESS FULL              | SYS_TEMP_0FD9D6FD2_17D15EA9 |    36 |  2052 |       |     2   (0)| 00:00:01 |
  | 108 |             VIEW                            |                             |   682 | 32054 |       |     3   (0)| 00:00:01 |
  | 109 |              TABLE ACCESS FULL              | SYS_TEMP_0FD9D6FD3_17D15EA9 |   682 | 36146 |       |     3   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("RNUM"&gt;=0)
     2 - filter(ROWNUM&lt;=3950)
     8 - access("D"."CRSE_ID"="C"."CRSE_ID")
    13 - access("A"."ACAD_PROG"='LMC09')
         filter("A"."ACAD_PROG"='LMC09')
    14 - access("B"."INSTITUTION"="A"."INSTITUTION" AND "B"."ACAD_ORG"="A"."ACAD_ORG")
    15 - access("C"."SUBJECT"="A"."ACAD_PLAN")
    16 - filter("C"."INSTITUTION"="A"."INSTITUTION")
    18 - access("E"."CRSE_ID"(+)="D"."CRSE_ID")
    19 - filter("E"."CRSE_ATTR"(+)='0003')
    23 - access("PERS"."EMPLID"="TRA"."EMPLID")
    24 - filter("TRA"."STRM"='1835' AND "TRA"."ACAD_PLAN"='LMC09')
    29 - access("A2"."EMPLID"="CAL"."EMPLID")
    35 - access("F"."CRSE_ID"="E"."CRSE_ID")
    36 - filter("F"."CRSE_ATTR"='0003')
    37 - access("B"."EMPLID"="A"."EMPLID")
    38 - access("H"."INSTITUTION"="A"."INSTITUTION" AND "H"."ACAD_CAREER"="A"."ACAD_CAREER" AND "H"."STRM"="A"."STRM")
    40 - access("E"."CRSE_ID"="C"."CRSE_ID")
    42 - access("G"."INSTITUTION"="A"."INSTITUTION" AND "G"."ACAD_ORG"="D"."ACAD_ORG")
    44 - access("C"."INSTITUTION"="A"."INSTITUTION" AND "C"."ACAD_CAREER"="A"."ACAD_CAREER" AND "C"."STRM"="A"."STRM" AND 
                "C"."CLASS_NBR"="A"."CLASS_NBR" AND "C"."SESSION_CODE"="A"."SESSION_CODE")
    46 - access("D"."INSTITUTION"="A"."INSTITUTION" AND "D"."ACAD_CAREER"="A"."ACAD_CAREER" AND 
                "D"."ACAD_PROG"="A"."ACAD_PROG")
    48 - filter("A"."STDNT_ENRL_STATUS"='E')
    52 - access("H"."INSTITUTION"="A"."INSTITUTION" AND "H"."ACAD_CAREER"="A"."ACAD_CAREER" AND 
                "H"."STRM"="A"."ARTICULATION_TERM")
    53 - access("F"."CRSE_ID"="E"."CRSE_ID")
    54 - access("E"."CRSE_ID"="C"."CRSE_ID")
    55 - access("C"."INSTITUTION"="A"."INSTITUTION" AND "C"."ACAD_CAREER"="A"."ACAD_CAREER" AND "C"."CRSE_ID"="A"."CRSE_ID")
    56 - access("D"."INSTITUTION"="A"."INSTITUTION" AND "D"."ACAD_CAREER"="A"."ACAD_CAREER" AND 
                "D"."ACAD_PROG"="A"."ACAD_PROG" AND "G"."INSTITUTION"="A"."INSTITUTION")
    57 - access("G"."ACAD_ORG"="D"."ACAD_ORG")
    61 - access("B"."EMPLID"="A"."EMPLID" AND "B"."ACAD_CAREER"="A"."ACAD_CAREER" AND "B"."INSTITUTION"="A"."INSTITUTION" 
                AND "B"."MODEL_NBR"="A"."MODEL_NBR")
    66 - filter("F"."CRSE_ATTR"='0003')
    68 - access("B"."EMPLID"="A"."EMPLID")
    70 - filter(ROWNUM&lt;=3950)
    71 - access("C3"."CRSE_ID"(+)="from$_subquery$_019"."CRSE_ID" AND "C3"."EMPLID"(+)="from$_subquery$_019"."EMPLID")
    74 - access("R3"."EMPLID"(+)="from$_subquery$_019"."EMPLID" AND 
                "R3"."RQRMNT_GROUP"(+)="from$_subquery$_019"."RQRMNT_GROUP")
         filter("from$_subquery$_019"."RQRMNT_GROUP"&lt;&gt;CASE  WHEN ("R3"."RQRMNT_GROUP"(+) IS NOT NULL) THEN ' ' ELSE ' ' END )
    79 - access("REQ"."CRSE_ID"="C2"."CRSE_ID"(+))
    80 - access("REQ"."RQRMNT_GROUP"(+)="M2"."RQRMNT_GROUP")
         filter("M2"."RQRMNT_GROUP"&lt;&gt;CASE  WHEN ("REQ"."RQRMNT_GROUP"(+) IS NOT NULL) THEN ' ' ELSE ' ' END )
    86 - access("DTL"."CRSE_ID"(+)="M3"."CRSE_ID" AND "A3"."IDTRAYECTORIA"=TO_NUMBER("DTL"."ID_TRAYECTORIA"(+)))
    88 - access("H3"."CRSE_ID"(+)="from$_subquery$_019"."CRSE_ID")
    96 - filter("A"."SESSION_CODE"='ORD' AND "A"."CLASS_STAT"='A')
    97 - access("A"."SUBJECT"='LMC09' AND "A"."STRM"='1853')
    99 - access("B"."CRSE_ID"(+)="A"."CRSE_ID" AND "B"."CRSE_OFFER_NBR"(+)="A"."CRSE_OFFER_NBR" AND "B"."STRM"(+)='1853' AND 
                "B"."SESSION_CODE"(+)='ORD' AND "B"."CLASS_SECTION"(+)="A"."CLASS_SECTION")
   101 - access("C"."CRSE_ID"(+)="B"."CRSE_ID" AND "C"."CRSE_OFFER_NBR"(+)="B"."CRSE_OFFER_NBR" AND "C"."STRM"(+)="B"."STRM" 
                AND "C"."SESSION_CODE"(+)="B"."SESSION_CODE" AND "C"."CLASS_SECTION"(+)="B"."CLASS_SECTION" AND 
                "C"."CLASS_MTG_NBR"(+)="B"."CLASS_MTG_NBR")
   103 - access("D"."EMPLID"(+)="C"."EMPLID")
   105 - access("M3"."ACAD_PLAN"="A3"."ACAD_PLAN")
  </code></pre>
  
  <p>EDIT 2, SOLUTION:
  I Managed to find the indexes already configured for the table 'PS_STDNT_ENRL' using the following query:</p>
  
  <pre><code>SELECT
      index_owner, index_name, table_name, column_name, column_position
  FROM DBA_IND_COLUMNS
  WHERE table_name = 'PS_STDNT_ENRL'
  ORDER BY
      index_owner,
      table_name,
      index_name,
      column_position;
  </code></pre>
  
  <p>And found the columns needed on my WHERE clause so that the index works. 
  I modified my query and now its very fast.</p>