Home » RDBMS Server » Performance Tuning » Virtual Columns : function based very slow (Oracle 11.2.0.2.0 )
Virtual Columns : function based very slow [message #525134] |
Thu, 29 September 2011 07:00 |
|
ric90
Messages: 42 Registered: May 2011 Location: Belfort
|
Member |
|
|
Hi all,
i create tables which contains a VARCHAR2(4000) that i want to split in virtual column.
I have a mapping table for each field.
I have a problem, because select on my tables with virtual fields is very long.
First of all, my mapping table
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
)
Datas to fill this table
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');
The functions i use in my definitive table :
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;
And my definitive table
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'))
)
When i test my table, whiwh actually contains 300 000 lines, i obtain this :
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
But, the data of table are exactly the same as the follow query :
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
Is there a way to optimize my function to obtain the same elapsed as my query ?
I tried many functions (returning only POSITION_DEB and LONGUEUR), or several functions to have only one by INFORMATION, but all solutions i tested are worst than the first.
Does anyone use the virtual columns ? Do you have any suggestion to optimize my virtual columns ?
Thanks in advance for you help.
|
|
|
Goto Forum:
Current Time: Wed Jul 10 13:14:56 CDT 2024
|