Pipelined Table Functions – Funções para retornar “tabelas virtuais”
Neste artigo irei apresentar um recurso muito bom que existe no Oracle Database desde a versão 9i e chama-se Pipelined Table Function.
Este recurso permite criar funções que retornam dados como se fossem uma tabela virtual, podendo transformar os dados de retorno enquanto eles são produzidos, ou seja, é possível alterar os dados pesquisados em uma tabela, linha por linha, enquanto eles são processados, sem ter que esperar pelo retorno completo do “result set” (conjunto de dados que são retornados pela função).
Este recurso é ótimo para ETL (Extract, Transform, and Load), pois é rápido e consome menos memória que outros métodos que podem ser utilizados para o mesmo objetivo, como por exemplo, preencher um cursor e percorrê-lo para transformar e retornar dados.
Seguem abaixo 3 scripts que demonstram como criar e testar uma Pipelined Table Function. Os scripts utilizam a tabela EMPLOYEES do schema de exemplo HR.
Para iniciar o passo-a-passo dos itens abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA) ou com o usuário HR.
1- Criando a package HR.PKG_TYPES
A package HR.PKG_TYPES contém os tipos de dados que são criados para retornarem uma tabela virtual na função que será criada no próximo passo:
create or replace package HR.PKG_TYPES as
TYPE TABLEEMPTYPE IS TABLE OF EMPLOYEES%ROWTYPE;
TYPE ROWEMPTYPE IS RECORD(
EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE,
FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE,
LAST_NAME EMPLOYEES.LAST_NAME%TYPE,
EMAIL EMPLOYEES.EMAIL%TYPE,
PHONE EMPLOYEES.PHONE_NUMBER%TYPE,
HIRE_DATE EMPLOYEES.HIRE_DATE%TYPE,
JOB_ID EMPLOYEES.JOB_ID%TYPE,
SALARY EMPLOYEES.SALARY%TYPE,
COMMISSION_PCT EMPLOYEES.COMMISSION_PCT%TYPE,
MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE,
DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE
);
END;
/
Local Index vs. Global Index em tabela particionada
Mar 31, 2010 SQL
A maioria das pessoas tem essas perguntas sobre Index Partition:
- O que é um Local Index?
- O que é um Global Index?
- Quando você forçaria a criação de um Global Index em uma partition table?
- Quando você recomendaria criar um Global Index em vez de um Local Index?
Para responder a essas perguntas….
1. O que é um Local Index?
Local Indexes particionados são mais fáceis de gerenciar, cada partição do Local Index está associado a uma partição. Eles também oferecem maior disponibilidade e são comuns em ambientes de DSS. Quando tomamos qualquer ação (MERGE, SPLIT,EXCHANGE etc) em um Local Index, isso impacta apenas aquela partição e as outras estarão disponíveis. Nós não podemos adicionar explicitamente um Local Index para uma nova partição. O Local Index será adicionado implicitamente a nova partição, quando for criada uma nova partição na tabela. Da mesma forma, não podemos dropar o índice local em uma partição específica. Ele pode ser dropado automaticamente quando nós dropamos a partição da tabela subjacente. Local Indexes podem ser UNIQUE quando a chave da partição é parte do índice composto. Unique Local Indexes são úteis para o ambiente OLTP. Podemos também criar bitmap indexes em tabelas, com a restrição de que os índices de bitmap deve ser local para a tabela particionada. Eles não podem ser Global Indexes.
SQL> CREATE TABLE employees 2 (employee_id NUMBER(4) NOT NULL, 3 last_name VARCHAR2(10), 4 department_id NUMBER(2)) 5 PARTITION BY RANGE (department_id) 6 (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE ODS_STAGE_DATA, 7 PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE ODS_STAGE_DATA, 8 PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE ODS_STAGE_DATA); Table created. SQL> declare 2 v_no number :=1; 3 begin 4 delete employees; 5 for i in 1..10 loop 6 insert into employees values(v_no,'name...',v_no); 7 v_no := v_no+1; 8 end loop; 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> create index idx_local on employees(last_name) local; Index created. SQL>
Tags: Index, Oracle 10g, Oracle 11g, Partition, Tuning
PLAN_TABLE faz aniversário de 21 anos em 19-out-2009
Oct 13, 2009 SQL
Pois é isso mesmo! A querida tabela do ORACLE que nos mostra o plano de execução dos SQLs hoje completa 21 anos!
Hoje eu fui olhar um plano de execução e me deparei com a seguinte mensagem:
SET AUTOTRACE ON SELECT bla bla bla... Execution Plan ---------------------------------------------------------- -------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 102 (0)| | 1 | SORT AGGREGATE | | 1 | | | 2 | TABLE ACCESS FULL| TABELA | 4322 | 102 (0)| -------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version
PLAN_TABLE is old version ???
Read the rest of this entry »
Tags: Curiosidade
Melhorias nos EXTERNAL TABLES do 10g
Jan 13, 2009 SQL
A partir do Oracle 9i foi criado o conceito de EXTERNAL TABLES, ou seja, você cria uma tabela baseado num arquivo texto no sistema operacional e pode fazer consultas SQL nessa tabela (ou seja, diretamente no arquivo texto como se fosse uma tabela) Read the rest of this entry »
Agora a partir do oracle 10g é possível também criar um arquivo texto baseado numa tabela do banco usando o novo driver de Data Pump existente.
Tags: External Table, Oracle 10g, SQL
Novas funcionalidades com CONNECT BY
Jan 12, 2009 SQL
A partir do Oracle 10g temos mais algumas funcionalidades no uso da cláusula CONNECT BY dentro dos comandos SELECT. Essas mudanças se aplicam a queries hiearquicas permitindo o retorno de não apenas PAIS, FILHOS mas também “ancestrais”. São 3 as novas cláusulas disponíveis com CONNECT BY.
Read the rest of this entry »
Tags: Connect By, Oracle 10g, SQL
Cláusula RETURNING com funções somatórias
Jan 9, 2009 SQL
Oracle 10g agora permite o uso de funções somatórias na cláusula RETURNING. O seguinte exemplo faz um UPDATE no salário de todos empregados e retorna a média salarial resultante para as linhas afetadas.
Read the rest of this entry »
Tags: Oracle 10g, SQL
Ignorando Acentos e Maiúsculas/Minúsculas no 10g
Jan 8, 2009 SQL
O Oracle 10g agora permite fazer consultas considerando ou não acentos e maiúsculas/minúsculas. Isso é feito pelo uso do parâmetro NLS_SORT. Vamos aos exemplos.
Read the rest of this entry »
Tags: Acentuação, Oracle 10g, SQL
Select que retorna os Números Primos
Oct 11, 2007 SQL
Esse post vai mais a título de curiosidade. Sabemos que os números primos são números naturais que podem ser divididos apenas por dois números naturais, o 1 (um) e ele mesmo. Pois a tempos que eu queria um SQL que retornasse esses números!
Read the rest of this entry »
Tags: Curiosidade, Matemática, SQL
Como usar Índices Virtuais – NOSEGMENT INDEXES
Sep 25, 2007 SQL
Essa é para quem faz tunning de SQL. Digamos que você quer verificar se a criação de um novo índice vai ser realmente usada pelo otimizador. Antes de criá-lo, você pode testar se ele resolverá seu problema atraves de INDICE VIRTUAL.
Read the rest of this entry »
Ver Plano de execução de um SQL rodando
Sep 25, 2007 SQL
Imagine que você rodou um SQL que está demorando muito. O Trace está desligado e você quer saber o que pode estar errado com o SQL. Com essa dica, você enxerga o plano de execução deste SQL.
Read the rest of this entry »
