Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, ankit.

Asked: July 05, 2016 - 7:03 am UTC

Answered by: Chris Saxon - Last updated: July 07, 2016 - 2:44 pm UTC

Category: Developer - Version: oracle 11g

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Finding the Longest Common Starting Substring Using SQL

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 we 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.

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

How to create on index on columns of different tables.

July 07, 2016 - 5:58 am UTC

Reviewer: ankit mongia from India

Hi Tom,

As you said i took the join to where clause. But now how to create
index on the columns in order by as columns are from two different tables.

select
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,
ut.LOCATION
from ms_inc_list_report join MS_DNATA_ALL_LOCATIONS loc
on ms_inc_list_report.INC_COUNTRY =loc.LOCATION_NAME
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
left outer join
si_users_t ut on ut.location=loc.LOCATION_NAME
and ut.USER_NAME=:11
order by ut.location,
ms_inc_list_report.inc_country,
TO_DATE (ms_inc_list_report.INC_OCCURRENCE_DATE,'DD/MM/YYYY hh24:mi') desc

Connor McDonald

Followup  

July 07, 2016 - 7:52 am UTC

It's Chris actually, but hey :)

The point is that by placing the query in the main join, Oracle Database may be able to find a better plan - without any extra indexes!

But really, we need to see the execution plan to comment further.

July 07, 2016 - 6:17 am UTC

Reviewer: ankit mongia from India

Hi Tom,

I changed the query by putting join in order by to where clause.
But now the problem is how can create index on columns used in order by as their are columns from two different tables.

July 07, 2016 - 7:07 am UTC

Reviewer: ankit mongia from India


Explain plan

July 07, 2016 - 8:59 am UTC

Reviewer: ankit mongia from India


July 07, 2016 - 9:01 am UTC

Reviewer: ankit mongia from India

Explain plan when i am not forcing an index.


Plan hash value: 3902570389

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53068 | 27 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 53068 | 27 (4)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 53068 | 26 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 53057 | 23 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | FILTER | | | | | |
| 6 | NESTED LOOPS | | 1 | 328 | 23 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | MS_INC_LIST_REPORT | 1 | 310 | 22 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | MS_DNATA_ALL_LOCATIONS | 1 | 18 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | LOCATION_NAME_IDX | 1 | | 0 (0)| 00:00:01 |
|* 10 | COLLECTION ITERATOR PICKLER FETCH| SPLIT_STRING | 1 | 2 | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | SI_USERS_T | 1 | 11 | 3 (0)| 00:00:01 |
|* 13 | INDEX SKIP SCAN | SI_USERS_T_N2 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Chris Saxon

Followup  

July 07, 2016 - 2:44 pm UTC

Thanks, but what is the execution plan?

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

We need to see actual row figures!

And how long does the query take to execute? How quickly do you
need
it to execute?