[Solved] Oracle query runs very slow when used sub-query. Can this be rectified?

EverSQL Database Performance Knowledge Base

Oracle query runs very slow when used sub-query. Can this be rectified?

Database type:

I have the following query to be executed for my project:

SELECT fcr.request_id,
             DECODE
                (fcpt.user_concurrent_program_name,
                 'Report Set', fcr.description,
                 'Request Set Stage', fcr.description,
                 fcpt.user_concurrent_program_name
                ) user_concurrent_program_name,
             fcr.description, fcr.argument_text, fcr.concurrent_program_id,
             fcr.parent_request_id, fcr.actual_start_date,
             fcr.actual_completion_date,
             ROUND (  (fcr.actual_completion_date - fcr.actual_start_date)
                    * 24
                    * 60,
                    4
                   ) runtime,
             DECODE (fcr.phase_code, 'C', 'No Schedule') program_status,
             fu.user_name, frt.responsibility_name, fcr.logfile_name
        FROM [email protected]_link fcr,
             [email protected]_link fcpt,
             [email protected]_link fu,
             [email protected]_link frt
       WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
         AND fcr.requested_by = fu.user_id
         AND fcr.responsibility_id = frt.responsibility_id
         AND fcr.responsibility_application_id = frt.application_id
         AND fcr.actual_completion_date >= (SELECT MAX (alert_logged_time)
                                          FROM allen.main_table
                                         WHERE program_status = 'No Schedule')
         AND fcr.phase_code = 'C';

But the above query takes too long to run. When I give the corresponding time as input, instead of 

SELECT MAX (alert_logged_time) 
FROM allen.main_table 
WHERE program_status = 'No Schedule'

I get the output very soon even. why is that so? Anyway to rectify this?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 34): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX fnd_programs_idx_concurrent_id ON fnd_concurrent_programs_tl (concurrent_program_id);
CREATE INDEX fnd_requests_idx_phase_concur_reques_respon_respon ON fnd_concurrent_requests (phase_code,concurrent_program_id,requested_by,responsibility_id,responsibility_application_id);
CREATE INDEX fnd_tl_idx_responsibility_application_id ON fnd_responsibility_tl (responsibility_id,application_id);
CREATE INDEX fnd_user_idx_user_id ON fnd_user (user_id);
CREATE INDEX main_table_idx_alert_time ON main_table (alert_logged_time);
The optimized query:
SELECT
        fcr.request_id,
        DECODE(fcpt.user_concurrent_program_name,
        'Report Set',
        fcr.description,
        'Request Set Stage',
        fcr.description,
        fcpt.user_concurrent_program_name) user_concurrent_program_name,
        fcr.description,
        fcr.argument_text,
        fcr.concurrent_program_id,
        fcr.parent_request_id,
        fcr.actual_start_date,
        fcr.actual_completion_date,
        ROUND((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60,
        4) runtime,
        DECODE(fcr.phase_code,
        'C',
        'No Schedule') program_status,
        fu.user_name,
        frt.responsibility_name,
        fcr.logfile_name 
    FROM
        [email protected]_link fcr,
        [email protected]_link fcpt,
        [email protected]_link fu,
        [email protected]_link frt 
    WHERE
        fcr.concurrent_program_id = fcpt.concurrent_program_id 
        AND fcr.requested_by = fu.user_id 
        AND fcr.responsibility_id = frt.responsibility_id 
        AND fcr.responsibility_application_id = frt.application_id 
        AND fcr.actual_completion_date >= (
            SELECT
                MAX(allen.main_table.alert_logged_time) 
            FROM
                allen.main_table 
            WHERE
                program_status = 'No Schedule'
        ) 
        AND fcr.phase_code = 'C'

Related Articles



* original question posted on StackOverflow here.