Oracle 12c e as novas funções para acesso a dados

Por Ronaldo dos Reis Olegario
Postado em Março 2017

Revisado por Marcelo Pivovar - Solution Architect

Entre as várias novidades que foram apresentadas pelo Oracle database 12c, lançado como o primeiro banco dados projetado para a nuvem.A versão apresenta muitas funcionalidades voltada para administração e consolidação de instâncias, tais como Container Databases(CBD) e PluggableDatabases (PDB). A administração de backups também ganhou uma funcionalidade muito interessantes que é a possibilidade de recuperar uma tabela do backup full, sem a necessidade de restaurar todo o backup.A nova versão ganhou novas funcionalidades voltadas para o SQL que melhoram muito a forma de codificar Query’s e PLSQL’s.Foram desenvolvidas novas funcionalidadesno SQL na forma de funções, estas irão auxiliarmuito no do dia a dia dos desenvolvedores e analista de dados.

Neste artigo escolhemos algumas das novas funções disponibilizada na Versão 12c para uso no SQL e PLSQL para demonstramos o seu uso e realizarmos algumas comparações.

As funções escolhidas foram APPROX_COUNT_DISTINCT, FETCH FIRST n ROWS e aIDENTITY COLUMNS. A função SQLAPPROX_COUNT_DISTINCT, foi disponibilizada no release 12.1.0.2, o mais recente do Oracle Database 12c. Foi elaborada com o objetivo de minimizar a leitura de dados desnecessário e melhorar a performance a acesso a tabelas com grande número de registros, Vale a pena lembrar que a função não poderá ser utilizada para campos dos seguintes datatypes BFILE, BLOB, CLOB, LONG, LONG RAW, ou NCLOB. A função pode ser utilizada como alternativa ao selectcount(distinct).

A Função FETCH FIRST n ROWS nasceu com a versão 12c e igual ao Oracle e a outrosSGBDscomo SQLServer, SYBASE e Mysql no quesitorowlimits.A Exemplodo TOP que existe nos SGDBs SQL-Server e Sybase ouLimit do Mysql. No meu ponto de vista a funcionalidade auxiliara muito no dia a dia, até então precisávamos fazer alguns subselects para conseguir que o retorno de uma query fosse limitado a uma quantidade de registros e sua ordenação fosse satisfatória a condição, com a nova função código ficarálimpo e elegante.A função oferece a possibilidade de ser utilizada para paginar o retorno de uma consulta usando a cláusulaOFFSET é a solução de um grande problema enfrentado por desenvolvedores, também é possível limitar o retorno de dados utilizando um valor percentual desta forma é possível flexibilizar o retorno de dados.

A última função da nossa lista que também nasceu com a versão 12c é a Identify, muito utilizadas nos SGBDs Sybase e SQLServer agora poderá ser utilizada também no Oracle Database 12c. Embora o Oracle utilize internamente uma sequence para controlar o valor da coluna definido como IDENTITY COLUMNS, para o desenvolvedor representará em vários Nextval a menos no seu código.Projetos que estão sendo desenvolvidos para o Oracle 12c podem utilizar a função IDENTITY COLUMNS substituindo as sequencias que eram utilizadas para identificadores únicos de auto incremente.

Conjunto de dados básicos para demonstrar o uso das funções

Para possibilitar a demonstração do uso das funções precisaremos criar algumas tabelas. Para isso escolhemos um conjunto de tabelas bastante simples, que representa um modelo de dados para Cadastro de Funcionários de uma empresa. As tabelas são FUNCIONARIO, DEPARMENTO e seu relacionamento que se chamara FUNC_DEP_R. Nas listagens 1 e 2 apresentaremos os comandos utilizados para criar as tabelas e a carga de dados.

Listagem 1: DDL – Definição das tabelas que serão utilizadas no teste 
create table funcionario( 
id_funcionario integer, 
nm_nome varchar2(35), 
dt_nascimento date
);
create table departamento (
id_departamento integer, 
nm_nome varchar2(35)
);

alter table funcionario modify id_funcionario primary key;
alter table departamento modify id_departamento primary key;

create table func_dep_r (
id_funcionario integer, 
id_departamento integer, 
dt_desativacao date)
;

Listagem 2: DML – Carga de dados básicos para teste
INSERT INTO FUNCIONARIO VALUES (1, 'RONALDO DOS REIS OLEGARIO',TO_DATE('15/05/1979', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (2, 'JOSE DA SILVA',TO_DATE('15/05/1990', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (3, 'JOAO DA SILVA',TO_DATE('15/05/1989', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (4, 'PEDRO DA SILVA',TO_DATE('15/05/1991', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (5, 'AUGUSTO DA SILVA',TO_DATE('15/05/1978', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (6, 'ADERBAL DA SILVA',TO_DATE('15/05/1944', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (7, 'ANASTACIO DA SILVA',TO_DATE('15/05/1977', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (8, 'ROGERIO DA SILVA',TO_DATE('15/05/1980', 'DD/MM/YYYY') );
INSERT INTO FUNCIONARIO VALUES (9, 'ROMARIO DA SILVA',TO_DATE('15/05/1985', 'DD/MM/YYYY') );
COMMIT;
INSERT INTO DEPARTAMENTO VALUES (1,'INFRA-ESTRUTURA');
INSERT INTO DEPARTAMENTO VALUES (2,'RECURSOS HUMANOS');
INSERT INTO DEPARTAMENTO VALUES (3,'DESENVOLVIMENTO');
INSERT INTO DEPARTAMENTO VALUES (4,'NEGOCIOS');
INSERT INTO DEPARTAMENTO VALUES (5,'COMPRAS');
INSERT INTO DEPARTAMENTO VALUES (6,'CONTRATOS');
COMMIT;
INSERT INTO FUNC_DEP_R VALUES (1, 1, NULL);
INSERT INTO FUNC_DEP_R VALUES (2, 1, NULL);
INSERT INTO FUNC_DEP_R VALUES (3, 1, NULL);
INSERT INTO FUNC_DEP_R VALUES (4, 2, NULL);
INSERT INTO FUNC_DEP_R VALUES (5, 2, NULL);
INSERT INTO FUNC_DEP_R VALUES (6, 3, NULL);
INSERT INTO FUNC_DEP_R VALUES (7, 3, NULL);
INSERT INTO FUNC_DEP_R VALUES (8, 3, NULL);
INSERT INTO FUNC_DEP_R VALUES (9, 4, NULL);
COMMIT;
INSERT INTO FUNC_DEP_R VALUES (1, 4, NULL);
COMMIT;

Utilizando a funcão APPROX_COUNT_DISTINCT

Após ter criado a base de dados inicial, vamos realizar a primeira comparação de planos de execução. Para exemplificar vamos simular que precisamos identificar quais funcionários estão associados a mais de 1 departamento. Para realizar a tarefa utilizaremos uma query que utiliza o selectcount(distinct) clássico e outra query utilizando a função APPROX_COUNT_DISTINCT. Extraímos o plano de execução da query que utiliza o selectcount(distinct) clássico, demostrando na listagem 3,logo após extrairemos o plano de execução da query utilizando a função APPROX_COUNT_DISTINCT, demonstrado na listagem 4.

Listagem 3: Seleção de dados utilizando o select count(distinct)  e a função Having count()
SQL> select id_funcionario, count(distinct id_departamento) as ct from func_dep_r group by 
id_funcionario having count(distinct id_departamento)  > 1;

   ID_FUNCIONARIO         CT
----------------- ----------
         1          2
Execution Plan
----------------------------------------------------------
Plan hash value: 2028735599

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    16 |     4  (25)| 00:00:01 |
|*  1 |  FILTER               |           |       |       |            |          |
|   2 |   HASH GROUP BY       |           |     1 |    16 |     4  (25)| 00:00:01 |
|   3 |    VIEW               | VM_NWVW_1 |     9 |   144 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY     |           |     9 |    54 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| FUNC_DEP_R|     9 |    54 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT("$vm_col_1")>1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

Listagem 4: Seleção de dados utilizando a função APPROX_COUNT_DISTINCT

SQL> select id_func, APPROX_COUNT_DISTINCT(id_departamento) from func_dep_r group by id_func having 
APPROX_COUNT_DISTINCT(id_departamento) > 1;

   ID_FUNCIONARIO APPROX_COUNT_DISTINCT(ID_DEP)
----------------- -----------------------------
1                             2

Execution Plan
----------------------------------------------------------
Plan hash value: 3846490288

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     6 |     4  (25)| 00:00:01 |
|*  1 |  FILTER               |          |       |       |            |          |
|   2 |   HASH GROUP BY APPROX|          |     1 |     6 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | FUNC_DEP_R|     9 |    54 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(APPROX_COUNT_DISTINCT("ID_DEPARTAMENTO")>1)

Analisamos os 2 planos de execução, podemos perceber que tivemos uma diminuição no número de passos realizados pelo otimizador para resolver a query que utiliza a função APPROX_COUNT_DISTINCT, embora o custo tenha sido igual para os 2 métodos, a quantidade de bytes processados diminuiu consideravelmente com o uso da função APPROX_COUNT_DISTINCT. Para enriquecer a analise vamos adicionar mais dados a tabela FUNC_DEP e realizar uma nova rodada de testescom os 2 métodos descritos anteriormente.

Vamos utilizar o método deselectinsert na própria tabela para gerar volume de dados na tabela Func_dep_r. Este método será executado algumas vezes sem alteração, desta forma teremos um volume de dados considerável para a nova simulação, os métodos estão demonstrados nas listagens 5 e 6.Visto que o número total de registros da tabela mudou em relação ao primeiro teste, atualizaremos as estatísticas das tabelas este passo está demonstrado na listagem 7. Desta formateremos certeza que o otimizador fará o plano de acesso baseado com o número de registros atual das tabelas.

Listagem 5: Inserindo dados com select insert na tabela func_dep_r
insert into func_dep_r
select * from func_dep_r;
commit;

Listagem 6: Inserindo dados com select insert na tabela func_dep_r modificando o departamento
insert into func_dep_r
select 7,2,null 
from func_dep_r
where id_funcionario = 7;
commit;

Listagem 7: Atualização de estatisticas das tabelas utilizadas no teste
execute DBMS_STATS.gather_table_stats(ownname=> 'TEST', tabname=>'DEPARTAMENTO', estimate_percent =>100, 
cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO'); 

execute DBMS_STATS.gather_table_stats(ownname=> 'TEST', tabname=>'FUNCIONARIO', estimate_percent =>100, 
cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO'); 

execute DBMS_STATS.gather_table_stats(ownname=> 'TEST', tabname=>'FUNC_DEP', estimate_percent =>100, 
cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO'); 

Listagem 8:Seleção de dados utilizando o select count(distinct)  e a função Having count() segundo teste
SQL> select id_funcionario, count(distinct id_departamento) as ct from func_dep_r group by id_func having 
count(distinct id_departamento)  > 1;

   ID_FUNCIONARIO         CT
----------------- ----------
1          2
7          2


Execution Plan
----------------------------------------------------------
Plan hash value: 2028735599

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    16 |     4  (25)| 00:00:01 |
|*  1 |  FILTER               |           |       |       |            |          |
|   2 |   HASH GROUP BY       |           |     1 |    16 |     4  (25)| 00:00:01 |
|   3 |    VIEW               | VM_NWVW_1 |    10 |   160 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY     |           |    10 |    60 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| FUNC_DEP_R|   704 |  4224 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(“$vm_col_1")>1)

Listagem 9: Seleção de dados utilizando a função APPROX_COUNT_DISTINCT segundo teste

SQL> select id_func, APPROX_COUNT_DISTINCT(id_dep) from func_dep group by id_func having 
APPROX_COUNT_DISTINCT(id_dep) > 1;

   ID_FUNCIONARIO APPROX_COUNT_DISTINCT(ID_DEP)
----------------- -----------------------------
1                             2
7                             2

Execution Plan
----------------------------------------------------------
Plan hash value: 3846490288

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     6 |     4  (25)| 00:00:01 |
|*  1 |  FILTER               |          |       |       |            |          |
|   2 |   HASH GROUP BY APPROX|          |     1 |     6 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | FUNC_DEP_R|   704 |  4224 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(APPROX_COUNT_DISTINCT("ID_DEPARTAMENTO")>1)

Analisando o plano de execução demonstrados na listagens 8 e 9,percebemos que existe uma diferença significativa entre a query utilizando oselectcount(distinct) e a query utilizando a função APPROX_COUNT_DISTINCT. Se observamos as listagens 3 e 4 e comparamos com a listagens8 e 9, identificamos que os números de passos que o otimizador realizou para localizar os dados que precisamos é menor quando utilizada a função APPROX_COUNT_DISTINCT. Outro ponto importante de comparação éa quantidade de bytes processados. Volume de bytes processados diminuiu consideravelmente quando a função APPROX_COUNT_DISTINCT é utilizada. O uso da função APPROX_COUNT_DISTINCT se mostrou interessante mesmo quando utilizada em cenários onde temos pouca complexidade e baixo volume de dados, os números devem melhorar ainda mais como um volume de dados maior.

Utilizando a funçãoFETCH FIRST n ROWS

Outra função que se destaca no Oracle Database 12c é a FETCH FIRST n ROWS. Esta função insere ao Oracle o conceito de TOP N query, este tipo de função existe a algum tempo em outrosSGBDs do mercado. Exemplos o TOP do SQL-Server e Sybase e o limit do Mysql. Esta funcionalidade vai auxiliar muito no dia a dia do desenvolvedor. Atéo surgimento desta função no Oracle, para retornarmos um número determinado de registros no resultado de uma query era necessário fazer o uso de subselects, como o uso do subselectera possíveladequar o retorno dos registros de forma que o resultado fosse o número limite esperado. O código ficava extenso e nenhum um pouco elegante. Abaixo vamos ver como isso fica na prática, para agilizar o processo utilizarei o mesmo conjunto de tabelas e dados utilizado para testar a função APPROX_COUNT_DISTINCT. Realizaremos uma query que irá retornar todos os dados da tabela funcionário demonstrado na listagem 10.

Listagem 10: Seleção de todos os dados da tabelade funcionario
SQL> select id_funcionario, nm_nome from funcionario;

   ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
	1 Ronaldo dos Reis Olegario
	2 Jose da Silva
		3 Joao da Silva
		4 Pedro da Silva
		5 Augusto da Silva
		6 Aderbal da Silva
		7 Anastacio da Silva
		8 Rogerio da Silva
		9 Romario da Silva

Listagem 11: Seleção de dados da tabela de funcionarios utilizando a funcão FETCH FIRST ROWS
SQL> select id_funcionario, nm_nome from funcionario FETCH FIRST 5 ROWS ONLY;
ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		1 Ronaldo dos Reis Olegario
		2 Jose da Silva
		3 Joao da Silva
		4 Pedro da Silva
		5 Augusto da Silva

A listagem 11 mostra como ficaria o retorno da query, se a função FETCH FIRST ROWS fosse utilizada para retornar somente 5 registros da tabela funcionário. A tabela possui um total de 9 registros que foram listados nalistagem 10. No próximo exemplo de utilização vamos simular uma ordenaçãopor nome e retornar somente os 5 primeiros registros desta ordenação. Sem o uso da função FETCH FIRST é necessário utilizar um Subselect, para que os dados retornem de forma correta, este método é demonstrado na listagem 12.

Listegem 12: Seleção de funcionário retornando 5 registros ordenados
SQL>  select id_funcionario, nm_nome from (
 select id_funcionario, nm_nome, rownum as linha from (
 select id_funcionario, nm_nome fromFUNCIONARIO order by nm_nome )
 )
 where linha < 6 ;

   ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		6 Aderbal da Silva
		7 Anastacio da Silva
		5 Augusto da Silva
		3 Joao da Silva
		2 Jose da Silva


Listegam 13:Seleção de funcionário retornando 5 registros ordenados com a funcão FETCH FIRSTROWS
SQL> select id_funcionario, nm_nome from TEST.FUNCIONARIO order by nm_nome FETCH FIRST 5 rows only;

ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
			6 Aderbal da Silva
			7 Anastacio da Silva
		5 Augusto da Silva
		3 Joao da Silva
		2 Jose da Silva

A listagem 12 demonstrou que precisamos utilizar vários subselects para retornar o resultado corretamente, sem o uso desubselect retornaríamos todos os registros da tabela ordenados por nome. A listagem 13 demonstra como a função FETCH FIRSTé utilizada adequar o resultado sem o uso de subselect. Observe que a query é limpa e direta, sem a necessidade de utilizar código extra para que o retorno seja o esperado.

Outra forma de utilizar a função FETCH FIRST, é informando um percentual, ou seja, você pode indicar o percentual de registros que gostaria que a query retornasse. Digamos que você precisa que o retorno seja uma amostra de 30% do totalde registros da tabela funcionário, esta forma de uso é demonstrada na listagem 14.

Listagem 14: Utilizando a função FETCH FIRST para retorna um percentual de registros 
SQL> select id_funcionario, nm_nome from funcionario FETCH FIRST 30 percent rows only;
ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		1 Ronaldo Olegario
		2 Jose da Silva
		3 Joao da Silva

É possível utilizar a função FETCH FIRST com a cláusula withties. Utilizando desta forma,valores duplicados que estejam presentes no conjunto de dados selecionados serão apresentados resultado, desta forma o resultado pode não obedecer exatamente ao que foi especificado, recomenda-se atenção ao utilizar. Outra funcionalidade muito interessante da função FETCH FIRST é a OFFSET. Esta funcionalidade é muito útil para realizar paginação de dados. Utilizando a funcionalidadeé possível limitar a quantidade de registros que serão retornados, também é possível indicar qual o registro que deve iniciar a ser listado.

Listagem 15:Utilizando a função FETCH FIRST para paginação de dados
SQL>  select id_funcionario, nm_nome from FUNCIONARIO order by nm_nome OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		 4 Pedro da Silva
		 8 Rogerio da Silva
		 9 Romario da Silva
		 1 Ronaldo Olegario

No resultado retornado estão listados somente os registros a partir da linha 6. Conforme comentado anteriormente, a função pode ser utilizada para relatórios em que é utilizada paginação, muito comum em relatório de aplicações web.Demonstrado o uso das funções, vamos realizar a analise dos seus planos de execução. Compararemos os planos de execução dos métodos tradicionais e dos métodos usando a função FETCH FIRST.

Listagem 16:Cenário, select das primeiros 5 linhas com subselect
SQL> select id_funcionario, nm_nome from (
 select id_funcionario, nm_nome, rownum as linha from (
 select id_funcionario, nm_nome from FUNCIONARIO order by nm_nome ))
 where linha < 6 ;

   ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		6 Aderbal da Silva
		7 Anastacio da Silva
		5 Augusto da Silva
		3 Joao da Silva
		2 Jose da Silva


Execution Plan
----------------------------------------------------------
Plan hash value: 311957440

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     9 |   405 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                 |             |     9 |   405 |     4  (25)| 00:00:01 |
|   2 |   COUNT               |             |       |       |            |          |
|   3 |    VIEW               |             |     9 |   288 |     4  (25)| 00:00:01 |
|   4 |     SORT ORDER BY     |             |     9 |   198 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| FUNCIONARIO |     9 |   198 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(“LINHA"<6)


Listagem 17: Cenário usando a função FETCH FIRST
SQL> select id_funcionario, nm_nome from FUNCIONARIO order by nm_nome FETCH FIRST 5 rows only ;

ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		6 Aderbal da Silva
		7 Anastacio da Silva
		5 Augusto da Silva
		3 Joao da Silva
		2 Jose da Silva


Execution Plan
----------------------------------------------------------
Plan hash value: 3823756800

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     5 |   320 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |             |     5 |   320 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|             |     9 |   198 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | FUNCIONARIO |     9 |   198 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY “NM_NOME")<=5)

Comparando a listagem 16 e a listagem 17 podemos identificar uma diminuição na quantidade de bytes processados e na quantidade de passos que o otimizador realizou para chegar ao resultado foi menor. O otimizador tem um método de acesso específico para a funçãoFETCH FIRST, desta forma o acesso aos dados é mais eficiente e rápido.

Listagem 18:Cenárioonde a paginação é feita manualmente no código
SQL> select id_funcionario, nm_nome from (
 select id_funcionario, nm_nome, rownum as linha from (
 select id_funcionario, nm_nome from FUNCIONARIO order by nm_nome )
)
 where linha between 6 and 12  2    3    4    5  ;

   ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		4 Pedro da Silva
		8 Rogerio da Silva
		9 Romario da Silva
		1 Ronaldo dos Reis Olegario


Execution Plan
----------------------------------------------------------
Plan hash value: 311957440

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     9 |   405 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                 |             |     9 |   405 |     4  (25)| 00:00:01 |
|   2 |   COUNT               |             |       |       |            |          |
|   3 |    VIEW               |             |     9 |   288 |     4  (25)| 00:00:01 |
|   4 |     SORT ORDER BY     |             |     9 |   198 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| FUNCIONARIO |     9 |   198 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LINHA"<=12 AND "LINHA">=6)

Listagem 19:Cenário onde a paginação é feita utilizando a função OFFSET
SQL>  select id_funcionario, nm_nome from FUNCIONARIO order by nm_nome OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

ID_FUNCIONARIO NM_NOME
----------------- -----------------------------------
		4 Pedro da Silva
		8 Rogerio da Silva
		9 Romario da Silva
		1 Ronaldo dos Reis Olegario


Execution Plan
----------------------------------------------------------
Plan hash value: 3823756800

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     9 |   576 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |             |     9 |   576 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|             |     9 |   198 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | FUNCIONARIO |     9 |   198 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (5>=0)
              THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "NM_NOME")<=CASE  WHEN (5>=0) THEN 5
ELSE 0 END +5)

Analisando todos os planos de acesso das listagens 18 e 19 observamos que a função OFFSET processou mais bytes que a query onde a paginação é realizada manualmente, no entanto o número de passos que o otimizador realizou foi menor.

Utilizando a função IDENTITY COLUMNS

A função IDENTITY COLUMNS presente em SGBDs como Sybase e SQL-Server está disponível na versão do Oracle 12c. Aplicações voltadas para a versão 12c podem fazer o uso desta nova funcionalidade, e assim substituindo o uso dassequences que são largamente utilizadas para controle de campos incrementais no por desenvolveres Oracle. A função permite 3 formas de uso, são elas ALWAYS, BY DEFAULT e BY DEFAULT ON NULL. Vamos demonstrar o uso de cada uma delas nas próximas listagens, discas para evitar erros e possibilidades de uso.

Listagem 20: Uso da funcão Identify columnscom always no create table
SQL>create table empresa (
    ID_EMPRESA NUMBER GENERATED ALWAYS AS IDENTITY, 
	NM_NOME varchar2(50), 
	CNPJ VARCHAR2(18)
	);

Table created.

SQL>	alter table empresa add constraint pk_emp primary key (ID_EMPRESA);

Table altered.

SQL>insert into empresa (NM_NOME, CNPJ) values ('AZ Solucoes','01.001.001/0001-01');

1 row created.

SQL> commit;

Commit complete.

SQL>

Na listagem 20 criamos a tabela empresa usando a função IDENTITY COLUMNS. Utilizamos o comando insert para inserir um registro a tabela, observe que no comando insert informamos o nome de cada campo. Este é um ponto muito importante quando utilizamos a funçãoIDENTITY COLUMNS, na sua forma mais simples precisamos informar o nome dos campos no comando de insert, caso o nome do campo não estejam presente no comando insert, será retornado um erro na execução do comando e o registro não será inserido na tabela. Este comportamento é demonstrado na listagem 21, onde não são informados os nomes dos campos.

Listagem 21:Inserindo dados em tabela com coluna IDENTITY COLUMNS sem identicar os campos
SQL> insert into empresa values ('AZ Solucoes','01.001.001/0001-01');
insert into empresa values ('AZ Solucoes','01.001.001/0001-01')
            *
ERROR at line 1:
ORA-00947: not enough values


SQL>  insert into empresa values (null,'AZ Solucoes','01.001.001/0001-01');
 insert into empresa values (null,'AZ Solucoes','01.001.001/0001-01')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


Listagem 22:Uso da funcão Identify columns com BY DEFAULT no create table
SQL> drop table empresa;
Table dropped.

SQL>create table empresa (
    ID_EMPRESA NUMBER GENERATED BY DEFAULT AS IDENTITY, 
        NM_NOME varchar2(50), 
        CNPJ VARCHAR2(18)
        );
Table created.


SQL> insert into empresa (NM_NOME, CNPJ) values ('AZ Solucoes','01.001.001/0001-01');

1 row created.

SQL> commit;

Commit complete.

SQL>insert into empresa (ID_EMPRESA, NM_NOME, CNPJ) values (100, 'AZ Solucoes','01.001.001/0001-01');

1 row created.

SQL> commit;

Commit complete.	

SQL> insert into empresa (ID_EMPRESA, NM_NOME, CNPJ) values (null, 'AZ Solucoes','01.001.001/0001-01');
insert into empresa (ID_EMPRESA, NM_NOME, CNPJ) values (null, 'AZ Solucoes','01.001.001/0001-01')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."EMPRESA"."ID_EMPRESA")


SQL> select * from empresa;

ID_EMPRESA NM_NOME                                            CNPJ
---------- -------------------------------------------------- ------------------
         1 AZ Solucoes                                        01.001.001/0001-01
       100 AZ Solucoes                                        01.001.001/0001-01

SQL>

A listagem 22 cria a tabela informando o parâmetro GENERATED BY DEFAULT AS IDENTITY, desta forma podemos utilizar o comando insert informando um valor para o campo ao qual o identify foi especificado, entretanto o valor utilizado para o campo deve ser um valor valido, caso contrario o comando retornará erro. A listagem 23 apresenta uma alternativa que é a junção dos 2 métodos anteriormente apresentados.

Listagem 23: Uso da funcão Identify columns com BY DEFAULT ON NULL no create table
SQL> drop table empresa;
Table dropped.
SQL>create table empresa (
    ID_EMPRESA NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
        NM_NOME varchar2(50), 
        CNPJ VARCHAR2(18)
        );
Table created.

SQL> INSERT INTO empresa (NM_NOME) VALUES ('AZ Solucoes');

1 row created.

SQL> INSERT INTO empresa (ID_EMPRESA, NM_NOME) VALUES (100, 'Empresa 100');

1 row created.

SQL> INSERT INTO empresa (ID_EMPRESA, NM_NOME) VALUES (NULL, 'Empresa com ID null');

1 row created.

SQL> commit;

Commit complete.



SQL> select * from empresa;

ID_EMPRESA NM_NOME                                            CNPJ
---------- -------------------------------------------------- ------------------
         1 AZ Solucoes
       100 Empresa 100
         2 Empresa com ID null

Observando as listagens 20, 22 e 23 é possível ver quanto é flexível a funçãoIDENTITY COLUMNS. Podemos utilizar de forma automática ou controlando os valores que serão inseridos, com tantas alternativas de uso a conversão de códigos para a nova versão do Oracle fica mais fácil. Emborainternamente o Oracle tratea função IDENTITY COLUMNS como uma sequence a flexibilidade é indiscutível ao comparar o método de sequence. Caso queria verificar qual sequence faz a gerencia do IDENTITY COLUMNS, podemos coletar a informação consultando o dicionário de dados. A listagem 24 demonstra como está relação é encontramos do dicionário de dados.

Listagem 24:  Listando todas as sequences de um determinado usuário
SQL> SELECT table_name, 
       column_name,
       generation_type,
       identity_options
FROM   all_tab_identity_cols
WHERE  owner = 'TEST'
ORDER BY 1, 2;

TABLE_NAME           COLUMN_NAME     GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- ----------------------------------------------
EMPRESA              ID_EMPRESA      BY DEFAULT START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 
9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

Conclusão

A versão 12c do Oracle database presenteou os usuários com váriasfuncionalidades que ajudarão muito no dia a dia do desenvolvimento. O uso das funções que demonstramos no artigo podem tornar seus códigos mais eficientes e dinâmicos, eliminam acesso desnecessário a dados pois o otimizador tem funcionalidades específica para processar a query. Com crescente a necessidade de aplicações distribuídas pela internet quanto menos dados desnecessários acessarmos mais performáticas elas serão, o trafego de dados entre cliente e servidor diminui e com isso a necessidade de banda também. Como o próprio nome da versão 12 do Oracle já diz seu objetivo é levar o banco de dados para a nuvem, sem dúvida alguns passos nesta direção foram dados.


Ronaldo dos Reis Olegario
rolegar@gmail.com - https://br.linkedin.com/in/ronaldoolegario
DBA Oracle/Sybase e SQLServer Senior, atuando a mais de 12 anos na área TI é formado em Ciência da Computação pela PUC-RS, possui certificações de Oracle Professional 10g e 11g, Oracle Real Application Cluster 10G e Oracle Goldengate Certified Specialist.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.