Lendo informações de uma tabela externa no Oracle

Um recurso muito interessante do Oracle (e talvez pouco explorado) são as Tabelas Externas. Com esse mecanismo é possível consultar uma tabela à partir de um arquivo texto, sem a necessidade de importá-lo para o Banco de Dados.

Uma determinada demanda pode exigir que se faça a comparação de uma fonte de dados externa com os dados presentes no sistema de gestão, sem uma tabela externa os dados precisam ser importados para uma tabela normal (por algoritmo ou por SQL*Loader), que por sua vez precisa ser criada especificamente para essa finalidade. Contudo, com a utilização de uma tabela externa você pode ler um arquivo texto e ela terá praticamente as mesmas características de uma tabela normal, sem a necessidade de que os dados façam parte do Banco de Dados.

O comando

Para criar uma tabela externa, primeiramente é necessário escrever a sua definição de dados (colunas e tipo de dado), em seguida determinar qual o diretório Oracle a ser utilizado e que o arquivo estará presente, estabelecer as regras para a delimitação das colunas e linhas e dos arquivos de log, de erro e de descarte (são opcionais, mas muito úteis) e por fim o nome do arquivo a ser lido.

CREATE TABLE T1_EXTERNA
(
	ID NUMBER,
	NOME VARCHAR2(200),
	NR_MUNICIPIO_IBGE NUMBER(8)
)
ORGANIZATION EXTERNAL
(
	TYPE ORACLE_LOADER DEFAULT DIRECTORY DIRETORIO_TESTE
	ACCESS PARAMETERS
	(
		RECORDS DELIMITED BY NEWLINE BADFILE 'ARQEXTERNO.BAD'
		DISCARDFILE 'ARQEXTERNO.DSC'
		LOGFILE 'ARQEXTERNO.LOG'
		FIELDS TERMINATED BY ';'
		MISSING FIELD VALUES ARE NULL
	)
	LOCATION ('ArquivoClienteCidade.txt')
);

Note, que foi escolhido o diretório Oracle DIRETORIO_TESTE, sendo que para isso teríamos que inicialmente ter acesso a ele ou criá-lo com o comando a seguir:

CREATE DIRECTORY DIRETORIO_TESTE AS 'C:\TEMP\';

ArquivoClienteCidade.txt

1;Adauto Santos;4115200
2;Nicole Souza Costa;4126256
3;Martim Ribeiro Gomes;4114807
4;Luana Castro Alves;4103453
5;Alvito Solange Bivar;4102109
6;Belchior Edite Rufino;4117503
7;Luiza Silva Azevedo;4116901

Simples assim! Você poderá fazer um SQL na tabela T1_EXTERNA da mesma maneira que qualquer outra tabela. Muito provavelmente um outro usuário nem mesmo saberá que se trata de uma Tabela Externa.

SQL> SELECT * FROM T1_EXTERNA;

ID NOME                  NR_MUNICIPIO_IBGE
-- --------------------- -----------------
 1 Adauto Santos                   4115200
 2 Nicole Souza Costa              4126256
 3 Martim Ribeiro Gomes            4114807
 4 Luana Castro Alves              4103453
 5 Alvito Solange Bivar            4102109
 6 Belchior Edite Rufino           4117503
 7 Luiza Silva Azevedo             4116901

A explicação

Quando tiver terminado a execução do comando da sessão anterior você poderá realizar uma consulta na DBA_OBJECTS e irá notar que a tabela em questão estará presente no dicionário de dados como qualquer outra. Na realidade, as três diferenças básicas entre a Tabela Externa e uma tabela convencional são: 1) os dados estarão no SO e não na Tablespace 2) não será possível fazer DML 3) ela não será um segmento, pois não armazena dados.

SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME = 'T1_EXTERNA';

OBJECT_NAME     OBJECT_TYPE
-------------- ------------
T1_EXTERNA      TABLE

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T1_EXTERNA';

não há linhas selecionadas

Além disso, você poderá fazer a junção com outras tabelas sendo externas ou não. No exemplo a seguir fazemos a junção com a tabela MUNICIPIO de forma a obter a descrição da cidade do cliente.

SELECT T.ID, T.NOME, T.NR_MUNICIPIO_IBGE, M.DS_MUNICIPIO
  FROM T1_EXTERNA T,
       MUNICIPIO M
 WHERE T.NR_MUNICIPIO_IBGE = M.NR_SEQ_IBGE;

ID NOME                  NR_MUNICIPIO_IBGE DS_MUNICIPIO
-- --------------------- ----------------- --------------
 1 Adauto Santos                   4115200 Maringá
 2 Nicole Souza Costa              4126256 Sarandi
 3 Martim Ribeiro Gomes            4114807 Marialva
 4 Luana Castro Alves              4103453 Cafelândia
 5 Alvito Solange Bivar            4102109 Astorga
 6 Belchior Edite Rufino           4117503 Paiçandu
 7 Luiza Silva Azevedo             4116901 Nova Esperança

Meus testes

Para demonstrar o ganho de performance proporcionado pela utilização das tabelas externas, foram realizados dois testes: (1) a contagem de registros em tabelas com 100 mil, 500 mil, 1 milhão e 5 milhões (2) agrupamento e contagem de registros com a junção em outra tabela, sendo que esse teste foi realizado em uma tabela com 100 mil registros.

Teste 1

No primeiro teste foram realizadas as contagens dos registros em tabelas regulares e externas, com o DDL apresentado na seção anterior e ambas as tabelas possuem 100 mil, 500 mil, 1 milhão e 5 milhões de registros. Nos testes as tabelas externas independentemente do tamanho apresentaram o mesmo custo (29), enquanto as tabelas regulares tiveram um custo maior, iniciando em 129 na tabela de 100 mil e terminando em 6.338 na tabela de 5 milhões de registros. Os planos de execução das consultas podem ser observados a seguir.

SQL> SELECT COUNT(*) FROM T1_EXTERNA_100K WHERE NR_MUNICIPIO_IBGE = 4115200;

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     1 |    13 |   129   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE    |                 |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1_REGULAR_100K | 13384 |   169K|   129   (2)| 00:00:02 |
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     1 |    13 |   638   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE    |                 |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1_REGULAR_500K | 66918 |   849K|   638   (2)| 00:00:08 |
--------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
  Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    13 |  1275   (2)| 00:00:16 |
|   1 |  SORT AGGREGATE    |               |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1_REGULAR_1M |   142K|  1813K|  1275   (2)| 00:00:16 |
------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    13 |  6338   (2)| 00:01:17 |
|   1 |  SORT AGGREGATE    |               |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1_REGULAR_5M |   532K|  6765K|  6338   (2)| 00:01:17 |
------------------------------------------------------------------------------------

-- O custo foi o mesmo para as tabelas com 100 mil, 500 mill, 1 milhão e 5 milhões de registros
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    13 |    29   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |               |     1 |    13 |            |          |
|*  2 |   EXTERNAL TABLE ACCESS FULL| T1_EXTERNA_5M |    82 |  1066 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

A tabela a seguir apresenta um resumo das estatísticas obtidas nas consultas. Nota-se que a tabela externa não apresentou um comportamento padrão em seu consistent gets, enquanto a tabela regular cresceu paralelamente ao número de registros presentes nas tabelas.

Descrição 100 mil regs. 500 mil regs. 1 milhão regs. 5 milhões regs.
Normal Externa Regular Externa Regular Externa Regular Externa
recursive calls 18 415 15 40 15 1.421 15 42
db block gets 0 0 0 0 0 0 0 0
consistent gets 551 1.156 2.396 613 4.707 2.172 23.128 620
physical reads 0 0 2.305 0 4.609 0 23.042 0
redo size 0 0 0 0 0 0 0 0
bytes sent via SQL*Net to client 528 528 528 528 528 528 528 528
bytes received via SQL*Net from client 523 523 523 523 523 523 523 523
SQL*Net roundtrips to/from client 2 2 2 2 2 2 2 2
sorts (memory) 2 5 2 43 2 5 2 2
sorts (disk) 0 0 0 0 0 0 0 0
rows processed 1 1 1 1 1 1 1 1

Teste 2

No segundo teste foram realizadas a contagem dos registros em uma tabelas regular e externa com 100 mil registros e realizando a junção com uma segunda tabela (MUNICIPIO_IBGE) e para esse teste foi criada ainda uma terceira tabela com índice na coluna NR_MUNICIPIO, para compará-la com as demais. Novamente, a tabela externa possuiu um custo menor quando comparada com a tabela regular, porém a utilização do índice fez com que custo se aproximasse ao obtido na tabela externa. Reforçando, que devido a não possuir segmentos nas tablespaces não é possível criar índices em tabelas externas.

SQL> SELECT DS_MUNICIPIO,
            COUNT(*)
       FROM T1_REGULAR_100K
            MUNICIPIO_IBGE MI
      WHERE NR_MUNICIPIO_IBGE = MI.NR_MUNICIPIO
        AND NR_MUNICIPIO_IBGE = 4115200
   GROUP BY DS_MUNICIPIO;

-- Tabela Normal
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 13384 |   862K|   133   (4)| 00:00:02 |
|   1 |  HASH GROUP BY        |                 | 13384 |   862K|   133   (4)| 00:00:02 |
|*  2 |   HASH JOIN           |                 | 13384 |   862K|   132   (3)| 00:00:02 |
|   3 |    TABLE ACCESS FULL  | MUNICIPIO_IBGE  |     1 |    40 |     2   (0)| 00:00:01 |
|   4 |    VIEW               | VW_GBF_7        | 13384 |   339K|   129   (2)| 00:00:02 |
|   5 |     HASH GROUP BY     |                 | 13384 |   169K|   129   (2)| 00:00:02 |
|*  6 |      TABLE ACCESS FULL| T1_REGULAR_100K | 13384 |   169K|   129   (2)| 00:00:02 |
-----------------------------------------------------------------------------------------

-- Tabela Externa
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |    82 |  5412 |    32   (4)| 00:00:01 |
|   1 |  HASH GROUP BY                 |                 |    82 |  5412 |    32   (4)| 00:00:01 |
|*  2 |   HASH JOIN                    |                 |    82 |  5412 |    31   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL           | MUNICIPIO_IBGE  |     1 |    40 |     2   (0)| 00:00:01 |
|   4 |    VIEW                        | VW_GBF_7        |    82 |  2132 |    29   (0)| 00:00:01 |
|   5 |     HASH GROUP BY              |                 |    82 |  1066 |    29   (0)| 00:00:01 |
|*  6 |      EXTERNAL TABLE ACCESS FULL| T1_EXTERNA_100K |    82 |  1066 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

-- Tabela Normal com índice na coluna NR_MUNICIPIO_IBGE
----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                | 15425 |   994K|    37   (3)| 00:00:01 |
|   1 |  HASH GROUP BY        |                | 15425 |   994K|    37   (3)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|                | 15425 |   994K|    37   (3)| 00:00:01 |
|   3 |    VIEW               | VW_GBF_7       |     1 |    53 |     3  (34)| 00:00:01 |
|   4 |     HASH GROUP BY     |                |     1 |    40 |     3  (34)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL| MUNICIPIO_IBGE |     1 |    40 |     2   (0)| 00:00:01 |
|   6 |    BUFFER SORT        |                | 15425 |   195K|    37   (3)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN  | IDX_REG_100K   | 15425 |   195K|    34   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

A tabela a seguir apresenta um resumo das estatísticas obtidas nas consultas.

Descrição Tabela Normal Tabela Externa Tabela Externa c/ Índice
recursive calls 41 62 52
db block gets 0 0 0
consistent gets 816 652 471
physical reads 0 0 33
redo size 0 0 0
bytes sent via SQL*Net to client 610 610 610
bytes received via SQL*Net from client 523 523 523
SQL*Net roundtrips to/from client 2 2 2
sorts (memory) 5 8 6
sorts (disk) 0 0 0
rows processed 1 1 1

Referências

Livro OCA Oracle Database 11g Administração I

Adauto Santos

Analista de Sistemas Senior na Unimed Regional Maringá. Formado em Informática pela Universidade Estadual de Maringá. Pós-Graduado em Administração de Banco de Dados pela Metrocamp e Business Intelligence pela Unicesumar.

WebSite: http://www.adautosantos.com.br/
LinkedIn: http://www.linkedin.com/in/adauto-santos-91278029
Facebook: https://www.facebook.com/adauto.santos.946

Envie seu comentário

Mensagem
Nome
E-mail

Comentários

Não há comentários no artigo. Seja o primeiro a comentar!