Quando temos uma tabela muito grande, ou seja com milhões de linhas a melhor opção é particiona-la para uma melhor performance do banco. Para isso, é necessário algumas regras na tabela principal e criar as tabelas auxiliares:
Veja abaixo.
1 – Crie a tabela principal
CREATE TABLE salario(
funcionario numeric(10) NOT NULL,
centro_custo character varying(10) NOT NULL,
valor_resultado numeric(13,2),
data_evento date,evento numeric(3),
conta numeric(10),cargo numeric(5)
)
WITH (OIDS=TRUE);
2 – Crie as tabelas particionadas para inserção dos dados, sendo que as mesmas devem herdar as caracteristicas da tabela principal, para isso usamos o () INHERITS
–JANEIRO
CREATE TABLE salario_jan () INHERITS (salario);
–FEVEREIRO
CREATE TABLE salario_fev () INHERITS (salario);
……………………..
–DEZEMBRO
CREATE TABLE salario_dez () INHERITS (salario);
3 – Crie a regra que no caso de uma tabela de pagamentos de funcionarios, Nesse caso, o campo referencia é a data de pagamento tendo base o mês. Sendo assim crie uma rule para cada mês, onde a cada insert feito na tabela mãe … os dados são filtrados e inseridos em cada tabela filha correspondende ao mês.
– JANEIRO
CREATE OR REPLACE RULE insert_jan AS
ON INSERT TO salario
WHERE date_part (‘month’::text, new.data_evento)=1
DO INSTEAD INSERT INTO salario_jan (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);
– FEVEREIRO
CREATE OR REPLACE RULE insert_fev AS
ON INSERT TO salario
WHERE date_part(‘month’::text, new.data_evento)=2
DO INSTEAD INSERT INTO salario_fev (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);
……………………………
– DEZEMBRO
CREATE OR REPLACE RULE insert_dez AS
ON INSERT TO salario
WHERE date_part(‘month’::text, new.data_evento)=12
DO INSTEAD INSERT INTO salario_dez (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);
Explicando …
A função data_part, vai extrair uma determinada parte da data a ser imposta pela regra.
No caso desse particionamento a referencia é o mês. Sendo assim no momento do insert, será verificado o mês em questão e redirecionado para tabela particionada correspondente.
É importante dizer que se um registro não atender a uma determinada regra, ele será inserido na tabela principal.
E que a tabela principal se comportará como uma tabela MERGE, sendo assim ela mostrará todos os registros !!
4 – Criando os Indices ..
Para a busca ser mais rápida é interessante a criação de indice nas tabelas particionadas.
– JANEIRO
CREATE INDEX salario_jan_idx
ON salario_jan
USING btree
(data_evento);
– FEVEREIRO
CREATE INDEX salario_fev_idx
ON salario_fev
USING btree
(data_evento);
………………………..
– DEZEMBRO
CREATE INDEX salario_dez_idx
ON salario_dez
USING btree
(data_evento);
4 – Criando as views …..
As views vão auxiliar na vizualização dos dados particionados por mês, sendo assim, algumas views são interessantes ….
View para cada mês CORRENTE.
CREATE OR REPLACE VIEW vw_salario_jan AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_jan a
WHERE date_part(‘month’::text, data_evento) = 1
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;
CREATE OR REPLACE VIEW vw_salario_fev AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_fev a
WHERE date_part(‘month’::text, data_evento) = 2
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;
—————–
CREATE OR REPLACE VIEW vw_salario_dez AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_dez a
WHERE date_part(‘month’::text, data_evento) = 12
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;
View de anos anteriores.
Para vizualização dos anos anteriores as seguintes views
CREATE OR REPLACE VIEW vw_salario_2006 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2006
ORDER BY a.data_evento;
CREATE OR REPLACE VIEW vw_salario_2007 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2007
ORDER BY a.data_evento;
CREATE OR REPLACE VIEW vw_salario_2008 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2008
ORDER BY a.data_evento;
View dos ultimos 6 meses apartir do 1o dia util
CREATE OR REPLACE VIEW vw_6meses_anteriores AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta,a.cargo
FROM salario a
WHERE a.data_evento >= (current_date – (date_part( ‘day’ , current_date)::integer -1)) – interval ‘6 months’
ORDER BY a.data_evento;
Bom … apartir dai .. é possivel a criação de varias views para ver os dados …
Espero que seja util para vcs como foi pra mim !!!!
Kenia Milene
