Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    9

    Question Unanswered: Trying to avoid a "NOT IN"

    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

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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 ... )

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can also use:
    Code:
    NOT EXISTS ( SELECT 1 FROM register.phoneme AS i WHERE NFICTS.ssin = i.ssin )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Piri,
    what are data types of nissda, nisscp, nissck and ssin?

    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 ... )
    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).)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •