Foren
Need performance tuning for this query. very urgent
srikanth jonn, geändert vor 7 Jahren.
Need performance tuning for this query. very urgent
New Member Beiträge: 2 Beitrittsdatum: 14.09.16 Neueste Beiträge
WITH TGT AS
(SELECT TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COLUMN_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COLUMN_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_TABLE_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_TABLE_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_SYSTEM_ENVIRONMENT_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_SYSTEM_ENVIRONMENT_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_SYSTEM_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_SYSTEM_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_DATATYPE,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_DATATYPE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_LENGTH,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_LENGTH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_PRECISION,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_PRECISION,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_SCALE,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_SCALE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_XPATH,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_XPATH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_PRIMARY_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_PRIMARY_KEY_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_NULLABLE_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_NULLABLE_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_NATURAL_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_NATURAL_KEY_FLAG,
MAP_SEQ_ID,
MAP_ID,
PROJ_ID
FROM MAPPING_SPECIFICATION MS
WHERE (MS.MAP_ID IN
(SELECT MAP_ID
FROM MAPPING_DETAILS MD
WHERE MD.STATUS = 'Active'
OR MD.ISPUBLISHED = 'Y' ) AND MS.proj_id IN ( 39554 ))
AND COALESCE(TRIM(MS.TGT_COLUMN_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.TGT_TABLE_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.TGT_SYSTEM_ENVIRONMENT_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.TGT_SYSTEM_NAME),NULL,' ') <> ' ' CONNECT BY NOCYCLE LEVEL <= LENGTH(REPLACE(MS.TGT_COLUMN_NAME,CHR(10),',')) - LENGTH(REPLACE(REPLACE(MS.TGT_COLUMN_NAME,CHR(10),','), ',')) + 1
AND
PRIOR MAP_SEQ_ID = MAP_SEQ_ID
AND
PRIOR DBMS_RANDOM.VALUE IS NOT NULL ),
SRC AS
(SELECT TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COLUMN_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COLUMN_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_TABLE_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_TABLE_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_SYSTEM_ENVIRONMENT_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_SYSTEM_ENVIRONMENT_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_SYSTEM_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_SYSTEM_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_DATATYPE,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_DATATYPE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_LENGTH,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_LENGTH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_PRECISION,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_PRECISION,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_SCALE,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_SCALE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_XPATH,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_XPATH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_PRIMARY_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_PRIMARY_KEY_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_IDENTITY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_IDENTITY_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_NULLABLE_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_NULLABLE_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_NATURAL_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_NATURAL_KEY_FLAG,
MAP_SEQ_ID,
MAP_ID,
PROJ_ID
FROM MAPPING_SPECIFICATION MS WHERE(MS.MAP_ID IN
(SELECT MAP_ID
FROM MAPPING_DETAILS MD
WHERE MD.STATUS = 'Active'
OR MD.ISPUBLISHED = 'Y' ) AND MS.proj_id IN ( 39554 ))
AND COALESCE(TRIM(MS.SRC_COLUMN_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.SRC_TABLE_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.SRC_SYSTEM_ENVIRONMENT_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.SRC_SYSTEM_NAME),NULL,' ') <> ' ' CONNECT BY NOCYCLE LEVEL <= LENGTH(REPLACE(MS.SRC_COLUMN_NAME,CHR(10),',')) - LENGTH(REPLACE(REPLACE(MS.SRC_COLUMN_NAME,CHR(10),','), ',')) + 1
AND
PRIOR MAP_SEQ_ID = MAP_SEQ_ID
AND
PRIOR DBMS_RANDOM.VALUE IS NOT NULL),
SRCANDTGT AS
(SELECT SRC.SRC_SYSTEM_NAME,
SRC.SRC_SYSTEM_ENVIRONMENT_NAME,
SRC.SRC_TABLE_NAME,
SRC.SRC_COLUMN_NAME,
SRC.SRC_COL_DATATYPE,
SRC.SRC_COL_LENGTH,
SRC.SRC_COL_PRECISION,
SRC.SRC_COL_SCALE,
SRC.SRC_XPATH,
SRC.SRC_PRIMARY_KEY_FLAG,
SRC.SRC_COL_IDENTITY_FLAG,
SRC.SRC_COL_NULLABLE_FLAG,
SRC.SRC_NATURAL_KEY_FLAG,
TGT.*
FROM SRC
JOIN TGT ON (SRC.MAP_SEQ_ID = TGT.MAP_SEQ_ID)),
FWD_CTE AS
(SELECT
PRIOR T1.SRC_COLUMN_NAME AS PARENT_SRC,
T1.SRC_COLUMN_NAME SRC_COLUMN_NAME,
T1.SRC_TABLE_NAME SRC_TABLE_NAME,
T1.SRC_System_Environment_Name SRC_System_Environment_Name,
T1.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
T1.SRC_COL_DATATYPE,
T1.SRC_COL_LENGTH,
T1.SRC_COL_PRECISION,
T1.SRC_COL_SCALE,
T1.SRC_XPATH,
T1.SRC_PRIMARY_KEY_FLAG,
T1.SRC_COL_IDENTITY_FLAG,
T1.SRC_COL_NULLABLE_FLAG,
T1.SRC_NATURAL_KEY_FLAG,
T1.TGT_COLUMN_NAME TGT_COLUMN_NAME,
T1.TGT_TABLE_NAME TGT_TABLE_NAME,
T1.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
T1.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
T1.TGT_COL_DATATYPE,
T1.TGT_COL_LENGTH,
T1.TGT_COL_PRECISION,
T1.TGT_COL_SCALE,
T1.TGT_XPATH,
T1.TGT_PRIMARY_KEY_FLAG,
T1.TGT_COL_NULLABLE_FLAG,
T1.TGT_NATURAL_KEY_FLAG,
T1.MAP_ID,
t1.map_seq_id,
t1.proj_id
FROM SRCANDTGT T1
LEFT OUTER JOIN SRCANDTGT T2 ON (UPPER(T2.SRC_COLUMN_NAME) = UPPER(T1.TGT_COLUMN_NAME)
AND UPPER(T2.SRC_TABLE_NAME) = UPPER(T1.TGT_TABLE_NAME)
AND UPPER(T2.SRC_SYSTEM_NAME) = UPPER(T1.TGT_SYSTEM_NAME)
AND UPPER(T2.SRC_SYSTEM_ENVIRONMENT_NAME) = UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME))
WHERE (UPPER(T1.TGT_COLUMN_NAME) <> UPPER(T1.SRC_COLUMN_NAME)
OR UPPER(t1.TGT_SYSTEM_ENVIRONMENT_NAME) <> UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME)
OR UPPER(T1.TGT_SYSTEM_NAME) <> UPPER(T1.SRC_SYSTEM_NAME)
OR UPPER(T1.TGT_TABLE_NAME) <> UPPER(T1.SRC_TABLE_NAME))
START WITH (Trim(Upper(T1.src_system_name)) = Trim(Upper('ABS'))
AND Trim(Upper(T1.src_system_environment_name)) = Trim(Upper('POPULATION PROJECTION'))
AND Trim(Upper(T1.src_table_name)) = Trim(Upper('ABS POPULATION PROJECTIONS'))
AND Trim(Upper(T1.src_column_name)) = Trim(Upper('COLUMNA'))) CONNECT BY NOCYCLE (UPPER(t1.src_column_name) =
PRIOR UPPER(T1.TGT_COLUMN_NAME)
AND UPPER(T1.SRC_TABLE_NAME) =
PRIOR UPPER(T1.TGT_TABLE_NAME)
AND UPPER(T1.SRC_SYSTEM_NAME) =
PRIOR UPPER(T1.TGT_SYSTEM_NAME)
AND UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME) =
PRIOR UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME)) ),
REV_CTE AS
(SELECT
PRIOR T1.TGT_COLUMN_NAME AS PARENT_TGT,
T1.TGT_COLUMN_NAME TGT_COLUMN_NAME,
T1.TGT_TABLE_NAME TGT_TABLE_NAME,
T1.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
T1.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
T1.TGT_COL_DATATYPE,
T1.TGT_COL_LENGTH,
T1.TGT_COL_PRECISION,
T1.TGT_COL_SCALE,
T1.TGT_XPATH,
T1.TGT_PRIMARY_KEY_FLAG,
T1.TGT_COL_NULLABLE_FLAG,
T1.TGT_NATURAL_KEY_FLAG,
T1.SRC_COLUMN_NAME SRC_COLUMN_NAME,
T1.SRC_TABLE_NAME SRC_TABLE_NAME,
T1.SRC_SYSTEM_ENVIRONMENT_NAME SRC_SYSTEM_ENVIRONMENT_NAME,
T1.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
T1.SRC_COL_DATATYPE,
T1.SRC_COL_LENGTH,
T1.SRC_COL_PRECISION,
T1.SRC_COL_SCALE,
T1.SRC_XPATH,
T1.SRC_PRIMARY_KEY_FLAG,
T1.SRC_COL_IDENTITY_FLAG,
T1.SRC_COL_NULLABLE_FLAG,
T1.SRC_NATURAL_KEY_FLAG,
T1.MAP_ID,
T1.MAP_SEQ_ID,
T1.PROJ_ID
FROM SRCANDTGT T1
LEFT OUTER JOIN SRCANDTGT T2 ON UPPER(T2.TGT_COLUMN_NAME) = UPPER(T1.SRC_COLUMN_NAME)
AND UPPER(T2.TGT_TABLE_NAME) = UPPER(T1.SRC_TABLE_NAME)
AND UPPER(T2.TGT_SYSTEM_NAME) = UPPER(T1.SRC_SYSTEM_NAME)
AND UPPER(T2.TGT_SYSTEM_ENVIRONMENT_NAME) = UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME)
WHERE UPPER(T1.SRC_COLUMN_NAME) <> UPPER(T1.TGT_COLUMN_NAME)
OR UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME) <> UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME)
OR UPPER(T1.SRC_SYSTEM_NAME) <> UPPER(T1.TGT_SYSTEM_NAME)
OR UPPER(T1.SRC_TABLE_NAME) <> UPPER(T1.TGT_TABLE_NAME)
START WITH TRIM(UPPER(T1.TGT_SYSTEM_NAME)) = TRIM(UPPER('ABS'))
AND TRIM(UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME)) = TRIM(UPPER('POPULATION PROJECTION'))
AND TRIM(UPPER(T1.TGT_TABLE_NAME)) = TRIM(UPPER('ABS POPULATION PROJECTIONS'))
AND TRIM(UPPER(T1.TGT_COLUMN_NAME)) = TRIM(UPPER('COLUMNA')) CONNECT BY NOCYCLE UPPER(T1.TGT_COLUMN_NAME) =
PRIOR UPPER(T1.SRC_COLUMN_NAME)
AND UPPER(T1.TGT_TABLE_NAME) =
PRIOR UPPER(T1.SRC_TABLE_NAME)
AND UPPER(T1.TGT_SYSTEM_NAME) =
PRIOR UPPER(T1.SRC_SYSTEM_NAME)
AND UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME) =
PRIOR UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME) ),
FWD_CTE2 AS
(SELECT CTE.SRC_COLUMN_NAME SRC_COLUMN_NAME,
CTE.SRC_TABLE_NAME SRC_TABLE_NAME,
CTE.SRC_System_Environment_Name SRC_System_Environment_Name,
CTE.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
CTE.SRC_COL_DATATYPE,
CTE.SRC_COL_LENGTH,
CTE.SRC_COL_PRECISION,
CTE.SRC_COL_SCALE,
CTE.SRC_XPATH,
CTE.SRC_PRIMARY_KEY_FLAG,
CTE.SRC_COL_IDENTITY_FLAG,
CTE.SRC_COL_NULLABLE_FLAG,
CTE.SRC_NATURAL_KEY_FLAG,
CTE.TGT_COLUMN_NAME TGT_COLUMN_NAME,
CTE.TGT_TABLE_NAME TGT_TABLE_NAME,
CTE.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
CTE.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
CTE.TGT_COL_DATATYPE,
CTE.TGT_COL_LENGTH,
CTE.TGT_COL_PRECISION,
CTE.TGT_COL_SCALE,
CTE.TGT_XPATH,
CTE.TGT_PRIMARY_KEY_FLAG,
CTE.TGT_COL_NULLABLE_FLAG,
CTE.TGT_NATURAL_KEY_FLAG,
CTE.MAP_ID,
CTE.map_seq_id,
CTE.proj_id,
MS.SRC_SYSTEM_ID,
MS.SRC_SYSTEM_NAME SRC_SYS_NAME,
MS.SRC_COLUMN_NAME SRC_COL_NAME,
MS.SRC_SYSTEM_ENVIRONMENT_NAME SRC_SYS_ENV_NAME,
MS.SRC_TABLE_NAME SRC_TAB_NAME,
ms.src_col_def,
ms.SRC_COLUMN_COMMENTS,
ms.SRC_LOGICAL_COLUMN_NAME,
ms.SRC_COLUMN_CLASS,
ms.SRC_COLUMN_ALIAS,
ms.SRC_BUSINESS_KEY_FLAG,
MS.TGT_SYSTEM_ID,
MS.TGT_COLUMN_NAME TGT_COL_NAME,
MS.TGT_TABLE_NAME TGT_TAB_NAME,
MS.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYS_ENV_NAME,
MS.TGT_SYSTEM_NAME TGT_SYS_NAME,
ms.tgt_col_def,
ms.TGT_COLUMN_COMMENTS,
ms.TGT_LOGICAL_COLUMN_NAME,
ms.TGT_COLUMN_CLASS,
ms.TGT_COLUMN_ALIAS,
ms.TGT_BUSINESS_KEY_FLAG,
ms.BUSINESS_RULE,
ms.EXTENDED_BUS_RULE,
MS.LOOKUP_ON,
ms.TRNSF_LOOKUP_CONDITION,
ms.USER_DEFINATION1,
ms.USER_DEFINATION2,
ms.USER_DEFINATION3,
ms.USER_DEFINATION4,
ms.USER_DEFINATION5,
ms.USER_DEFINATION6,
ms.USER_DEFINATION7,
ms.USER_DEFINATION8,
ms.USER_DEFINATION9,
ms.USER_DEFINATION10,
ms.USER_DEFINITION11,
ms.USER_DEFINITION12,
ms.USER_DEFINITION13,
ms.USER_DEFINITION14,
ms.USER_DEFINITION15,
ms.USER_DEFINITION16,
ms.USER_DEFINITION17,
ms.USER_DEFINITION18,
ms.USER_DEFINITION19,
ms.USER_DEFINITION20,
ms.USER_DEFINITION21,
ms.USER_DEFINITION22,
ms.USER_DEFINITION23,
ms.USER_DEFINITION24,
ms.USER_DEFINITION25,
ms.USER_DEFINITION26,
ms.USER_DEFINITION27,
ms.USER_DEFINITION28,
ms.USER_DEFINITION29,
ms.USER_DEFINITION30,
ms.USER_DEFINITION31,
ms.USER_DEFINITION32,
ms.USER_DEFINITION33,
ms.USER_DEFINITION34,
ms.USER_DEFINITION35,
ms.USER_DEFINITION36,
ms.USER_DEFINITION37,
ms.USER_DEFINITION38,
ms.USER_DEFINITION39,
ms.USER_DEFINITION40,
ms.USER_DEFINITION41,
ms.USER_DEFINITION42,
ms.USER_DEFINITION43,
ms.USER_DEFINITION44,
ms.USER_DEFINITION45,
ms.USER_DEFINITION46,
ms.USER_DEFINITION47,
ms.USER_DEFINITION48,
ms.USER_DEFINITION49,
ms.USER_DEFINITION50,
md.map_name,
md.map_spec_version,
PROJ.proj_name,
MD.JOB_XREF,
MD.USER_DEFINED_FIELD1 MAP_USER_DEFINED1
FROM FWD_CTE CTE
JOIN MAPPING_SPECIFICATION MS ON (MS.map_seq_id = cte.map_seq_id
AND MS.map_id = cte.map_id
AND ms.proj_id = cte.proj_id)
JOIN MAPPING_DETAILS MD ON CTE.MAP_ID = md.map_id
AND (md.status ='Active'
OR md.ispublished = 'Y')
JOIN PROJECT PROJ ON CTE.PROJ_ID = PROJ.PROJ_ID),
REV_CTE2 AS
(SELECT CTE.SRC_COLUMN_NAME SRC_COLUMN_NAME,
CTE.SRC_TABLE_NAME SRC_TABLE_NAME,
CTE.SRC_System_Environment_Name SRC_System_Environment_Name,
CTE.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
CTE.SRC_COL_DATATYPE,
CTE.SRC_COL_LENGTH,
CTE.SRC_COL_PRECISION,
CTE.SRC_COL_SCALE,
CTE.SRC_XPATH,
CTE.SRC_PRIMARY_KEY_FLAG,
CTE.SRC_COL_IDENTITY_FLAG,
CTE.SRC_COL_NULLABLE_FLAG,
CTE.SRC_NATURAL_KEY_FLAG,
CTE.TGT_COLUMN_NAME TGT_COLUMN_NAME,
CTE.TGT_TABLE_NAME TGT_TABLE_NAME,
CTE.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
CTE.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
CTE.TGT_COL_DATATYPE,
CTE.TGT_COL_LENGTH,
CTE.TGT_COL_PRECISION,
CTE.TGT_COL_SCALE,
CTE.TGT_XPATH,
CTE.TGT_PRIMARY_KEY_FLAG,
CTE.TGT_COL_NULLABLE_FLAG,
CTE.TGT_NATURAL_KEY_FLAG,
CTE.MAP_ID,
CTE.map_seq_id,
CTE.proj_id,
MS.SRC_SYSTEM_ID,
MS.SRC_SYSTEM_NAME SRC_SYS_NAME,
MS.SRC_COLUMN_NAME SRC_COL_NAME,
MS.SRC_SYSTEM_ENVIRONMENT_NAME SRC_SYS_ENV_NAME,
MS.SRC_TABLE_NAME SRC_TAB_NAME,
MS.SRC_COL_DEF,
MS.SRC_COLUMN_COMMENTS,
MS.SRC_LOGICAL_COLUMN_NAME,
ms.SRC_COLUMN_CLASS,
ms.SRC_COLUMN_ALIAS,
ms.SRC_BUSINESS_KEY_FLAG,
MS.TGT_SYSTEM_ID,
MS.TGT_COLUMN_NAME TGT_COL_NAME,
MS.TGT_TABLE_NAME TGT_TAB_NAME,
MS.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYS_ENV_NAME,
MS.TGT_SYSTEM_NAME TGT_SYS_NAME,
MS.TGT_COL_DEF,
MS.TGT_COLUMN_COMMENTS,
MS.TGT_LOGICAL_COLUMN_NAME,
ms.TGT_COLUMN_CLASS,
ms.TGT_COLUMN_ALIAS,
ms.TGT_BUSINESS_KEY_FLAG,
MS.BUSINESS_RULE,
MS.EXTENDED_BUS_RULE,
MS.LOOKUP_ON,
MS.TRNSF_LOOKUP_CONDITION,
MS.USER_DEFINATION1,
MS.USER_DEFINATION2,
MS.USER_DEFINATION3,
MS.USER_DEFINATION4,
MS.USER_DEFINATION5,
MS.USER_DEFINATION6,
MS.USER_DEFINATION7,
MS.USER_DEFINATION8,
MS.USER_DEFINATION9,
MS.USER_DEFINATION10,
MS.USER_DEFINITION11,
MS.USER_DEFINITION12,
MS.USER_DEFINITION13,
MS.USER_DEFINITION14,
MS.USER_DEFINITION15,
MS.USER_DEFINITION16,
MS.USER_DEFINITION17,
MS.USER_DEFINITION18,
MS.USER_DEFINITION19,
MS.USER_DEFINITION20,
MS.USER_DEFINITION21,
MS.USER_DEFINITION22,
MS.USER_DEFINITION23,
MS.USER_DEFINITION24,
MS.USER_DEFINITION25,
MS.USER_DEFINITION26,
MS.USER_DEFINITION27,
MS.USER_DEFINITION28,
MS.USER_DEFINITION29,
MS.USER_DEFINITION30,
MS.USER_DEFINITION31,
MS.USER_DEFINITION32,
MS.USER_DEFINITION33,
MS.USER_DEFINITION34,
MS.USER_DEFINITION35,
MS.USER_DEFINITION36,
MS.USER_DEFINITION37,
MS.USER_DEFINITION38,
MS.USER_DEFINITION39,
MS.USER_DEFINITION40,
MS.USER_DEFINITION41,
MS.USER_DEFINITION42,
MS.USER_DEFINITION43,
MS.USER_DEFINITION44,
MS.USER_DEFINITION45,
MS.USER_DEFINITION46,
MS.USER_DEFINITION47,
MS.USER_DEFINITION48,
MS.USER_DEFINITION49,
MS.USER_DEFINITION50,
MD.MAP_NAME,
MD.MAP_SPEC_VERSION,
PROJ.PROJ_NAME,
MD.JOB_XREF,
MD.USER_DEFINED_FIELD1 MAP_USER_DEFINED1
FROM REV_CTE CTE
JOIN MAPPING_SPECIFICATION MS ON (MS.MAP_SEQ_ID = CTE.MAP_SEQ_ID
AND MS.MAP_ID = CTE.MAP_ID
AND MS.PROJ_ID = CTE.PROJ_ID)
JOIN MAPPING_DETAILS MD ON CTE.MAP_ID = MD.MAP_ID
AND (MD.STATUS ='Active'
OR MD.ISPUBLISHED = 'Y')
JOIN PROJECT PROJ ON CTE.PROJ_ID = PROJ.PROJ_ID)
SELECT DISTINCT t.*
FROM
(SELECT DISTINCT *
FROM FWD_CTE2
UNION ALL SELECT DISTINCT *
FROM REV_CTE2)t
(SELECT TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COLUMN_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COLUMN_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_TABLE_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_TABLE_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_SYSTEM_ENVIRONMENT_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_SYSTEM_ENVIRONMENT_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_SYSTEM_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_SYSTEM_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_DATATYPE,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_DATATYPE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_LENGTH,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_LENGTH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_PRECISION,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_PRECISION,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_SCALE,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_SCALE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_XPATH,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_XPATH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_PRIMARY_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_PRIMARY_KEY_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_COL_NULLABLE_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_COL_NULLABLE_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.TGT_NATURAL_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) TGT_NATURAL_KEY_FLAG,
MAP_SEQ_ID,
MAP_ID,
PROJ_ID
FROM MAPPING_SPECIFICATION MS
WHERE (MS.MAP_ID IN
(SELECT MAP_ID
FROM MAPPING_DETAILS MD
WHERE MD.STATUS = 'Active'
OR MD.ISPUBLISHED = 'Y' ) AND MS.proj_id IN ( 39554 ))
AND COALESCE(TRIM(MS.TGT_COLUMN_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.TGT_TABLE_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.TGT_SYSTEM_ENVIRONMENT_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.TGT_SYSTEM_NAME),NULL,' ') <> ' ' CONNECT BY NOCYCLE LEVEL <= LENGTH(REPLACE(MS.TGT_COLUMN_NAME,CHR(10),',')) - LENGTH(REPLACE(REPLACE(MS.TGT_COLUMN_NAME,CHR(10),','), ',')) + 1
AND
PRIOR MAP_SEQ_ID = MAP_SEQ_ID
AND
PRIOR DBMS_RANDOM.VALUE IS NOT NULL ),
SRC AS
(SELECT TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COLUMN_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COLUMN_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_TABLE_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_TABLE_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_SYSTEM_ENVIRONMENT_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_SYSTEM_ENVIRONMENT_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_SYSTEM_NAME,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_SYSTEM_NAME,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_DATATYPE,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_DATATYPE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_LENGTH,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_LENGTH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_PRECISION,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_PRECISION,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_SCALE,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_SCALE,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_XPATH,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_XPATH,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_PRIMARY_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_PRIMARY_KEY_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_IDENTITY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_IDENTITY_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_COL_NULLABLE_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_COL_NULLABLE_FLAG,
TRIM(REGEXP_SUBSTR(REPLACE(MS.SRC_NATURAL_KEY_FLAG,CHR(10),','), '[^,]+', 1, LEVEL)) SRC_NATURAL_KEY_FLAG,
MAP_SEQ_ID,
MAP_ID,
PROJ_ID
FROM MAPPING_SPECIFICATION MS WHERE(MS.MAP_ID IN
(SELECT MAP_ID
FROM MAPPING_DETAILS MD
WHERE MD.STATUS = 'Active'
OR MD.ISPUBLISHED = 'Y' ) AND MS.proj_id IN ( 39554 ))
AND COALESCE(TRIM(MS.SRC_COLUMN_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.SRC_TABLE_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.SRC_SYSTEM_ENVIRONMENT_NAME),NULL,' ') <> ' '
AND COALESCE(TRIM(MS.SRC_SYSTEM_NAME),NULL,' ') <> ' ' CONNECT BY NOCYCLE LEVEL <= LENGTH(REPLACE(MS.SRC_COLUMN_NAME,CHR(10),',')) - LENGTH(REPLACE(REPLACE(MS.SRC_COLUMN_NAME,CHR(10),','), ',')) + 1
AND
PRIOR MAP_SEQ_ID = MAP_SEQ_ID
AND
PRIOR DBMS_RANDOM.VALUE IS NOT NULL),
SRCANDTGT AS
(SELECT SRC.SRC_SYSTEM_NAME,
SRC.SRC_SYSTEM_ENVIRONMENT_NAME,
SRC.SRC_TABLE_NAME,
SRC.SRC_COLUMN_NAME,
SRC.SRC_COL_DATATYPE,
SRC.SRC_COL_LENGTH,
SRC.SRC_COL_PRECISION,
SRC.SRC_COL_SCALE,
SRC.SRC_XPATH,
SRC.SRC_PRIMARY_KEY_FLAG,
SRC.SRC_COL_IDENTITY_FLAG,
SRC.SRC_COL_NULLABLE_FLAG,
SRC.SRC_NATURAL_KEY_FLAG,
TGT.*
FROM SRC
JOIN TGT ON (SRC.MAP_SEQ_ID = TGT.MAP_SEQ_ID)),
FWD_CTE AS
(SELECT
PRIOR T1.SRC_COLUMN_NAME AS PARENT_SRC,
T1.SRC_COLUMN_NAME SRC_COLUMN_NAME,
T1.SRC_TABLE_NAME SRC_TABLE_NAME,
T1.SRC_System_Environment_Name SRC_System_Environment_Name,
T1.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
T1.SRC_COL_DATATYPE,
T1.SRC_COL_LENGTH,
T1.SRC_COL_PRECISION,
T1.SRC_COL_SCALE,
T1.SRC_XPATH,
T1.SRC_PRIMARY_KEY_FLAG,
T1.SRC_COL_IDENTITY_FLAG,
T1.SRC_COL_NULLABLE_FLAG,
T1.SRC_NATURAL_KEY_FLAG,
T1.TGT_COLUMN_NAME TGT_COLUMN_NAME,
T1.TGT_TABLE_NAME TGT_TABLE_NAME,
T1.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
T1.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
T1.TGT_COL_DATATYPE,
T1.TGT_COL_LENGTH,
T1.TGT_COL_PRECISION,
T1.TGT_COL_SCALE,
T1.TGT_XPATH,
T1.TGT_PRIMARY_KEY_FLAG,
T1.TGT_COL_NULLABLE_FLAG,
T1.TGT_NATURAL_KEY_FLAG,
T1.MAP_ID,
t1.map_seq_id,
t1.proj_id
FROM SRCANDTGT T1
LEFT OUTER JOIN SRCANDTGT T2 ON (UPPER(T2.SRC_COLUMN_NAME) = UPPER(T1.TGT_COLUMN_NAME)
AND UPPER(T2.SRC_TABLE_NAME) = UPPER(T1.TGT_TABLE_NAME)
AND UPPER(T2.SRC_SYSTEM_NAME) = UPPER(T1.TGT_SYSTEM_NAME)
AND UPPER(T2.SRC_SYSTEM_ENVIRONMENT_NAME) = UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME))
WHERE (UPPER(T1.TGT_COLUMN_NAME) <> UPPER(T1.SRC_COLUMN_NAME)
OR UPPER(t1.TGT_SYSTEM_ENVIRONMENT_NAME) <> UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME)
OR UPPER(T1.TGT_SYSTEM_NAME) <> UPPER(T1.SRC_SYSTEM_NAME)
OR UPPER(T1.TGT_TABLE_NAME) <> UPPER(T1.SRC_TABLE_NAME))
START WITH (Trim(Upper(T1.src_system_name)) = Trim(Upper('ABS'))
AND Trim(Upper(T1.src_system_environment_name)) = Trim(Upper('POPULATION PROJECTION'))
AND Trim(Upper(T1.src_table_name)) = Trim(Upper('ABS POPULATION PROJECTIONS'))
AND Trim(Upper(T1.src_column_name)) = Trim(Upper('COLUMNA'))) CONNECT BY NOCYCLE (UPPER(t1.src_column_name) =
PRIOR UPPER(T1.TGT_COLUMN_NAME)
AND UPPER(T1.SRC_TABLE_NAME) =
PRIOR UPPER(T1.TGT_TABLE_NAME)
AND UPPER(T1.SRC_SYSTEM_NAME) =
PRIOR UPPER(T1.TGT_SYSTEM_NAME)
AND UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME) =
PRIOR UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME)) ),
REV_CTE AS
(SELECT
PRIOR T1.TGT_COLUMN_NAME AS PARENT_TGT,
T1.TGT_COLUMN_NAME TGT_COLUMN_NAME,
T1.TGT_TABLE_NAME TGT_TABLE_NAME,
T1.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
T1.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
T1.TGT_COL_DATATYPE,
T1.TGT_COL_LENGTH,
T1.TGT_COL_PRECISION,
T1.TGT_COL_SCALE,
T1.TGT_XPATH,
T1.TGT_PRIMARY_KEY_FLAG,
T1.TGT_COL_NULLABLE_FLAG,
T1.TGT_NATURAL_KEY_FLAG,
T1.SRC_COLUMN_NAME SRC_COLUMN_NAME,
T1.SRC_TABLE_NAME SRC_TABLE_NAME,
T1.SRC_SYSTEM_ENVIRONMENT_NAME SRC_SYSTEM_ENVIRONMENT_NAME,
T1.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
T1.SRC_COL_DATATYPE,
T1.SRC_COL_LENGTH,
T1.SRC_COL_PRECISION,
T1.SRC_COL_SCALE,
T1.SRC_XPATH,
T1.SRC_PRIMARY_KEY_FLAG,
T1.SRC_COL_IDENTITY_FLAG,
T1.SRC_COL_NULLABLE_FLAG,
T1.SRC_NATURAL_KEY_FLAG,
T1.MAP_ID,
T1.MAP_SEQ_ID,
T1.PROJ_ID
FROM SRCANDTGT T1
LEFT OUTER JOIN SRCANDTGT T2 ON UPPER(T2.TGT_COLUMN_NAME) = UPPER(T1.SRC_COLUMN_NAME)
AND UPPER(T2.TGT_TABLE_NAME) = UPPER(T1.SRC_TABLE_NAME)
AND UPPER(T2.TGT_SYSTEM_NAME) = UPPER(T1.SRC_SYSTEM_NAME)
AND UPPER(T2.TGT_SYSTEM_ENVIRONMENT_NAME) = UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME)
WHERE UPPER(T1.SRC_COLUMN_NAME) <> UPPER(T1.TGT_COLUMN_NAME)
OR UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME) <> UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME)
OR UPPER(T1.SRC_SYSTEM_NAME) <> UPPER(T1.TGT_SYSTEM_NAME)
OR UPPER(T1.SRC_TABLE_NAME) <> UPPER(T1.TGT_TABLE_NAME)
START WITH TRIM(UPPER(T1.TGT_SYSTEM_NAME)) = TRIM(UPPER('ABS'))
AND TRIM(UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME)) = TRIM(UPPER('POPULATION PROJECTION'))
AND TRIM(UPPER(T1.TGT_TABLE_NAME)) = TRIM(UPPER('ABS POPULATION PROJECTIONS'))
AND TRIM(UPPER(T1.TGT_COLUMN_NAME)) = TRIM(UPPER('COLUMNA')) CONNECT BY NOCYCLE UPPER(T1.TGT_COLUMN_NAME) =
PRIOR UPPER(T1.SRC_COLUMN_NAME)
AND UPPER(T1.TGT_TABLE_NAME) =
PRIOR UPPER(T1.SRC_TABLE_NAME)
AND UPPER(T1.TGT_SYSTEM_NAME) =
PRIOR UPPER(T1.SRC_SYSTEM_NAME)
AND UPPER(T1.TGT_SYSTEM_ENVIRONMENT_NAME) =
PRIOR UPPER(T1.SRC_SYSTEM_ENVIRONMENT_NAME) ),
FWD_CTE2 AS
(SELECT CTE.SRC_COLUMN_NAME SRC_COLUMN_NAME,
CTE.SRC_TABLE_NAME SRC_TABLE_NAME,
CTE.SRC_System_Environment_Name SRC_System_Environment_Name,
CTE.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
CTE.SRC_COL_DATATYPE,
CTE.SRC_COL_LENGTH,
CTE.SRC_COL_PRECISION,
CTE.SRC_COL_SCALE,
CTE.SRC_XPATH,
CTE.SRC_PRIMARY_KEY_FLAG,
CTE.SRC_COL_IDENTITY_FLAG,
CTE.SRC_COL_NULLABLE_FLAG,
CTE.SRC_NATURAL_KEY_FLAG,
CTE.TGT_COLUMN_NAME TGT_COLUMN_NAME,
CTE.TGT_TABLE_NAME TGT_TABLE_NAME,
CTE.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
CTE.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
CTE.TGT_COL_DATATYPE,
CTE.TGT_COL_LENGTH,
CTE.TGT_COL_PRECISION,
CTE.TGT_COL_SCALE,
CTE.TGT_XPATH,
CTE.TGT_PRIMARY_KEY_FLAG,
CTE.TGT_COL_NULLABLE_FLAG,
CTE.TGT_NATURAL_KEY_FLAG,
CTE.MAP_ID,
CTE.map_seq_id,
CTE.proj_id,
MS.SRC_SYSTEM_ID,
MS.SRC_SYSTEM_NAME SRC_SYS_NAME,
MS.SRC_COLUMN_NAME SRC_COL_NAME,
MS.SRC_SYSTEM_ENVIRONMENT_NAME SRC_SYS_ENV_NAME,
MS.SRC_TABLE_NAME SRC_TAB_NAME,
ms.src_col_def,
ms.SRC_COLUMN_COMMENTS,
ms.SRC_LOGICAL_COLUMN_NAME,
ms.SRC_COLUMN_CLASS,
ms.SRC_COLUMN_ALIAS,
ms.SRC_BUSINESS_KEY_FLAG,
MS.TGT_SYSTEM_ID,
MS.TGT_COLUMN_NAME TGT_COL_NAME,
MS.TGT_TABLE_NAME TGT_TAB_NAME,
MS.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYS_ENV_NAME,
MS.TGT_SYSTEM_NAME TGT_SYS_NAME,
ms.tgt_col_def,
ms.TGT_COLUMN_COMMENTS,
ms.TGT_LOGICAL_COLUMN_NAME,
ms.TGT_COLUMN_CLASS,
ms.TGT_COLUMN_ALIAS,
ms.TGT_BUSINESS_KEY_FLAG,
ms.BUSINESS_RULE,
ms.EXTENDED_BUS_RULE,
MS.LOOKUP_ON,
ms.TRNSF_LOOKUP_CONDITION,
ms.USER_DEFINATION1,
ms.USER_DEFINATION2,
ms.USER_DEFINATION3,
ms.USER_DEFINATION4,
ms.USER_DEFINATION5,
ms.USER_DEFINATION6,
ms.USER_DEFINATION7,
ms.USER_DEFINATION8,
ms.USER_DEFINATION9,
ms.USER_DEFINATION10,
ms.USER_DEFINITION11,
ms.USER_DEFINITION12,
ms.USER_DEFINITION13,
ms.USER_DEFINITION14,
ms.USER_DEFINITION15,
ms.USER_DEFINITION16,
ms.USER_DEFINITION17,
ms.USER_DEFINITION18,
ms.USER_DEFINITION19,
ms.USER_DEFINITION20,
ms.USER_DEFINITION21,
ms.USER_DEFINITION22,
ms.USER_DEFINITION23,
ms.USER_DEFINITION24,
ms.USER_DEFINITION25,
ms.USER_DEFINITION26,
ms.USER_DEFINITION27,
ms.USER_DEFINITION28,
ms.USER_DEFINITION29,
ms.USER_DEFINITION30,
ms.USER_DEFINITION31,
ms.USER_DEFINITION32,
ms.USER_DEFINITION33,
ms.USER_DEFINITION34,
ms.USER_DEFINITION35,
ms.USER_DEFINITION36,
ms.USER_DEFINITION37,
ms.USER_DEFINITION38,
ms.USER_DEFINITION39,
ms.USER_DEFINITION40,
ms.USER_DEFINITION41,
ms.USER_DEFINITION42,
ms.USER_DEFINITION43,
ms.USER_DEFINITION44,
ms.USER_DEFINITION45,
ms.USER_DEFINITION46,
ms.USER_DEFINITION47,
ms.USER_DEFINITION48,
ms.USER_DEFINITION49,
ms.USER_DEFINITION50,
md.map_name,
md.map_spec_version,
PROJ.proj_name,
MD.JOB_XREF,
MD.USER_DEFINED_FIELD1 MAP_USER_DEFINED1
FROM FWD_CTE CTE
JOIN MAPPING_SPECIFICATION MS ON (MS.map_seq_id = cte.map_seq_id
AND MS.map_id = cte.map_id
AND ms.proj_id = cte.proj_id)
JOIN MAPPING_DETAILS MD ON CTE.MAP_ID = md.map_id
AND (md.status ='Active'
OR md.ispublished = 'Y')
JOIN PROJECT PROJ ON CTE.PROJ_ID = PROJ.PROJ_ID),
REV_CTE2 AS
(SELECT CTE.SRC_COLUMN_NAME SRC_COLUMN_NAME,
CTE.SRC_TABLE_NAME SRC_TABLE_NAME,
CTE.SRC_System_Environment_Name SRC_System_Environment_Name,
CTE.SRC_SYSTEM_NAME SRC_SYSTEM_NAME,
CTE.SRC_COL_DATATYPE,
CTE.SRC_COL_LENGTH,
CTE.SRC_COL_PRECISION,
CTE.SRC_COL_SCALE,
CTE.SRC_XPATH,
CTE.SRC_PRIMARY_KEY_FLAG,
CTE.SRC_COL_IDENTITY_FLAG,
CTE.SRC_COL_NULLABLE_FLAG,
CTE.SRC_NATURAL_KEY_FLAG,
CTE.TGT_COLUMN_NAME TGT_COLUMN_NAME,
CTE.TGT_TABLE_NAME TGT_TABLE_NAME,
CTE.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYSTEM_ENVIRONMENT_NAME,
CTE.TGT_SYSTEM_NAME TGT_SYSTEM_NAME,
CTE.TGT_COL_DATATYPE,
CTE.TGT_COL_LENGTH,
CTE.TGT_COL_PRECISION,
CTE.TGT_COL_SCALE,
CTE.TGT_XPATH,
CTE.TGT_PRIMARY_KEY_FLAG,
CTE.TGT_COL_NULLABLE_FLAG,
CTE.TGT_NATURAL_KEY_FLAG,
CTE.MAP_ID,
CTE.map_seq_id,
CTE.proj_id,
MS.SRC_SYSTEM_ID,
MS.SRC_SYSTEM_NAME SRC_SYS_NAME,
MS.SRC_COLUMN_NAME SRC_COL_NAME,
MS.SRC_SYSTEM_ENVIRONMENT_NAME SRC_SYS_ENV_NAME,
MS.SRC_TABLE_NAME SRC_TAB_NAME,
MS.SRC_COL_DEF,
MS.SRC_COLUMN_COMMENTS,
MS.SRC_LOGICAL_COLUMN_NAME,
ms.SRC_COLUMN_CLASS,
ms.SRC_COLUMN_ALIAS,
ms.SRC_BUSINESS_KEY_FLAG,
MS.TGT_SYSTEM_ID,
MS.TGT_COLUMN_NAME TGT_COL_NAME,
MS.TGT_TABLE_NAME TGT_TAB_NAME,
MS.TGT_SYSTEM_ENVIRONMENT_NAME TGT_SYS_ENV_NAME,
MS.TGT_SYSTEM_NAME TGT_SYS_NAME,
MS.TGT_COL_DEF,
MS.TGT_COLUMN_COMMENTS,
MS.TGT_LOGICAL_COLUMN_NAME,
ms.TGT_COLUMN_CLASS,
ms.TGT_COLUMN_ALIAS,
ms.TGT_BUSINESS_KEY_FLAG,
MS.BUSINESS_RULE,
MS.EXTENDED_BUS_RULE,
MS.LOOKUP_ON,
MS.TRNSF_LOOKUP_CONDITION,
MS.USER_DEFINATION1,
MS.USER_DEFINATION2,
MS.USER_DEFINATION3,
MS.USER_DEFINATION4,
MS.USER_DEFINATION5,
MS.USER_DEFINATION6,
MS.USER_DEFINATION7,
MS.USER_DEFINATION8,
MS.USER_DEFINATION9,
MS.USER_DEFINATION10,
MS.USER_DEFINITION11,
MS.USER_DEFINITION12,
MS.USER_DEFINITION13,
MS.USER_DEFINITION14,
MS.USER_DEFINITION15,
MS.USER_DEFINITION16,
MS.USER_DEFINITION17,
MS.USER_DEFINITION18,
MS.USER_DEFINITION19,
MS.USER_DEFINITION20,
MS.USER_DEFINITION21,
MS.USER_DEFINITION22,
MS.USER_DEFINITION23,
MS.USER_DEFINITION24,
MS.USER_DEFINITION25,
MS.USER_DEFINITION26,
MS.USER_DEFINITION27,
MS.USER_DEFINITION28,
MS.USER_DEFINITION29,
MS.USER_DEFINITION30,
MS.USER_DEFINITION31,
MS.USER_DEFINITION32,
MS.USER_DEFINITION33,
MS.USER_DEFINITION34,
MS.USER_DEFINITION35,
MS.USER_DEFINITION36,
MS.USER_DEFINITION37,
MS.USER_DEFINITION38,
MS.USER_DEFINITION39,
MS.USER_DEFINITION40,
MS.USER_DEFINITION41,
MS.USER_DEFINITION42,
MS.USER_DEFINITION43,
MS.USER_DEFINITION44,
MS.USER_DEFINITION45,
MS.USER_DEFINITION46,
MS.USER_DEFINITION47,
MS.USER_DEFINITION48,
MS.USER_DEFINITION49,
MS.USER_DEFINITION50,
MD.MAP_NAME,
MD.MAP_SPEC_VERSION,
PROJ.PROJ_NAME,
MD.JOB_XREF,
MD.USER_DEFINED_FIELD1 MAP_USER_DEFINED1
FROM REV_CTE CTE
JOIN MAPPING_SPECIFICATION MS ON (MS.MAP_SEQ_ID = CTE.MAP_SEQ_ID
AND MS.MAP_ID = CTE.MAP_ID
AND MS.PROJ_ID = CTE.PROJ_ID)
JOIN MAPPING_DETAILS MD ON CTE.MAP_ID = MD.MAP_ID
AND (MD.STATUS ='Active'
OR MD.ISPUBLISHED = 'Y')
JOIN PROJECT PROJ ON CTE.PROJ_ID = PROJ.PROJ_ID)
SELECT DISTINCT t.*
FROM
(SELECT DISTINCT *
FROM FWD_CTE2
UNION ALL SELECT DISTINCT *
FROM REV_CTE2)t
srikanth jonn, geändert vor 7 Jahren.
RE: Need performance tuning for this query. very urgent
New Member Beiträge: 2 Beitrittsdatum: 14.09.16 Neueste Beiträge
Plan hash value: 2307097070
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 251K| | 1559K (1)| 05:11:55 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6721_3CF816B9 | | | | | |
| 3 | VIEW | | 49778 | 1712M| | 86068 (1)| 00:17:13 |
|* 4 | HASH JOIN | | 49778 | 1712M| 856M| 86068 (1)| 00:17:13 |
| 5 | VIEW | | 49778 | 855M| | 552 (2)| 00:00:07 |
|* 6 | FILTER | | | | | | |
|* 7 | CONNECT BY WITHOUT FILTERING | | | | | | |
| 8 | TABLE ACCESS FULL | MAPPING_SPECIFICATION | 49778 | 3645K| | 552 (2)| 00:00:07 |
|* 9 | TABLE ACCESS BY INDEX ROWID | MAPPING_DETAILS | 1 | 15 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | XPKMAPPING_DETAILS | 1 | | | 1 (0)| 00:00:01 |
| 11 | VIEW | | 49778 | 856M| | 551 (2)| 00:00:07 |
|* 12 | FILTER | | | | | | |
|* 13 | CONNECT BY WITHOUT FILTERING | | | | | | |
| 14 | TABLE ACCESS FULL | MAPPING_SPECIFICATION | 49778 | 4520K| | 551 (2)| 00:00:07 |
|* 15 | TABLE ACCESS BY INDEX ROWID | MAPPING_DETAILS | 1 | 15 | | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | XPKMAPPING_DETAILS | 1 | | | 1 (0)| 00:00:01 |
| 17 | HASH UNIQUE | | 2 | 251K| | 1473K (1)| 04:54:42 |
| 18 | VIEW | | 2 | 251K| | 1473K (1)| 04:54:42 |
| 19 | UNION-ALL | | | | | | |
| 20 | HASH UNIQUE | | 1 | 36440 | | 736K (1)| 02:27:21 |
| 21 | NESTED LOOPS | | 1 | 36440 | | 736K (1)| 02:27:21 |
|* 22 | HASH JOIN | | 28664 | 987M| | 708K (1)| 02:21:37 |
| 23 | TABLE ACCESS FULL | PROJECT | 12 | 180 | | 2 (0)| 00:00:01 |
|* 24 | HASH JOIN | | 31052 | 1069M| | 708K (1)| 02:21:37 |
| 25 | JOIN FILTER CREATE | :BF0000 | 1265 | 78430 | | 27 (0)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | MAPPING_DETAILS | 1265 | 78430 | | 27 (0)| 00:00:01 |
| 27 | VIEW | | 49803 | 1712M| | 1164K (40)| 03:52:56 |
|* 28 | FILTER | | | | | | |
| 29 | JOIN FILTER USE | :BF0000 | | | | | |
|* 30 | CONNECT BY WITH FILTERING| | | | | | |
|* 31 | HASH JOIN RIGHT OUTER | | 49778 | 2093M| 380M| 236K (1)| 00:47:13 |
| 32 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 34 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 36 | HASH JOIN OUTER | | 25 | 1272K| | 472K (1)| 01:34:25 |
|* 37 | HASH JOIN | | 25 | 1076K| 380M| 405K (1)| 01:21:12 |
| 38 | CONNECT BY PUMP | | | | | | |
| 39 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 41 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 42 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 43 | TABLE ACCESS BY INDEX ROWID | MAPPING_SPECIFICATION | 1 | 298 | | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | XPKMAPPING_SPECIFICATION | 1 | | | 0 (0)| 00:00:01 |
| 45 | HASH UNIQUE | | 1 | 36440 | | 736K (1)| 02:27:21 |
| 46 | NESTED LOOPS | | 1 | 36440 | | 736K (1)| 02:27:21 |
|* 47 | HASH JOIN | | 28664 | 987M| | 708K (1)| 02:21:37 |
| 48 | TABLE ACCESS FULL | PROJECT | 12 | 180 | | 2 (0)| 00:00:01 |
|* 49 | HASH JOIN | | 31052 | 1069M| | 708K (1)| 02:21:37 |
| 50 | JOIN FILTER CREATE | :BF0001 | 1265 | 78430 | | 27 (0)| 00:00:01 |
|* 51 | TABLE ACCESS FULL | MAPPING_DETAILS | 1265 | 78430 | | 27 (0)| 00:00:01 |
| 52 | VIEW | | 49803 | 1712M| | 1164K (40)| 03:52:56 |
|* 53 | FILTER | | | | | | |
| 54 | JOIN FILTER USE | :BF0001 | | | | | |
|* 55 | CONNECT BY WITH FILTERING| | | | | | |
|* 56 | HASH JOIN RIGHT OUTER | | 49778 | 2093M| 380M| 236K (1)| 00:47:13 |
| 57 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 58 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 59 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 60 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 61 | HASH JOIN OUTER | | 25 | 1272K| | 472K (1)| 01:34:25 |
|* 62 | HASH JOIN | | 25 | 1076K| 380M| 405K (1)| 01:21:12 |
| 63 | CONNECT BY PUMP | | | | | | |
| 64 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 65 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 66 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 67 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 68 | TABLE ACCESS BY INDEX ROWID | MAPPING_SPECIFICATION | 1 | 298 | | 1 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | XPKMAPPING_SPECIFICATION | 1 | | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 251K| | 1559K (1)| 05:11:55 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6721_3CF816B9 | | | | | |
| 3 | VIEW | | 49778 | 1712M| | 86068 (1)| 00:17:13 |
|* 4 | HASH JOIN | | 49778 | 1712M| 856M| 86068 (1)| 00:17:13 |
| 5 | VIEW | | 49778 | 855M| | 552 (2)| 00:00:07 |
|* 6 | FILTER | | | | | | |
|* 7 | CONNECT BY WITHOUT FILTERING | | | | | | |
| 8 | TABLE ACCESS FULL | MAPPING_SPECIFICATION | 49778 | 3645K| | 552 (2)| 00:00:07 |
|* 9 | TABLE ACCESS BY INDEX ROWID | MAPPING_DETAILS | 1 | 15 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | XPKMAPPING_DETAILS | 1 | | | 1 (0)| 00:00:01 |
| 11 | VIEW | | 49778 | 856M| | 551 (2)| 00:00:07 |
|* 12 | FILTER | | | | | | |
|* 13 | CONNECT BY WITHOUT FILTERING | | | | | | |
| 14 | TABLE ACCESS FULL | MAPPING_SPECIFICATION | 49778 | 4520K| | 551 (2)| 00:00:07 |
|* 15 | TABLE ACCESS BY INDEX ROWID | MAPPING_DETAILS | 1 | 15 | | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | XPKMAPPING_DETAILS | 1 | | | 1 (0)| 00:00:01 |
| 17 | HASH UNIQUE | | 2 | 251K| | 1473K (1)| 04:54:42 |
| 18 | VIEW | | 2 | 251K| | 1473K (1)| 04:54:42 |
| 19 | UNION-ALL | | | | | | |
| 20 | HASH UNIQUE | | 1 | 36440 | | 736K (1)| 02:27:21 |
| 21 | NESTED LOOPS | | 1 | 36440 | | 736K (1)| 02:27:21 |
|* 22 | HASH JOIN | | 28664 | 987M| | 708K (1)| 02:21:37 |
| 23 | TABLE ACCESS FULL | PROJECT | 12 | 180 | | 2 (0)| 00:00:01 |
|* 24 | HASH JOIN | | 31052 | 1069M| | 708K (1)| 02:21:37 |
| 25 | JOIN FILTER CREATE | :BF0000 | 1265 | 78430 | | 27 (0)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | MAPPING_DETAILS | 1265 | 78430 | | 27 (0)| 00:00:01 |
| 27 | VIEW | | 49803 | 1712M| | 1164K (40)| 03:52:56 |
|* 28 | FILTER | | | | | | |
| 29 | JOIN FILTER USE | :BF0000 | | | | | |
|* 30 | CONNECT BY WITH FILTERING| | | | | | |
|* 31 | HASH JOIN RIGHT OUTER | | 49778 | 2093M| 380M| 236K (1)| 00:47:13 |
| 32 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 34 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 36 | HASH JOIN OUTER | | 25 | 1272K| | 472K (1)| 01:34:25 |
|* 37 | HASH JOIN | | 25 | 1076K| 380M| 405K (1)| 01:21:12 |
| 38 | CONNECT BY PUMP | | | | | | |
| 39 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 41 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 42 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 43 | TABLE ACCESS BY INDEX ROWID | MAPPING_SPECIFICATION | 1 | 298 | | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | XPKMAPPING_SPECIFICATION | 1 | | | 0 (0)| 00:00:01 |
| 45 | HASH UNIQUE | | 1 | 36440 | | 736K (1)| 02:27:21 |
| 46 | NESTED LOOPS | | 1 | 36440 | | 736K (1)| 02:27:21 |
|* 47 | HASH JOIN | | 28664 | 987M| | 708K (1)| 02:21:37 |
| 48 | TABLE ACCESS FULL | PROJECT | 12 | 180 | | 2 (0)| 00:00:01 |
|* 49 | HASH JOIN | | 31052 | 1069M| | 708K (1)| 02:21:37 |
| 50 | JOIN FILTER CREATE | :BF0001 | 1265 | 78430 | | 27 (0)| 00:00:01 |
|* 51 | TABLE ACCESS FULL | MAPPING_DETAILS | 1265 | 78430 | | 27 (0)| 00:00:01 |
| 52 | VIEW | | 49803 | 1712M| | 1164K (40)| 03:52:56 |
|* 53 | FILTER | | | | | | |
| 54 | JOIN FILTER USE | :BF0001 | | | | | |
|* 55 | CONNECT BY WITH FILTERING| | | | | | |
|* 56 | HASH JOIN RIGHT OUTER | | 49778 | 2093M| 380M| 236K (1)| 00:47:13 |
| 57 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 58 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 59 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 60 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
|* 61 | HASH JOIN OUTER | | 25 | 1272K| | 472K (1)| 01:34:25 |
|* 62 | HASH JOIN | | 25 | 1076K| 380M| 405K (1)| 01:21:12 |
| 63 | CONNECT BY PUMP | | | | | | |
| 64 | VIEW | | 49778 | 1713M| | 66072 (1)| 00:13:13 |
| 65 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 66 | VIEW | | 49778 | 380M| | 66072 (1)| 00:13:13 |
| 67 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6721_3CF816B9 | 49778 | 1712M| | 66072 (1)| 00:13:13 |
| 68 | TABLE ACCESS BY INDEX ROWID | MAPPING_SPECIFICATION | 1 | 298 | | 1 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | XPKMAPPING_SPECIFICATION | 1 | | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
Olaf Kock, geändert vor 7 Jahren.
RE: Need performance tuning for this query. very urgent
Liferay Legend Beiträge: 6403 Beitrittsdatum: 23.09.08 Neueste Beiträgesrikanth jonn:
Plan hash value: 2307097070
Are you sure you wanted to post this on the Liferay forums? On my Liferay installation it has no performance impact - I've tried adding both your posts as Web Content and uploaded them as a document, and there's no decreasing performance. ;)
In case you post somewhere else - e.g. in a database forum: Choose one for the undisclosed database platform of your choice and - especially as it's urgent - give as much verbal information as you can give. It might be urgent for you, but be aware that it's not urgent for anyone else. Free help is typically given for a clear and concise problem statement, not for a pasted dump of machine output. If you just expect others to pick up on your urgency and don't want to give more information, prepare to pay for such services.