Oracle 12c – Limit records – Top-N Query

Estava conversando com meu amigo Milton Bastos (certificacaobd) e acabei achando essa new feature do Oracle 12c, que achei muito interessante. Quem já teve que trabalhar com query TOP-N sabe que é complexo para escrever e muito pesado do ponto de vista de performance.

Desafio: Pegar as 3 últimas linhas de uma listagem das 10 primeiras tabelas da dba_tables (ordem alfabética)

Abaixo, irei mostrar como fazer até o 11g e como ficou muito mais simples no Oracle 12c.

 

— Pre 12c, poderia ser escrito dessa forma

col owner form a20
col table_name form a32

SELECT owner, table_name, rn
FROM   (SELECT owner,table_name, rownum AS rn
FROM   (SELECT owner,table_name
FROM   dba_tables
ORDER BY table_name)
WHERE rownum <= 10)
WHERE  rn > 7;

— Plano de execução
—————————————————————————
| Id  | Operation                      | Name     | Rows  | Bytes | Cost  |
—————————————————————————
|   0 | SELECT STATEMENT               |          |    10 |  1450 |  2597 |
|   1 |  VIEW                          |          |    10 |  1450 |  2597 |
|   2 |   COUNT STOPKEY                |          |       |       |       |
|   3 |    VIEW                        |          | 13519 |  1742K|  2597 |
|   4 |     SORT ORDER BY STOPKEY      |          | 13519 |  1980K|  2597 |
|   5 |      HASH JOIN RIGHT OUTER     |          | 13519 |  1980K|  2149 |
|   6 |       INDEX FULL SCAN          | I_USER2  |   199 |   796 |     1 |
|   7 |       HASH JOIN OUTER          |          | 13519 |  1927K|  2148 |
|   8 |        HASH JOIN               |          | 13519 |  1808K|  2014 |
|   9 |         TABLE ACCESS FULL      | USER$    |   199 |  3582 |     5 |
|  10 |         HASH JOIN              |          | 13519 |  1571K|  2009 |
|  11 |          NESTED LOOPS OUTER    |          | 13519 |  1082K|  1399 |
|  12 |           HASH JOIN RIGHT OUTER|          | 13519 |  1016K|  1318 |
|  13 |            TABLE ACCESS FULL   | SEG$     | 31681 |   340K|   248 |
|  14 |            HASH JOIN           |          | 13519 |   871K|  1070 |
|  15 |             NESTED LOOPS       |          |    24 |   840 |    10 |
|  16 |              HASH JOIN         |          |     1 |    32 |     1 |
|  17 |               FIXED TABLE FULL | X$KSPPI  |     1 |    28 |     0 |
|  18 |               FIXED TABLE FULL | X$KSPPCV |  3351 | 13404 |     0 |
|  19 |              TABLE ACCESS FULL | TS$      |    24 |    72 |     9 |
|  20 |             TABLE ACCESS FULL  | TAB$     | 13519 |   409K|  1060 |
|  21 |           INDEX RANGE SCAN     | I_OBJ1   |     1 |     5 |     1 |
|  22 |          TABLE ACCESS FULL     | OBJ$     |   145K|  5263K|   609 |
|  23 |        INDEX FAST FULL SCAN    | I_OBJ1   |   145K|  1280K|   134 |
—————————————————————————

Statistics
———————————————————-
0  recursive calls
0  db block gets
7686  consistent gets
0  physical reads
0  redo size
779  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
3  rows processed

Tempo de execução 10 ms

— No Oracle 12c a query acima pode ser substituído por:

select owner, table_name, rownum as rn
from dba_tables
order by table_name
offset 8 rows fetch next 3 rows only;

— Plano de execução
—————————————————————————
| Id  | Operation                      | Name     | Rows  | Bytes | Cost  |
—————————————————————————
|   0 | SELECT STATEMENT               |          | 13519 |  2957K|  2597 |
|   1 |  VIEW                          |          | 13519 |  2957K|  2597 |
|   2 |   WINDOW SORT PUSHED RANK      |          | 13519 |  1980K|  2597 |
|   3 |    COUNT                       |          |       |       |       |
|   4 |     HASH JOIN RIGHT OUTER      |          | 13519 |  1980K|  2149 |
|   5 |      INDEX FULL SCAN           | I_USER2  |   199 |   796 |     1 |
|   6 |      HASH JOIN OUTER           |          | 13519 |  1927K|  2148 |
|   7 |       HASH JOIN                |          | 13519 |  1808K|  2014 |
|   8 |        TABLE ACCESS FULL       | USER$    |   199 |  3582 |     5 |
|   9 |        HASH JOIN               |          | 13519 |  1571K|  2009 |
|  10 |         NESTED LOOPS OUTER     |          | 13519 |  1082K|  1399 |
|  11 |          HASH JOIN RIGHT OUTER |          | 13519 |  1016K|  1318 |
|  12 |           TABLE ACCESS FULL    | SEG$     | 31681 |   340K|   248 |
|  13 |           HASH JOIN            |          | 13519 |   871K|  1070 |
|  14 |            MERGE JOIN CARTESIAN|          |    24 |   840 |    10 |
|  15 |             HASH JOIN          |          |     1 |    32 |     1 |
|  16 |              FIXED TABLE FULL  | X$KSPPI  |     1 |    28 |     0 |
|  17 |              FIXED TABLE FULL  | X$KSPPCV |  3351 | 13404 |     0 |
|  18 |             BUFFER SORT        |          |    24 |    72 |     9 |
|  19 |              TABLE ACCESS FULL | TS$      |    24 |    72 |     9 |
|  20 |            TABLE ACCESS FULL   | TAB$     | 13519 |   409K|  1060 |
|  21 |          INDEX RANGE SCAN      | I_OBJ1   |     1 |     5 |     1 |
|  22 |         TABLE ACCESS FULL      | OBJ$     |   145K|  5263K|   609 |
|  23 |       INDEX FAST FULL SCAN     | I_OBJ1   |   145K|  1280K|   134 |
—————————————————————————

Statistics
———————————————————-
0  recursive calls
0  db block gets
7686  consistent gets
0  physical reads
0  redo size
779  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
3  rows processed

Tempo de execução 12 ms

Também é possível usar % para essas queries entre outras coisas… Achei muito legal essa funcionalidade embora seja um pouquinho mais pesada, mas dá muito menos trabalho para escrever.

SINTAXE:

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]

Referência:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#BABHFGAA