tHanKs Mohan !!!

always got struck with a querys.. 
Mohan, SQL Geek always helps me in fixing it..

this page is dedicated to him..

Tuesday, 24 Oct 2017

1. didn't thought of adding multiple parameter as comma separated in query. he did it in seconds.
 SELECT WORKER_ID,WORKER_NM FROM SHIFT_DET WHERE (SHIFT_ID , ROLE_CD ) IN  (
SELECT SHIFT_ID , ROLE_CD FROM SHIFT_DET WHERE SHIFT_DET_ID = 7451 )  

Wednesday, 25 Oct 2017

2. Query Optimization
Asked sql geek to look into the same query little bit modified by me.

SELECT WORKER_ID,
       WORKER_NM,
       STEV_SHIFT_DET_ID,
       SHIFT_SRC_DET_ID,
       stev_shift_id
  FROM (SELECT WORKER_ID,
               WORKER_NM,
               STEV_SHIFT_DET_ID,
               SHIFT_SRC_DET_ID,
               stev_shift_id
          FROM JPSNS.SNS_STEV_SHIFT_DET
         WHERE     (STEV_SHIFT_ID, ROLE_CD) IN
                      (SELECT STEV_SHIFT_ID, ROLE_CD
                         FROM JPSNS.SNS_STEV_SHIFT_DET
                        WHERE STEV_SHIFT_DET_ID = 7203)
               AND STEV_SHIFT_DET_ID <> 7203) A
 WHERE STEV_SHIFT_DET_ID NOT IN (SELECT NVL (SHIFT_SRC_DET_ID, 0)
                                   FROM JPSNS.SNS_STEV_SHIFT_DET

                                  WHERE stev_shift_id = A.stev_shift_id)

he altered the query like this,

SELECT WORKER_ID,
               WORKER_NM,
               STEV_SHIFT_DET_ID,
               SHIFT_SRC_DET_ID,
               stev_shift_id
          FROM JPSNS.SNS_STEV_SHIFT_DET d
         WHERE     (STEV_SHIFT_ID, ROLE_CD) IN
                      (SELECT STEV_SHIFT_ID, ROLE_CD
                         FROM JPSNS.SNS_STEV_SHIFT_DET
                        WHERE STEV_SHIFT_DET_ID = 7203)
               AND STEV_SHIFT_DET_ID <> 7203
 and not exists (SELECT 'X' FROM JPSNS.SNS_STEV_SHIFT_DET d1
                                  WHERE stev_shift_id = d.stev_shift_id and
                                  d.STEV_SHIFT_DET_ID = d1.SHIFT_SRC_DET_ID

                                  );

also he looked into cost of both the queries using CTRL + E in toad.

and gave me pretty good suggestions, like 
TABLE ACCESS SHOULD NOT COME FULL for that need to set index for the shift id.
also said, using of NOT IN is elementary practice, better to use NOT EXIST.