Particionamento de tabelas Oracle


O particionamento de tabelas e de indices auxilia no tratamento e na seleção destes dados. Os dados são armazenados em segmentos, e estes segmentos que estão armazenados nessas particoes podem sofrer manutençoes diretamente.


Para isso temos as seguintes tipos de partições:


Range – E a mais comum e pelo nome ja diz baseada em cima de um range da chave.
Hash – A partir de um algoritmo os dados são armazenados nas partições a partir de uma chave de particionamento.
List – Os dados sao mapeados as particoes a partir de listas.
Interval – Os dados sao mapeados as particoes a partir de intervalos que são automaticamente criados de acordo com o intervalo.
System – Os dados sao mapeados as particoes de forma arbitraria – via o programa que os esta tratando.
Podemos ainda ter uma subparticao, para que possamos ter uma opção de aproveitar ainda mais aquela partição
 ja existente.
  • Range top level: – Range-Range
    – Range-Hash
    – Range-List 
  • List top level:
    – List-Range – List-Hash – List-List 
  • Interval top level: – Interval-Range
    – Interval-Hash – Interval-List
 A chave do particionamento e que comanda, como os dados vão ser inseridos, e cada partição pode ter no máximo 16 colunas como chave. Podemos também fazer os comandos DML e select diretamente a essas chaves. Nenhum dos comandos existentes seja DML ou SELECT precisa ser alterado para que se utilize particionamento.
Existe um tipo de particionamento que esta baseado no relacionamento das tabelas. Ou seja, pela constraint de chave estrangeira. Ainda, temos também o particionamento que pode ser baseado em uma coluna virtual ou seja calculada.
Quais sao os beneficios de se utilizar o particionamento ?
O particionamento automaticamente faz com que o otimizador do oracle exclua as partições que não são interessantes para resolver os comandos. 
As particoes podem sofrer manutenção diretamente sem ter outras partições comprometidas para os comandos de DML e SELECT.
As partições vão contribuir diretamente com os recursos de gerenciamento de ciclo de vida da sua informação.
Voce pode atribuir uma particao a uma tablespace, dai podendo armazenar seus datafiles em locais distintos.
Varios comandos poderemos executar para realizar a manutenção nas partições:
  • ALTER TABLE ADD PARTITION 
  • ALTER TABLE DROP PARTITION (RANGE) 
  • ALTER TABLE TRUNCATE PARTITION (RANGE) 
  • ALTER TABLE MOVE PARTITION 
  • ALTER TABLE SPLIT PARTITION 
  • ALTER TABLE EXCHANGE PARTITION 
  • ALTER TABLE RENAME PARTITION 
  • ALTER TABLE MODIFY PARTITION 
  • ALTER TABLE COALESCE PARTITION
A criação de uma tabela particionada pode ser feita usando o comando abaixo:
create table exemplo
(idx number, descricao varchar2(2))
partition by range (idx)
(partition values less than (0)  tablespace users,
partition values less than (maxvalue) tablespace users1);
Operações diretamente nas partições:
select idx from complex PARTITION (particao1);
insert into complex PARTITION (particao2) values (4,5);
delete from complex PARTITION (particao3);
create table teste as select * from tabela partition(particao1);
expdp sh/sh tables(SALES:SALES_Q1_20001)
Dicionario de dados para consultar as partições
DBA_TAB_PARTITIONS
DBA_SUBPATITION_TEMPLATES
DBA_SEGMENTS

Name
Purpose
N
DBA_TABLES
T
DBA_PART_TABLES
Partition type: default values
T
DBA_TAB_*PARTIT
IONS
Partitions detail
P
DBA_*PART_KEY_COLUMNS
Partition keys
P

T = Tabela
P = Particao
SQL> SELECT TABLE_NAME, TABLESPACE_NAME,

  2         PARTITIONED, ROW_MOVEMENT
3 FROM USER_TABLES ;
TABLE_NAME TABLESPACE_NAME PARTITIONED ROW_MOVE
---------- --------------- ----------- --------
HR_EMP                     YES
MULTICOL YES
ORDINARY USERS NO
SIMPLE YES
ENABLED
ENABLED

DISABLED


SQL> SELECT TABLE_NAME, PARTITIONING_TYPE,
2 SUBPARTITIONING_TYPE, PARTITION_COUNT,
3 PARTITIONING_KEY_COUNT,DEF_TABLESPACE_NAME 4 FROM USER_PART_TABLES ;

TABLE_NAME TYPE   SUBTYPE PAR.CNT PAR.KEY_CNT DEF_TAB.SP
---------- ------ ------- ------- ----------- ----------
COMPOS     RANGE  HASH          3
MULTICOL RANGE NONE 4
SIMPLE LIST NONE 2
1 USERS
2 USERS 

1 USERS



Name
Columns to show
DBA_SEGMENTS
PARTITION_NAME, SEGMENT_TYPE
DBA_EXTENTS
PARTITION_NAME, SEGMENT_TYPE
DBA_OBJECTS
SUBOBJECT_NAME, OBJECT_TYPE

Global index
create index idx on employees ( first_name )
global partition by range (first_name)
( partition x1 values less than (‘H’) tablespace users, 
  partition x2 values less than (maxvalue) );
Local index tabela particionada mesmas tablespaces da tabela
create index idx on employees ( first_name ) local;
Local index tabela particionada mas em outras tablespaces
create index idx on employees ( first_name ) 
 local
 partition partition1 tablespace idx1,
 partition partition2 tablespace idx2
 );
Algumas curiosidades quando usando tabelas particionadas:
  • O particionamento e pago a parte, sendo uma option do banco instalado como enterprise database
  • Algumas sentenças SELECT podem ser automaticamente dividas para que a resolução seja mais rápida
  • As operações de delete e inserts são enxergadas pelo dicionário somente como simples operação de organização.
  • As operações nos indices podem ser individualizadas (usando local indexes)
  • Podemos ter ate 1048575 partições por tabela ou índice
  • Podemos automatizar a criação das tabelas através de templates para as partições
  • As operações nos indices globais afetam as operações nas tabelas
  • As operações nos indices locais não afetam as operações nas tabelas como um todo
  • Os indices globais podem ser usados em tabelas particionadas ou nao. Os indices locais somente em tabelas particionadas.
  • < span>Os indices locais sao automaticamente mantidos. Ou seja, as partições alteradas nas tabelas são automaticamente alteradas nos indices.

Particionamento em indices

Diferente do particionamento da tabela, temos o particionamento do indice. O particionamento do indice pode existir independente da tabela ser particionada ou nao. Isso ocorre uma flexibilizacao a mais e importante para a manutencao destes dados.

Atributos: Global ou  Local

O indice sendo global pode ter outro tipo de particionamento que o da tabela, ou mesmo se a tabela nao tiver nenhum particionamento.  O indice global pode utilizar somente as tecnicas range e hash.
Os tipos de particionamento validos para o indice global . O indice sendo particionado local segue a mesma regra de particionamento da tabela, sendo que o indice local somente pode ser criado para a tabela que e particionadada.

Concorrencia pelo indice

Geralmente temos o evento buffer busy em uma frequencia muito grande de DML. Dai em um rac por exemplo para se evitar essa incidencia do evento, se cria o indice reverso. Isso resolveria em parte a concorrencia, mas para resolver de uma forma eficiente e conservar a mesma ordem o ideal e criar o indice global usando a tecnica de hash. Como o hash fara com que o indice seja designado a varias tablespaces, criaremos entao varios pequenos locais de contencao e nao um unico ponto de contencao geral, como resultado aumentaremos a eficiencia geral.

Os indices particionados tem duas categorias de conceito logico

Prefixo – A chave de partição é a coluna mais à esquerda (s) do índice. Sondagem este tipo de índice é menos onerosa. Se uma consulta especifica a chave de partição na partição onde particao de pesquisa é possível, isto é, nem todas as partições serão pesquisados.
Não-prefixadas – Não suporta partição de pesquisa, mas é eficaz no acesso aos dados que abrange várias partições. Muitas vezes usado para indexar uma coluna que não é a chave de partição tabelas, quando você gostaria que o índice a ser dividido na mesma chave que a tabela subjacente.
Isso depende como o acesso e feito e como as chaves estao armazenadas. Dai pode ser simplesmente um conceito logico sem nenhuma distincao ate na sintaxe do comando da criacao do indice local.

Local Prefixed Indexes

  • Somente possivel em tabelas particionadas
  • Ocupa menos espaco porque se refere a tabela particionada e suas devidas particoes
  • Melhor performance quando utilizada para operacoes massivas com paralelismo
  • Podem ser unique ou nao unique
  • Btree ou Bitmap
  • A condicao e que o indice seja criado com mesmas colunas da chave de particionamento da tabela
Exemplo:
Create index idxp1 on emp(first_name) local;
Create index idxp1 on emp(first_name) 
tablespace indx04
local
partition ex1 tablespace indx01,
partition ex2 tablespace indx02,
partition ex3 tablespace indx03
);

Local Nonprefixed Index

Indices que sao criados em tabelas particionadas
A chave do indice nao e a mesma da chave do particionamento, mesmo que a chave de particionamento seja a mesma, a chave de ordenacao nao e.
Pode ser nao unica
Pode ser bitmap ou btree
Utilizada quando as consultas envolvem a busca em mais particoes, ou que geralmente temos que varrer o indice ou muitas particoes
Exemplo:
create index idxp on emp (hire_date) local;

Tipos de particionamento de Indice

Index Types
Global (Range or Hash)
Local (All)
B-tree
Yes
Yes
Bitmap
No
Yes
Bitmap Join
No
Yes
Function
No (*)
Yes
Secondary IOT
No
Yes
Cluster*
No
No

O indice normalmente criado na tabela, tambem pode ser chamado de GLOBALNONPARTITIONED INDEX

O Status do indice – UNUSABLE

Quando o indice fica no status de unusable, o indice nao e mais usado pelo otimizador e ocorrerao erros ao executar o DML. Para isso temos alguns detalhes a serem seguidos:
Parametro de inicializacao SKIP_UNUSABLE_INDEXES – quando configurado como TRUE que e o padrao o banco de dados o oracle ira executar o DML desde que haja um indice unique com status unusable, mas os indices nao sao mantidos aqueles com status unusable.
Se em um indice particionado uma ou mais particoes estiverem com status unusable, o otimizador nao ira usar o indice para a combinacao de particoes para resolucao de consultas ou o partitioning pruning. Para as demais, utilizara as particoes que estao usable.
Truncate de uma tabela com indices particionados invalidos, faz com que estes indices invalidos fiquem validos.
Os indices invalidos devem ser reconstruidos ou removidos.

Criando o indice como PARTIAL ou FULL Versao 12c

O indice na versao 12c pode ser criado somente em algumas particoes da tabela, para agilizar sua utilizacao. Nas versoes anteriores as do 12c, nao temos tanta flexibilizacao na criacao, manutencao e remocao de particoes de indices.
Exemplos
create index orders_idx_partial on orders(order_total) global indexing partial;
ou
create index orders_idx_partial on orders(order_total) global indexing full;