CREATE
  TABLE "RRG"."RRGQTZY_M"
  (
    "CODE_INFORMATION" VARCHAR2(4 CHAR) NOT NULL ENABLE,
    "INFORMATION"      VARCHAR2(25 CHAR) NOT NULL ENABLE,
    "DATE_DEBUT_VALIDITE" DATE DEFAULT SYSDATE NOT NULL ENABLE,
    "DATE_FIN_VALIDITE" DATE DEFAULT TO_DATE('31/12/2999','DD/MM/YYYY') NOT NULL ENABLE,
    "POSITION_DEB" NUMBER,
    "LONGUEUR"     NUMBER,
    "DATE_CREATION" DATE DEFAULT sysdate,
    "DATE_MODIFICATION" DATE,
    "DATE_SUPPRESSION" DATE,
    "USER_MAJ" VARCHAR2(15 CHAR) NOT NULL ENABLE,
    CONSTRAINT "RRGQCZY_M" PRIMARY KEY ("CODE_INFORMATION", "INFORMATION", "DATE_DEBUT_VALIDITE", "DATE_FIN_VALIDITE") ENABLE
  )

REM INSERTING into RRGQTZY_M
Insert into RRGQTZY_M (CODE_INFORMATION,INFORMATION,DATE_DEBUT_VALIDITE,DATE_FIN_VALIDITE,POSITION_DEB,LONGUEUR,DATE_CREATION,DATE_MODIFICATION,DATE_SUPPRESSION,USER_MAJ) values ('ZY00','SOCCLE',to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),to_date('31/12/2999 00:00:00','DD/MM/YYYY HH24:MI:SS'),1,3,to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),null,null,'RRG_INIT');
Insert into RRGQTZY_M (CODE_INFORMATION,INFORMATION,DATE_DEBUT_VALIDITE,DATE_FIN_VALIDITE,POSITION_DEB,LONGUEUR,DATE_CREATION,DATE_MODIFICATION,DATE_SUPPRESSION,USER_MAJ) values ('ZY00','MATCLE',to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),to_date('31/12/2999 00:00:00','DD/MM/YYYY HH24:MI:SS'),4,12,to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),null,null,'RRG_INIT');
Insert into RRGQTZY_M (CODE_INFORMATION,INFORMATION,DATE_DEBUT_VALIDITE,DATE_FIN_VALIDITE,POSITION_DEB,LONGUEUR,DATE_CREATION,DATE_MODIFICATION,DATE_SUPPRESSION,USER_MAJ) values ('ZY00','IDGPRG',to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),to_date('31/12/2999 00:00:00','DD/MM/YYYY HH24:MI:SS'),16,10,to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),null,null,'RRG_INIT');
Insert into RRGQTZY_M (CODE_INFORMATION,INFORMATION,DATE_DEBUT_VALIDITE,DATE_FIN_VALIDITE,POSITION_DEB,LONGUEUR,DATE_CREATION,DATE_MODIFICATION,DATE_SUPPRESSION,USER_MAJ) values ('ZY00','TYPDOS',to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),to_date('31/12/2999 00:00:00','DD/MM/YYYY HH24:MI:SS'),26,3,to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),null,null,'RRG_INIT');
Insert into RRGQTZY_M (CODE_INFORMATION,INFORMATION,DATE_DEBUT_VALIDITE,DATE_FIN_VALIDITE,POSITION_DEB,LONGUEUR,DATE_CREATION,DATE_MODIFICATION,DATE_SUPPRESSION,USER_MAJ) values ('ZY00','BLOB01',to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),to_date('31/12/2999 00:00:00','DD/MM/YYYY HH24:MI:SS'),29,1,to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),null,null,'RRG_INIT');
Insert into RRGQTZY_M (CODE_INFORMATION,INFORMATION,DATE_DEBUT_VALIDITE,DATE_FIN_VALIDITE,POSITION_DEB,LONGUEUR,DATE_CREATION,DATE_MODIFICATION,DATE_SUPPRESSION,USER_MAJ) values ('ZY00','QUIMAJ',to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),to_date('31/12/2999 00:00:00','DD/MM/YYYY HH24:MI:SS'),30,30,to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),null,null,'RRG_INIT');
Insert into RRGQTZY_M (CODE_INFORMATION,INFORMATION,DATE_DEBUT_VALIDITE,DATE_FIN_VALIDITE,POSITION_DEB,LONGUEUR,DATE_CREATION,DATE_MODIFICATION,DATE_SUPPRESSION,USER_MAJ) values ('ZY00','HORMAJ',to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),to_date('31/12/2999 00:00:00','DD/MM/YYYY HH24:MI:SS'),60,19,to_date('22/07/2011 08:43:05','DD/MM/YYYY HH24:MI:SS'),null,null,'RRG_INIT');

create or replace
FUNCTION         GET_RUBRIQUE(
              p_codeInformation RRGQTZY_M.CODE_INFORMATION%TYPE,
              p_information RRGQTZY_M.INFORMATION%TYPE,
              p_informationData VARCHAR2,
              p_dateCreation DATE)
  RETURN VARCHAR2
  DETERMINISTIC
  IS
  v_longueur  NUMBER;
  v_posDeb    NUMBER;
BEGIN
    BEGIN
      SELECT LONGUEUR, POSITION_DEB INTO v_longueur, v_posDeb
      FROM RRGQTZY_M
      WHERE CODE_INFORMATION=p_codeInformation
        AND INFORMATION=p_information
        AND p_dateCreation BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
      WHEN TOO_MANY_ROWS THEN
        SELECT LONGUEUR, POSITION_DEB INTO v_longueur, v_posDeb
        FROM (
          SELECT LONGUEUR, POSITION_DEB, RANK() OVER(PARTITION BY CODE_INFORMATION, INFORMATION ORDER BY DATE_CREATION DESC) rnk
          FROM RRGQTZY_M
          WHERE CODE_INFORMATION=p_codeInformation
            AND INFORMATION=p_information
            AND p_dateCreation BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE)
        WHERE rnk = 1;
    END;
  RETURN TRIM(SUBSTR(p_informationData, v_posDeb, v_longueur));
END GET_RUBRIQUE;

CREATE
  TABLE "RRGQTZY00"
  (
    "MATRICULE_RH"         VARCHAR2(8 CHAR) NOT NULL ENABLE,
    "AVANT_DERNIER_MATCLE" NUMBER(1,0) GENERATED ALWAYS AS (TO_NUMBER(SUBSTR(
    "MATRICULE_RH",7,1))) VIRTUAL VISIBLE NOT NULL ENABLE,
    "DINFOIDENTITE" TIMESTAMP (0) NOT NULL ENABLE,
    "DINFOIDENTITE_INFORMATION" TIMESTAMP (0) NOT NULL ENABLE,
    "ACTIF"            NUMBER(1,0) NOT NULL ENABLE,
    "INFORMATION_DATA" VARCHAR2(4000 CHAR),
    "SOCCLE"           VARCHAR2(4000 CHAR) GENERATED ALWAYS AS ("GET_RUBRIQUE"(
    'ZY00','SOCCLE',"INFORMATION_DATA","DATE_CREATION")) VIRTUAL VISIBLE ,
    "MATCLE" VARCHAR2(4000 CHAR) GENERATED ALWAYS AS ("GET_RUBRIQUE"('ZY00',
    'MATCLE',"INFORMATION_DATA","DATE_CREATION")) VIRTUAL VISIBLE ,
    "IDGPRG" NUMBER GENERATED ALWAYS AS (TO_NUMBER("GET_RUBRIQUE_NUM"('ZY00',
    'IDGPRG',"INFORMATION_DATA","DATE_CREATION"))) VIRTUAL VISIBLE ,
    "TYPDOS" VARCHAR2(4000 CHAR) GENERATED ALWAYS AS ("GET_RUBRIQUE"('ZY00',
    'TYPDOS',"INFORMATION_DATA","DATE_CREATION")) VIRTUAL VISIBLE ,
    "BLOB01" VARCHAR2(4000 CHAR) GENERATED ALWAYS AS ("GET_RUBRIQUE"('ZY00',
    'BLOB01',"INFORMATION_DATA","DATE_CREATION")) VIRTUAL VISIBLE ,
    "QUIMAJ" VARCHAR2(4000 CHAR) GENERATED ALWAYS AS ("GET_RUBRIQUE"('ZY00',
    'QUIMAJ',"INFORMATION_DATA","DATE_CREATION")) VIRTUAL VISIBLE ,
    "HORMAJ" DATE GENERATED ALWAYS AS ("GET_RUBRIQUE_HORMAJ"('ZY00','HORMAJ',
    "INFORMATION_DATA","DATE_CREATION")) VIRTUAL VISIBLE ,
    "DATE_CREATION" DATE DEFAULT SYSDATE NOT NULL ENABLE,
    "DATE_MODIFICATION" DATE,
    "DATE_SUPPRESSION" DATE,
    "USER_MAJ" VARCHAR2(15 CHAR),
    CONSTRAINT "RRGQCZY00" PRIMARY KEY ("MATRICULE_RH", "AVANT_DERNIER_MATCLE",
    "DINFOIDENTITE", "DINFOIDENTITE_INFORMATION", "ACTIF") ENABLE
  )
  PARTITION BY RANGE
  (
    "DINFOIDENTITE"
  )
  INTERVAL
  (
    (NUMTOYMINTERVAL(1,'MONTH'))
  )

SET timing ON

select max(matcle) from (
SELECT matricule_rh, actif, dinfoidentite, dinfoidentite_information, information_data , idgprg, matcle, typdos
FROM rrgqtzy00)
;

Elapsed: 00:00:03.528

select max(matcle) from (SELECT zy00.MATRICULE_RH, zy00.actif, zy00.dinfoidentite, zy00.dinfoidentite_information, 
SUBSTR(INFORMATION_DATA, 
              (SELECT POSITION_DEB FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'SOCCLE' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE),
              (SELECT LONGUEUR FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'SOCCLE' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE)) SOCCLE,
SUBSTR(INFORMATION_DATA, 
              (SELECT POSITION_DEB FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'MATCLE' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE),
              (SELECT LONGUEUR FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'MATCLE' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE)) MATCLE,
TO_NUMBER(SUBSTR(INFORMATION_DATA, 
              (SELECT POSITION_DEB FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'IDGPRG' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE),
              (SELECT LONGUEUR FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'IDGPRG' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE))) IDGPRG,
SUBSTR(INFORMATION_DATA, 
              (SELECT POSITION_DEB FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'TYPDOS' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE),
              (SELECT LONGUEUR FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'TYPDOS' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE)) TYPDOS,
SUBSTR(INFORMATION_DATA, 
              (SELECT POSITION_DEB FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'BLOB01' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE),
              (SELECT LONGUEUR FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'BLOB01' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE)) BLOB01,
SUBSTR(INFORMATION_DATA, 
              (SELECT POSITION_DEB FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'QUIMAJ' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE),
              (SELECT LONGUEUR FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'QUIMAJ' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE)) QUIMAJ,
TO_DATE(SUBSTR(INFORMATION_DATA, 
              (SELECT POSITION_DEB FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'HORMAJ' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE),
              (SELECT LONGUEUR FROM RRGQTZY_M WHERE CODE_INFORMATION = 'ZY00' AND INFORMATION = 'HORMAJ' AND ZY00.DATE_CREATION BETWEEN DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE)), 'YYYY-MM-DD-HH24.MI.SS') HORMAJ
              FROM rrgqtzy00 zy00);

Elapsed: 00:00:00.398