CODEWITHSUNDEEP
oracle-databaseperformancestored-proceduresoracle11g
AWS_Developer
AWS_Developer

Reputation: 856

Stored procedure for Select and Inner select query in Oracle

I have a query like this:

select PROMOTER_DSMID, 
      PROMOTER_NAME, 
      PROMOTER_MSISDN, 
      RETAILER_DSMID,
      RETAILER_MSISDN, 
      RETAILER_NAME ,
      ATTENDANCE_FLAG,
      ATTENDANCE_DATE 
from PROMO_ATTENDANCE_DETAILS
where PROMOTER_DSMID not in 
       (SELECT PROMOTER_DSMID
        FROM PROMO_ATTENDANCE_DETAILS 
        WHERE PROMOTERS_ASM_DSMID='ASM123'
        AND ATTENDANCE_FLAG='TRUE' 
        AND TRUNC(ATTENDANCE_DATE) ='16-07-17') 
   and   PROMOTERS_ASM_DSMID='ASM123'
  AND ATTENDANCE_FLAG='FALSE' 
  AND TRUNC(ATTENDANCE_DATE) ='16-07-17';

This query is taking too much time when I run this in PROD database because of large number of records.

I need to write a procedure for this but am not able to get the correct approach of how to write a procedure. Somebody please guide me.

Upvotes: 0

Views: 158

Answers (1)

APC
APC

Reputation: 146249

"was thinking to write a proc in which inner select statement can put the data in some temporary table and then from that temporary table I can run the outer select statement"

No need for that. Use a WITH clause to select the data once and use it twice.

with cte as (    
    select PROMOTER_DSMID, 
          PROMOTER_NAME, 
          PROMOTER_MSISDN, 
          RETAILER_DSMID,
          RETAILER_MSISDN, 
          RETAILER_NAME ,
          ATTENDANCE_FLAG,
          ATTENDANCE_DATE 
    from PROMO_ATTENDANCE_DETAILS
    where  PROMOTERS_ASM_DSMID='ASM123'
    AND TRUNC(ATTENDANCE_DATE) ='16-07-17'
)
select * 
from cte
where ATTENDANCE_FLAG='FALSE' 
AND PROMOTER_DSMID not in 
   (SELECT PROMOTER_DSMID
    FROM cte
    where ATTENDANCE_FLAG='TRUE') 
;

This will perform better than a temporary table, which involve a lot of disk I/O.

There are other possible performance improvements, depending on the usual tuning considerations: data volume and skew, indexes, etc

Upvotes: 1

Related Questions