Oracle Chaining Block.sql

-- Activar el DBMS_OUTUPUT
SET SERVEROUTPUT ON

Begin
 Dbms_Output.Put_Line(Systimestamp);
End;

-- Creacion de tablas
CREATE TABLE TBL_EXAMPLE_CHAINED (
  Columna_1 number(15), 
  Columna_2 varchar2(2000),
  Columna_3 varchar2(2000),
  Columna_4 varchar2(2000),
  Columna_5 varchar2(2000),
  Columna_6 varchar2(2000)
);

create table CHAINED_ROWS (
  owner_name         varchar2(30),
  table_name         varchar2(30),
  cluster_name       varchar2(30),
  partition_name     varchar2(30),
  subpartition_name  varchar2(30),
  head_rowid         rowid,
  analyze_timestamp  date
);


-- Insert con 8K
DECLARE
  caracteres VARCHAR2(2000) := '';
BEGIN
  FOR i IN 1..2000 LOOP
    caracteres := caracteres | 'a';
  END LOOP;
  Dbms_Output.Put_Line('Caracteres: ' | caracteres);
  INSERT INTO TBL_EXAMPLE_CHAINED VALUES(1, caracteres, caracteres, caracteres, caracteres, caracteres);
END;

ANALYZE TABLE TBL_EXAMPLE_CHAINED LIST CHAINED ROWS;
SELECT COUNT(*) FROM CHAINED_ROWS;

DECLARE
  caracteres VARCHAR2(2000) := '';
BEGIN
  FOR i IN 1..2000 LOOP
    caracteres := caracteres || 'a';
  END LOOP;
  -- Migracion
  INSERT INTO TBL_EXAMPLE_CHAINED VALUES(12345678912345, caracteres, caracteres, caracteres, caracteres, '');
  UPDATE TBL_EXAMPLE_CHAINED SET Columna_6 = caracteres;
END;

CREATE TABLE X_TEMP AS SELEC * FROM TBL_EXAMPLE_CHAINED WHERE ROWID IN (SELECT head_rowid FROM CHAINED_ROWS WHERE table_name='TBL_EXAMPLE_CHAINED')

DELETE FROM TBL_EXAMPLE_CHAINED
 WHERE ROWID IN (SELECT head_rowid
 FROM CHAINED_ROWS  WHERE table_name='TBL_EXAMPLE_CHAINED');
 
INSERT INTO TBL_EXAMPLE_CHAINED
 SELECT * FROM X_TEMP;
 
DROP TABLE X_TEMP;

ANALYZE TABLE TBL_EXAMPLE_CHAINED LIST CHAINED ROWS;