Hello people;

Thank you in advance for taking the time to read my post.

I am executing an export to create a csv file.
For this export, I am using a query containing a NOT IN, which I think is the reason why it has been running for 3 days now... so far, it has checked over 6 million records.

The query uses 3 tables, of which 2 have a common column called 'ssin'.
The 3rd table does not have this column, but I can 'reconstruct' it based upon 3 other columns (nissda, nisscp and nissck). That is why I use the substr and digits commands below.

The query is this one (never mind the substr and so in the first lines):

SELECT * from prodadm.nficts NFICTS where
DECIMAL(SUBSTR(DIGITS(nficts.nissda),5,2) ||SUBSTR(DIGITS(nficts.nissda),3,2) ||SUBSTR(DIGITS(nficts.nissda),1,2)||SUBSTR(DIGITS (nficts.nisscp),1,3)||SUBSTR(DIGITS(nficts.nissck) ,1,2),11,0)

in (SELECT ssin from register.person_identifier where person_status_code=3 and replaced_by is null and ssin
not in (SELECT ssin from register.phoneme))

Does anyone have any idea how I could speed up things?
I do not have that much experience. I thought about using a join, but how does one replace a 'NOT IN' by a join?

Many thanks in advance for any input!
Carl

At first sight the DECIMAL(SUBSTR(DIGITS ... is more suspicious than the NOT IN.

Be aware of
NOT IN ( SELECT ssin ... )
will fail, if ssin is NULL-able ( and at least one row contains <NULL> ).


You can rewrite the NOT IN with a LEFT JOIN:

SELECT ssin from register.person_identifier a
LEFT JOIN register.phoneme b
ON a.ssin = b.ssin
where person_status_code=3 and replaced_by is null and b.ssin IS NULL

Consider also to deriver the values of nissda, nisscp and nissck from ssid instead of vice versa.

SELECT * from prodadm.nficts NFICTS
where (nissda, nisscp, nissck) IN
(SELECT
--deriver nissda from ssin-- AS nissda,
--deriver nisscp from ssin-- AS nisscp,
--deriver nissck from ssin-- AS nissck
from register.person_identifier where ... )

You can also use:

Piri,
what are data types of nissda, nisscp, nissck and ssin?

I agree this.

But, if data type of (for example) nissck is not DEC(2,x),
a predicate for NFICTS.nissck might not be able to be equal.
Because you compared first two digits from nficts.nissck,
then nficts.nissck must be a range of values.

For example:
If data type of nissck was DEC(3,0),
a predicate for nissck might be like...
nficts.nissck BETWEEN MOD(ssin , 100) * 10 AND MOD(ssin , 100) * 10 + 9
(I assumed data type of ssin was DEC(11,0).)