ORACLE

Particionar tabla Oracle, y llenar automaticamente con registros

Para fragmentar horizontal una tabla y llenar automaticamente .
create table tablanormal
(
llave integer,dato varchar(10)
);
create table tablaparticionada
(
llave integer,dato varchar(10)
);

partition by range(llave)
(
partition p0 values less than (100000) ,
partition p1 values less than (200000) ,
partition p2 values less than (300000) ,
partition p3 values less than (400000) ,
partition p4 values less than (500000) ,
partition p5 values less than (600000) ,
partition p6 values less than (700000) ,

partition p7 values less than (800000) ,
partition p8 values less than (900000) ,
partition p9 values less than (1000001)
);

CREATE OR REPLACE PROCEDURE insertartablanormal(valor IN NUMBER) AS
BEGIN
declare
v number(7) := 1;
dato     varchar2(10);

begin
while v <= valor loop

insert into tablanormal (llave, dato) values (v, ‘dato’);
v := v + 1;

end loop;
end;
end insertar_particionada;

CREATE OR REPLACE PROCEDURE insertatablaparticionada(valor IN NUMBER) AS
BEGIN
declare
v number(7) := 1;
dato     varchar2(10);

begin
while v <= valor loop

insert into particionada (llave, dato) values (v, ‘dato’);
v := v + 1;

end loop;
end;
end insertar_particionada;

truncate tablanormal;
truncate tablaparticionada;
select insertatablanormal(1000000);
select insertatablaparticionada(1000000);
SELECT * FROM tablanormal ;
SELECT * FROM tablaparticionada;
UPDATE tablanormal SET dato = ’00ABCDEFGHI123′;
UPDATE tablaparticionada SET dato = ‘AAABCDEFG12345’;
DELETE FROM tablanormal;
DELETE FROM tablaparticionada;

Continue reading