Set A MINUS Set B 1

[SkipVought]

I need to perform a query like this, but this runs slow for a subset and hangs up for the entire table...

SELECT Distinct
  MBS.CC
, MBS.MACH_GRP

FROM FPRPTSAR_MC_BUILD_SCHEDULE_PAST_SS MBS

WHERE MBS.MACH_GRP NOT IN 
(
Select C.MACH_GRP 
From FPRPTSAR_CDS_STANDARDS C 
Where C.CC=MBS.CC
);

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I don't kow if that will make any difference, but I would try:

SELECT Distinct
  MBS.CC
, MBS.MACH_GRP
FROM FPRPTSAR_MC_BUILD_SCHEDULE_PAST_SS MBS
WHERE MBS.MACH_GRP NOT IN 
([blue]
Select C.MACH_GRP 
From FPRPTSAR_CDS_STANDARDS C, 
     FPRPTSAR_MC_BUILD_SCHEDULE_PAST_SS MYMBS
Where C.CC = MYMBS.CC[/blue]
);

where BLUE Select would be totally 'independent' from the main Select statement.

Also, I don't kow how many results you would get out of BLUE select, but in ORACLE there is a limit of 1000 elements that can be used in IN part.

Have fun.

---- Andy

 

[traingamer]

What about a simple unmatched query (with a left join):

SELECT Distinct
  MBS.CC
, MBS.MACH_GRP

FROM FPRPTSAR_MC_BUILD_SCHEDULE_PAST_SS MBS

left join FPRPTSAR_CDS_STANDARDS C on  [b](C.CC=MBS.CC and
C.MACH_GRP = MBS.MACH_GRP[/b])

where C.MACH_GRP is null

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[jedraw]

You could try the NOT EXISTS approach - which is usually faster than NOT IN

SELECT Distinct
  MBS.CC
, MBS.MACH_GRP

FROM FPRPTSAR_MC_BUILD_SCHEDULE_PAST_SS MBS

WHERE NOT EXISTS
(
Select "X"
From FPRPTSAR_CDS_STANDARDS C
Where C.CC=MBS.CC
);

 

[SkipVought]

Greg,

I had seen that technique before, but could not remember the syntax.

Thanks! It performed well.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}