Select query works too long

Question

I have select query which uses subquery to produce results:

SELECT 
  fu.user_name       "User name"
  ,  frt.responsibility_name "Responsibility Name" 
  , furg.start_date     "Start Date" 
  , furg.end_date      "End Date" 
  , fu.last_logon_date    "Last Logon Date"
  , fr.responsibility_key  "Responsibility key"
  , fu.email_address     "Email Address"
  , fu.description      "Description" 
  , m.MANAGER_NAME
  , m.MANAGER_EMAIL   
FROM
  fnd_user_resp_groups_direct   furg
, fnd_user        fu
, applsys.fnd_responsibility_tl  frt
, applsys.fnd_responsibility   fr
---------------- this subquery ------------------------
, (select distinct e.employee_number employee_id, e.full_name employee,e.EMAIL_ADDRESS employee_email, 
  m.employee_number manager_id, m.full_name manager_name,  m.EMAIL_ADDRESS as manager_email 
  from EMPDATA_IMPORT_STG e
      ,EMPDATA_IMPORT_STG m
where m.employee_number=e.supervisor_name
) m
---------------------------------------
WHERE  furg.user_id = fu.user_id
  AND furg.responsibility_id = frt.responsibility_id
  AND fr.responsibility_id = frt.responsibility_id 
  AND (to_char(fu.END_DATE) is null  OR fu.END_DATE > sysdate)
 and fu.email_address=m.EMPLOYEE_EMAIL
 and not (frt.responsibility_name like '%iExpenses%' and frt.responsibility_name not like '%iExpenses Setup and Admin%')
  and frt.responsibility_name not like '%Expenses Auditor%' 
  and frt.responsibility_name not like '%Notifications%'
  and frt.responsibility_name not like '%Inquiry%' and frt.responsibility_name not like '%INQUIRY%'
  and frt.responsibility_name not like '%Self-Service%'  and frt.responsibility_name not like '%Self Service%'
  and frt.responsibility_name not like '%(Read Only)%'  and frt.responsibility_name not like '%Internet Expenses Help Desk%'
  and frt.responsibility_name not like '%Employee Opportunities%'

My issue is that EMPDATA_IMPORT_STG table has over 2 millions records and it has non unique index created on its columns EMPLOYEE_NUMBER, FILE_TIMESTAMP. I have tried to execute this in production and it started execution, and i waited 4 minutes and killed my session. Just to mention in test environment same query executes in 10 sec.

How to speed this query execution in production?

Statistics are collected daily in prod and im thinking to create index on email column as well, would that give me faster response?

Thank you

There are a lot of things which affect the performance of a query. So it is impossible to answer your question just be looking at the SELECT statement and saying, "do this". Please read [this post](https://stackoverflow.com/a/34975420/146325) about asking Oracle tuning questions: it will help you understand the information you need to provide before we can answer your question. — APC, May 24 '21 at 11:52
First of all, why are you not using ANSI join syntax? Second, check the query plan if proper indexes are being used or not then you may modify column indexes accordingly and also check whether the query optimizer is using a hash join or sort merger join or nested loop join. — Aman Singh Rajpoot, May 24 '21 at 12:39
For what it's worth, `column NOT LIKE '%constant%'` is a notorious performance antipattern. It defeats the use of an index on `column`. You have many of those. You may be stuck with bad performance. — O. Jones, May 24 '21 at 13:20
What is the significance of EMPDATA_IMPORT_STG .FILE_TIMESTAMP? Should you be using it to filter the inline view or constrain its join? Otherwise it seems likely you're going to be joining multiple instances of Employees to multiple instances of Supervisors, and that product is what causes the extended execution time. — APC, May 24 '21 at 13:21
Please [edit] your question to show us the table definition, and all indexes, on your `EMPDATA_IMPORT_STG` table. — O. Jones, May 24 '21 at 13:25
**You need to show us the table and index definitions**, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We need row counts because that can affect query planning. If you know how to do an `EXPLAIN` or get an execution plan, put the results in the question as well. If you have no indexes, visit http://use-the-index-luke.com. — Andy Lester, May 24 '21 at 14:48

score 0 · Answer 1 · answered May 24 '21 at 14:47

Because this query is executed on time per month, i have created temporary table which holds those employees by using distinct employee_number from original one:

  create table xx_employees as select DISTINCT employee_number, 
       full_name, 
       supervisor_name,
       EMAIL_ADDRESS 
from EMPDATA_IMPORT_STG ;

This solves my problem, because unfortunately i had very small time window to provide results of this query.

My apologies to all once again.

Thank you

Select query works too long

1 Answers1