You Asked
Hi Tom,
In the below query order by is taking a lot of time. so i thought of creating a composite index on columns that are present in the
order by clause and force that index using hint.
But my problem is, here i have to fetch data of logged in user country first and then rest of the data.
To achieve above requirement i have added the following condition in the order by clause.
"CASE WHEN INC_COUNTRY=(SELECT loc.LOCATION_NAME FROM SI_USERS_T s,MS_DNATA_ALL_LOCATIONS loc WHERE USER_NAME=:11 and loc.LOCATION_NAME=s.LOCATION) THEN 1 ELSE 2 END"
This basically checks if country in the fetched row is equal to logged in user country it will give 1 to that row.
Due to this condition index created by me on rest of columns used in order by clause is useless.
Can you help me with an idea what can i do here to improve performance.
CON_DATE_COMP_IND ==>> composite index on INC_COUNTRY ,TO_DATE (INC_OCCURRENCE_DATE,'DD/MM/YYYY hh24:mi') desc
select /*+ index(ms_inc_list_report CON_DATE_COMP_IND) */
INC_REF_NUM,
INC_OCCURRENCE_DATE,
loc.LOCATION_FULL_NAME AS INC_Cnty,
INC_BU,
INC_LOC,
INC_TYPE,
MS_INC_CONSTRUCT_CHLD_URL(INC_INJ_DMG_TYPE,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10) AS INC_INJ_DMG_TYPE,
SUB_LOCATION as INC_EQP_TYPE,
INC_ACTIVITY_WAS,
INC_ACT_INJ_DMG,
INC_RPT_DATE AS INC_RPT_DATE,
INC_PER_ACTIVITY,
INC_OWNER,
INC_STATUS,
INC_CLASSIFICATION,
INC_CLASSIFIED_BY,
INC_INV_LEVEL,
INC_INVESTIGATOR,
INC_NO_COR_ACTION,
INC_NO_COR_OPEN,
INC_PPL_COR_ACTIONS,
INC_EXP_COR_ACTIONS,
INC_AT_RSK_BEHAVIOUR,
DECODE(INC_PRELIMINARY_REPORT,NULL,NULL,'<a target="_self" href="javascript:getDocument('''||INC_PRELIMINARY_REPORT|| ''',8)"><img border="0" src="/images/icons/arrow-stop-270.png"></a>') as INC_PRELIMINARY_REPORT,
DECODE(INC_FINAL_REPORT,NULL,NULL,'<a target="_self" href="javascript:getDocument('''||INC_FINAL_REPORT|| ''',8)"><img border="0" src="/images/icons/arrow-stop-270.png"></a>') as INC_FINAL_REPORT,
MS_INC_CONSTRUCT_CHLD_URL(INC_PER_ACTIVITY,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,i_report_name=>'MS_INC_CHLD_LIST_RPT') as dummy1,
INJURY_ID as dummy2,
MS_INC_CONSTRUCT_CHLD_URL(INC_AT_RSK_BEHAVIOUR,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,i_report_name=>'MS_INC_AT_RSK_BEHAVIOR_RPT') as dummy3,
MS_INC_CONSTRUCT_CHLD_URL(SIF_PRECURSORS,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,i_report_name=>'MS_INC_SIF_PRECURSORS_RPT') as dummy4,
null as dummy5
from ms_inc_list_report, MS_DNATA_ALL_LOCATIONS loc
WHERE
loc.LOCATION_NAME = ms_inc_list_report.INC_COUNTRY and
(:1 is null or (INC_COUNTRY in (:1)))
AND (:2 IS NULL OR (INC_BU in (:2)))
and (:3 is null or (INC_LOC in (:3)))
and (:4 is null or (INC_TYPE in (:4)))
AND (:5 is null or (INC_INJ_DMG_TYPE in (:5)))
AND (:6 IS NULL OR instr(','||INC_CLASSIFICATION||',',','||:6||',',1,1) > 0 or INC_CLASSIFICATION in (SELECT COLUMN_VALUE
FROM TABLE (
SELECT ms_apps_utilities.
split_string (:6,
',')
FROM DUAL))
)
AND (:7 is null or (INC_STATUS in (:7)))
AND (:8 IS NULL OR
(INC_INV_LEVEL in (:8)))
and (TO_DATE (INC_OCCURRENCE_DATE, 'DD/MM/YYYY hh24:mi') BETWEEN NVL (TO_DATE (:9,'DD/MM/RRRR hh24:mi'),TO_DATE (INC_OCCURRENCE_DATE,'DD/MM/YYYY hh24:mi'))
AND NVL (TO_DATE (:10,'DD/MM/RRRR hh24:mi'),TO_DATE (INC_OCCURRENCE_DATE,'DD/MM/YYYY hh24:mi')))
AND (:8 IS NULL OR
(INC_INV_LEVEL in (:8)))
AND CHECK_LOCAL_SAFETY_TEAM_MEMBER(:11) > 0
order by CASE WHEN INC_COUNTRY=(SELECT loc.LOCATION_NAME FROM SI_USERS_T s,MS_DNATA_ALL_LOCATIONS loc WHERE USER_NAME=:11 and loc.LOCATION_NAME=s.LOCATION) THEN 1 ELSE 2 END , INC_COUNTRY ,
TO_DATE (INC_OCCURRENCE_DATE,'DD/MM/YYYY hh24:mi') desc;
and Chris said...
Surely you can take the join out of the order by and place it in the main query?
I suspect this will need to be an outer join (assuming ms_dnata_all_locations can return locations other than the current user's).
Then your order by could be:
loc.location_name , inc_country , to_date ( inc_occurrence_date,'DD/MM/YYYY hh24:mi' ) desc;
For us to help you further, we'll need to see an execution plan for your query! Details of the tables and the indexes available will also help.
Rating
(5 ratings)
Is this answer out of date? If it is, please let us know via a Comment