Scripts:
CREATE TABLE "EMP"
( "NAME" VARCHAR2(20 BYTE),
"LOCATION" VARCHAR2(20 BYTE),
"EXTRACT_DATE" date
);
CREATE TABLE "PARAMETER"
( "PARAMETER_NAME" VARCHAR2(20 BYTE),
"VALUE" VARCHAR2(20 BYTE)
) ;
INSERT INTO "PARAMETER" (PARAMETER_NAME, VALUE) VALUES ('EXTRACT_DATE', '24072017');
****************************
select distinct name from emp c
where not exists
(
select 1 from
(
select * from emp where extract_date =to_date((select trim(value) from parameter where trim(parameter_name)='EXTRACT_DATE'),'DDMMYYYY')
) d
where c.name=d.name and location is null )
***************************
I am firing the above query in 11.2.0.2.0 version and I am using two table (emp having 6 million records and parameter table having one records ).
The result of this query is taking ages.
***************************************
select distinct name from emp c
where not exists
(
select 1 from
(
select /*+ parallel (a) */ * from emp a where extract_date =to_date((select trim(value) from parameter where trim(parameter_name)='EXTRACT_DATE'),'DDMMYYYY')
) d
where c.name=d.name and location is null )
@@@@@@
select distinct name from emp c
where not exists
(
select 1 from
(
select /*+ parallel (a) */ * from emp a where extract_date ='24-JUL-2017'
) d
where c.name=d.name and location is null )
above queries when used with parallel and another one with hardcoding giving me output in 1 second.
********************************************************
Could someone tell me the reason for this ...why first query is too slow... what wil happen if emp table is analysed lately..
************************
Message was edited by: vishurocks007 and in create scripts for emp table ..extract_date was missing..
Message was edited by: vishurocks007
********************
create or replace PROCEDURE COMPARE
AS
CURSOR CUR1 IS SELECT DISTINCT root,src_system FROM dm WHERE SRC_SYSTEM='C';
CURSOR CUR2 IS SELECT DISTINCT root,src_system FROM dm WHERE SRC_SYSTEM='D';
C_MID_DM_MST VARCHAR2(2000) ;
C_MID_MST_DM VARCHAR2(2000) ;
D_MID_DM_MST VARCHAR2(2000) ;
D_MID_MST_DM VARCHAR2(2000) ;
C_MID_MST_CD VARCHAR2(2000) ;
C_MID_CD_MST VARCHAR2(2000) ;
C_MID_CD_DM VARCHAR2(2000) ;
C_MID_DM_CD VARCHAR2(2000) ;
D_MID_MST_CD VARCHAR2(2000) ;
D_MID_CD_MST VARCHAR2(2000) ;
D_MID_CD_DM VARCHAR2(2000) ;
D_MID_DM_CD VARCHAR2(2000) ;
BEGIN
FOR I IN CUR1
loop
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO C_MID_CD_DM FROM
(SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO C_MID_DM_CD FROM
(SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO C_MID_MST_CD FROM
(SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO C_MID_CD_MST FROM
(SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO C_MID_MST_DM FROM
(SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO C_MID_DM_MST FROM
(SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
INSERT INTO INTER_TEMP(ROOT,SRC_SYSTEM,MID_CD_DM,MID_DM_CD,MID_MST_CD,MID_CD_MST,MID_MST_DM,MID_DM_MST )
VALUES(I.ROOT,I.SRC_SYSTEM,C_MID_CD_DM,C_MID_DM_CD,C_MID_MST_CD,C_MID_CD_MST,C_MID_MST_DM,C_MID_DM_MST);
dbms_output.put_line('CAMS'||C_MID_CD_DM||'-'||C_MID_DM_CD);
end loop;
FOR I IN CUR2
loop
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO D_MID_CD_DM FROM
(SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO D_MID_DM_CD FROM
(SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO D_MID_MST_CD FROM
(SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO D_MID_CD_MST FROM
(SELECT PATH FROM CD WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO D_MID_MST_DM FROM
(SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
SELECT LISTAGG(SUBSTR(PATH,-2),',') WITHIN GROUP(ORDER BY PATH) INTO D_MID_DM_MST FROM
(SELECT PATH FROM DM WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM MINUS
SELECT PATH FROM MST WHERE ROOT =I.ROOT AND SRC_SYSTEM=I.SRC_SYSTEM);
INSERT INTO INTER_TEMP(ROOT,SRC_SYSTEM,MID_CD_DM,MID_DM_CD,MID_MST_CD,MID_CD_MST,MID_MST_DM,MID_DM_MST)
VALUES(I.ROOT,I.SRC_SYSTEM,D_MID_CD_DM,D_MID_DM_CD,D_MID_MST_CD,D_MID_CD_MST,D_MID_MST_DM,D_MID_DM_MST);
dbms_output.put_line('DARWIN'||D_MID_CD_DM||'-'||D_MID_DM_CD);
end loop;
COMMIT;
Exception
when others then
raise_application_error(-20001,'error in code'||SQLCODE||SQLERRM);
END;