rank function taking too long
407048Dec 4 2006 — edited Dec 4 2006I am running the below query. This query runs fine. However, if I uncomment the
"rank() over(partition by CONCAT_DATE,VARIABLE_ID order by VARIABLE_VALUE) RANK" and
"B.rank=1" , the query takes a very long time to execute...takes about 6-7 minutes
instead of 20 seconds(when the rank part is commented out). Is there any other way to speed
this up as I needed the one with the lowest rank.
Thanks
SELECT
EXAMCODE,
STARTDATE,
REVDATE,
ENDDATE,
VARIATION ,
GROUPNAME,
PLAN,
STDPLAN,
CORPPLAN,
PRODUCT,
PES,
CONCAT_DATE,
NOTE_ID,
MAJ_HDG_ID,
MAJ_HDG_TXT,
MIN_HDG_ID,
MIN_HDG_TXT,
VARIABLE_ID,
VARIABLE_DESC,
PROVIDERCODE,
VARFORMAT,
NOTE_NAME,
MAJHEADNGNOTE,
MINHEADNGNOTE,
VARIABLENOTE,
VARIABLE_VALUE
FROM(
SELECT
EXAMCODE,
STARTDATE,
REVDATE,
ENDDATE,
VARIATION ,
GROUPNAME,
PLAN,
STDPLAN,
CORPPLAN,
PRODUCT,
PES,
CONCAT_DATE,
NOTE_ID,
MAJ_HDG_ID,
MAJ_HDG_TXT,
MIN_HDG_ID,
MIN_HDG_TXT,
VARIABLE_ID,
VARIABLE_DESC,
PROVIDERCODE,
VARFORMAT,
NOTE_NAME,
MAJHEADNGNOTE,
MINHEADNGNOTE,
VARIABLENOTE,
VARIABLE_VALUE --,
-- rank() over(partition by CONCAT_DATE,VARIABLE_ID order by VARIABLE_VALUE) RANK
FROM
(
SELECT
EXAM_DIM2.EXAM_CODE EXAMCODE,
to_char(START_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') STARTDATE,
to_char(REV_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') REVDATE,
to_char(END_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') ENDDATE,
VARIATION_DIM2.VARIATION_ID VARIATION ,
EXAM_DIM2.GROUP_NAME GROUPNAME,
EXAM_DIM2.BENEFIT_PLAN_NAME PLAN,
EXAM_DIM2.STANDARD_PLAN_NAME STDPLAN,
EXAM_DIM2.CORPORATE_PLAN_NAME CORPPLAN,
EXAM_DIM2.PRODUCT_NAME PRODUCT,
STRUCTURE_DIM2.STRUCTURE_NAME PES,
EXAM_DIM2.EXAM_CODE || ' - ' || to_char(START_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') || ' - ' || nvl(to_char(REV_DATE_DIM2.FULL_DATE,'MM/DD/YYYY'),'N/A')|| ' - ' || to_char(END_DATE_DIM2.FULL_DATE ,'MM/DD/YYYY') CONCAT_DATE,
NOTES_DIM2.NOTE_ID NOTE_ID,
DECODE (MAJOR_HEADING_DIM2.HEADING_ID ,null,HEADING_DIM2.HEADING_ID,MAJOR_HEADING_DIM2.HEADING_ID) MAJ_HDG_ID ,
DECODE (MAJOR_HEADING_DIM2.HEADING_ID ,null,HEADING_DIM2.HEADING_TEXT,MAJOR_HEADING_DIM2.HEADING_TEXT) MAJ_HDG_TXT ,
DECODE(HEADING_DIM2.PARENT_HEADING_ID,null,'',HEADING_DIM2.HEADING_ID) MIN_HDG_ID,
DECODE(HEADING_DIM2.PARENT_HEADING_ID,null,'',HEADING_DIM2.HEADING_TEXT) MIN_HDG_TXT,
VARIABLE_DIM2.VARIABLE_ID VARIABLE_ID,
VARIABLE_DIM2.VARIABLE_SHORT_DESCRIPTION VARIABLE_DESC,
VARIABLE_DIM2.PROVIDER_ARRANGEMENT_CODE PROVIDERCODE,
VARIABLE_DIM2.VARIABLE_FORMAT_CODE VARFORMAT,
NOTES_DIM2.NOTE_NAME NOTE_NAME,
'' as MAJHEADNGNOTE,
'' as MINHEADNGNOTE,
DBMS_LOB.SUBSTR(NOTES_DIM2.NOTE_TEXT,DBMS_LOB.GETLENGTH(NOTES_DIM2.NOTE_TEXT) ,1) VARIABLENOTE,
EXAM_INFO_FACT2.VARIABLE_VALUE VARIABLE_VALUE
FROM
MED_DM.DATE_DIM START_DATE_DIM2,
MED_DM.DATE_DIM END_DATE_DIM2,
MED_DM.DATE_DIM REV_DATE_DIM2,
MED_DM.EXAM_DIM EXAM_DIM2,
MED_DM.STRUCTURE_DIM STRUCTURE_DIM2,
MED_DM.NOTES_FACT NOTES_FACT2,
MED_DM.HEADING_DIM MAJOR_HEADING_DIM2,
MED_DM.HEADING_DIM HEADING_DIM2,
MED_DM.VARIABLE_DIM VARIABLE_DIM2,
MED_DM.NOTES_DIM NOTES_DIM2,
MED_DM.EXAM_INFO_FACT EXAM_INFO_FACT2,
MED_DM.VARIATION_DIM VARIATION_DIM2
WHERE
( EXAM_INFO_FACT2.EXAM_DIM_ID = EXAM_DIM2.EXAM_DIM_ID )
AND ( VARIATION_DIM2.VARIATION_DIM_ID (+)= EXAM_INFO_FACT2.VARIATION_DIM_ID )
AND ( EXAM_INFO_FACT2.STRUCTURE_DIM_ID = STRUCTURE_DIM2.STRUCTURE_DIM_ID)
AND ( EXAM_DIM2.END_DATE_DIM_ID=END_DATE_DIM2.DATE_DIM_ID )
AND ( EXAM_DIM2.REVISION_DATE_DIM_ID=REV_DATE_DIM2.DATE_DIM_ID )
AND ( EXAM_DIM2.START_DATE_DIM_ID=START_DATE_DIM2.DATE_DIM_ID )
AND ( HEADING_DIM2.HEADING_DIM_ID= EXAM_INFO_FACT2.HEADING_DIM_ID )
AND ( MAJOR_HEADING_DIM2.HEADING_ID(+)=HEADING_DIM2.PARENT_HEADING_ID )
AND ( EXAM_INFO_FACT2.VARIABLE_DIM_ID = VARIABLE_DIM2.VARIABLE_DIM_ID )
AND ( EXAM_INFO_FACT2.EXAM_DIM_ID = NOTES_FACT2.EXAM_DIM_ID (+) )
AND ( EXAM_INFO_FACT2.VARIABLE_DIM_ID = NOTES_FACT2.VARIABLE_DIM_ID (+))
AND ( NOTES_FACT2.NOTE_DIM_ID = NOTES_DIM2.NOTE_DIM_ID (+))
UNION ALL
SELECT
EXAM_DIM2.EXAM_CODE EXAMCODE,
to_char(START_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') STARTDATE,
to_char(REV_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') REVDATE,
to_char(END_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') ENDDATE,
'' as VARIATION,
EXAM_DIM2.GROUP_NAME GROUPNAME,
EXAM_DIM2.BENEFIT_PLAN_NAME PLAN,
EXAM_DIM2.STANDARD_PLAN_NAME ,
EXAM_DIM2.CORPORATE_PLAN_NAME CORPPLAN,
EXAM_DIM2.PRODUCT_NAME PRODUCT,
'' as PES,
EXAM_DIM2.EXAM_CODE || ' - ' || to_char(START_DATE_DIM2.FULL_DATE,'MM/DD/YYYY') || ' - ' || nvl(to_char(REV_DATE_DIM2.FULL_DATE,'MM/DD/YYYY'),'N/A')|| ' - ' || to_char(END_DATE_DIM2.FULL_DATE ,'MM/DD/YYYY') CONCAT_DATE,
MED_DM.NOTES_DIM.NOTE_ID NOTE_ID,
DECODE (MAJOR_HEADING_DIM2.HEADING_ID ,null,HEADING_DIM2.HEADING_ID,MAJOR_HEADING_DIM2.HEADING_ID) MAJ_HDG_ID ,
DECODE (MAJOR_HEADING_DIM2.HEADING_ID ,null,HEADING_DIM2.HEADING_TEXT,MAJOR_HEADING_DIM2.HEADING_TEXT) MAJ_HDG_TXT ,
DECODE(HEADING_DIM2.PARENT_HEADING_ID,null,'',HEADING_DIM2.HEADING_ID) MIN_HDG_ID,
DECODE(HEADING_DIM2.PARENT_HEADING_ID,null,'',HEADING_DIM2.HEADING_TEXT) MIN_HDG_TXT,
VARIABLE_DIM2.VARIABLE_ID VARIABLE_ID,
VARIABLE_DIM2.VARIABLE_SHORT_DESCRIPTION VARIABLE_DESC,
VARIABLE_DIM2.PROVIDER_ARRANGEMENT_CODE PROVIDERCODE,
VARIABLE_DIM2.VARIABLE_FORMAT_CODE VARFORMAT,
MED_DM.NOTES_DIM.NOTE_NAME NOTE_NAME,
(CASE WHEN ((HEADING_DIM2.PARENT_HEADING_ID is null) AND (NOTES_FACT.VARIABLE_DIM_ID is null))
THEN DBMS_LOB.SUBSTR(MED_DM.NOTES_DIM.NOTE_TEXT,DBMS_LOB.GETLENGTH(MED_DM.NOTES_DIM.NOTE_TEXT),1)
ELSE
''
END) as MAJHEADNGNOTE,
-- DECODE(HEADING_DIM2.PARENT_HEADING_ID,null,DBMS_LOB.SUBSTR(MED_DM.NOTES_DIM.NOTE_TEXT,DBMS_LOB.GETLENGTH(MED_DM.NOTES_DIM.NOTE_TEXT),1),'') MAJHEADNGNOTE,
-- DECODE(HEADING_DIM2.PARENT_HEADING_ID,null,'',DBMS_LOB.SUBSTR(MED_DM.NOTES_DIM.NOTE_TEXT,DBMS_LOB.GETLENGTH(MED_DM.NOTES_DIM.NOTE_TEXT),1)) MINHEADNGNOTE,
(CASE WHEN ((HEADING_DIM2.PARENT_HEADING_ID is not null) AND (NOTES_FACT.VARIABLE_DIM_ID is null))
THEN DBMS_LOB.SUBSTR(MED_DM.NOTES_DIM.NOTE_TEXT,DBMS_LOB.GETLENGTH(MED_DM.NOTES_DIM.NOTE_TEXT),1)
ELSE
''
END) as MINHEADNGNOTE,
(CASE WHEN (NOTES_FACT.VARIABLE_DIM_ID is not null)
THEN DBMS_LOB.SUBSTR(MED_DM.NOTES_DIM.NOTE_TEXT,DBMS_LOB.GETLENGTH(MED_DM.NOTES_DIM.NOTE_TEXT),1)
ELSE
''
END) as VARIABLENOTE,
--DECODE(NOTES_FACT.VARIABLE_DIM_ID,null,DBMS_LOB.SUBSTR(MED_DM.NOTES_DIM.NOTE_TEXT,DBMS_LOB.GETLENGTH(MED_DM.NOTES_DIM.NOTE_TEXT),1),'') VARIABLENOTE,
'' as VARIABLE_VALUE
FROM
MED_DM.DATE_DIM START_DATE_DIM2,
MED_DM.DATE_DIM END_DATE_DIM2,
MED_DM.DATE_DIM REV_DATE_DIM2,
MED_DM.EXAM_DIM EXAM_DIM2,
MED_DM.NOTES_FACT,
MED_DM.HEADING_DIM MAJOR_HEADING_DIM2,
MED_DM.HEADING_DIM HEADING_DIM2,
MED_DM.VARIABLE_DIM VARIABLE_DIM2,
MED_DM.NOTES_DIM
WHERE
( MED_DM.NOTES_DIM.NOTE_DIM_ID=MED_DM.NOTES_FACT.NOTE_DIM_ID )
AND ( MED_DM.NOTES_FACT.VARIABLE_DIM_ID=VARIABLE_DIM2.VARIABLE_DIM_ID (+) )
AND ( MED_DM.NOTES_FACT.EXAM_DIM_ID=EXAM_DIM2.EXAM_DIM_ID )
AND ( HEADING_DIM2.HEADING_DIM_ID=MED_DM.NOTES_FACT.HEADING_DIM_ID )
AND ( EXAM_DIM2.END_DATE_DIM_ID=END_DATE_DIM2.DATE_DIM_ID )
AND ( EXAM_DIM2.REVISION_DATE_DIM_ID=REV_DATE_DIM2.DATE_DIM_ID )
AND ( EXAM_DIM2.START_DATE_DIM_ID=START_DATE_DIM2.DATE_DIM_ID )
AND ( MAJOR_HEADING_DIM2.HEADING_ID(+)=HEADING_DIM2.PARENT_HEADING_ID )
AND ( MED_DM.NOTES_FACT.HEADING_DIM_ID is not null)
)
)B
WHERE B.EXAMCODE ='G971' and B.STARTDATE = '10/01/2002'
--and B.RANK =1