Sagar Joon Sagar Joon - 7 months ago 6
SQL Question

Join query taking more time compared to query with sub-query

I have a query with sub-query which takes around 10 mins of time for execution so I thought of writing the same logic using JOIN to get results faster. But eventually the JOIN query is taking much more time compared to the sub-query.

Using sub-query

select count(distinct nrc_app_no) from nrc_doc_submit_tbl
where ng_status='Uploaded'
and (processed_by is null OR processed_by='XML1')
and nrc_app_no not in
(
select distinct nrc_app_no from nrc_doc_submit_tbl where ng_status='Parsed'
)
and
(
nrc_app_no not like '4%' and
nrc_app_no not like '5%' and
nrc_app_no not like '6%'
);


Plan :
enter image description here

Using JOIN & substr

select count(distinct a.nrc_app_no)
from
nrc_doc_submit_tbl a left join nrc_doc_submit_tbl b
on
a.nrc_app_no=b.nrc_app_no and b.ng_status='Parsed' and a.ng_status='Uploaded'
and (a.processed_by is null OR a.processed_by='XML1')
where
b.nrc_app_no is null and substr(a.nrc_app_no,1,1) not in ('4','5','6');


Plan :
enter image description here

Database : Oracle 11g

Table size : 10 million rows

I have also attached plans for both the queries.

Primary key : NRC_APP_NO , FAMILY_MEMBER_ID , NRC_DOC_SUBMIT_ID

List of Indexes :
enter image description here

MT0 MT0
Answer

You should not need to use a self-join or a correlated sub-query as you can get the same functionality with an analytic function (which will only require a single table scan):

SELECT COUNT( DISTINCT nrc_app_no )
FROM   (
  SELECT nrc_app_no,
         ng_status,
         processed_by,
         COUNT( CASE ng_status WHEN 'Parsed' THEN 1 END )
           OVER ( PARTITION BY nrc_app_no ) AS parsed_count
  FROM   nrc_doc_submit_tbl
)
WHERE  ng_status    = 'Uploaded'
AND    (processed_by is null OR processed_by='XML1')
AND    parsed_count = 0
AND    nrc_app_no   NOT LIKE '4%'
AND    nrc_app_no   NOT LIKE '5%'
AND    nrc_app_no   NOT LIKE '6%';

Alternative - Not Exists:

Back to two table (or index) scans:

SELECT COUNT( DISTINCT nrc_app_no )
FROM   nrc_doc_submit_tbl a
WHERE  ng_status    = 'Uploaded'
AND    (processed_by is null OR processed_by='XML1')
AND    NOT EXISTS (
                    SELECT 'X'
                    FROM   nrc_doc_submit_tbl b
                    WHERE  a.nrc_app_no = b.nrc_app_no
                    AND    b.ng_status  = 'Parsed'
                  )
AND    nrc_app_no   NOT LIKE '4%'
AND    nrc_app_no   NOT LIKE '5%'
AND    nrc_app_no   NOT LIKE '6%';