IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to: PK does not exist in several child-tables?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)

PostPosted: Tue Oct 12, 2021 9:08 pm
Reply with quote

Hello everybody,

I'm seeking for a hint...
The problem is we do have one parent table which that has five depending childs with eight relations. All of them are defined as ON DELETE CASCADE.
Now I'd like to label all parent rows that do not have any relating childs in any of these five tables.
While trying to selecting instead of updating the parent rows, I got tow or three variants of queries. But I'm not happy with'em.

Code:
    SELECT alpha_pk
      FROM parent_alpha
     WHERE alpha_pk NOT IN (SELECT one_fk1        FROM child_one)
       AND alpha_pk NOT IN (SELECT one_fk2        FROM child_one)
       AND alpha_pk NOT IN (SELECT two_fk1        FROM child_two)
       AND alpha_pk NOT IN (SELECT two_fk2        FROM child_two)
       AND alpha_pk NOT IN (SELECT three_fk1      FROM child_three)
       AND alpha_pk NOT IN (SELECT three_fk2      FROM child_three)
       AND alpha_pk NOT IN (SELECT four_fk        FROM child_four)
       AND alpha_pk NOT IN (SELECT five_fk        FROM child_five)
      WITH UR;
--  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
    SELECT alpha_pk
      FROM alpha p
     WHERE NOT EXISTS
           (SELECT 1 FROM child_one   a1 WHERE p.alpha_pk = a1.one_fk1)
       AND NOT EXISTS
           (SELECT 1 FROM child_one   a2 WHERE p.alpha_pk = a2.one_fk2)
       AND NOT EXISTS
           (SELECT 1 FROM child_two   b1 WHERE p.alpha_pk = b1.two_fk1)
       AND NOT EXISTS
           (SELECT 1 FROM child_two   b2 WHERE p.alpha_pk = b2.two_fk2)
       AND NOT EXISTS
           (SELECT 1 FROM child_three c1 WHERE p.alpha_pk = c1.three_fk1)
       AND NOT EXISTS
           (SELECT 1 FROM child_three c2 WHERE p.alpha_pk = c2.three_fk2)
       AND NOT EXISTS
           (SELECT 1 FROM child_four  d  WHERE p.alpha_pk =  d.four_fk)
       AND NOT EXISTS
           (SELECT 1 FROM child_five  e  WHERE p.alpha_pk =  e.five_fk)
      WITH UR
    ;
--  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
    SELECT alpha_pk
      FROM alpha p
     WHERE NOT EXISTS
           (SELECT 1 FROM child_one   a  WHERE p.alpha_pk =  a.one_fk1
                                            OR p.alpha_pk =  a.one_fk2)
       AND NOT EXISTS
           (SELECT 1 FROM child_two   b  WHERE p.alpha_pk  = b.two_fk1
                                            OR p.alpha_pk  = b.two_fk2)
       AND NOT EXISTS
           (SELECT 1 FROM child_three c  WHERE p.alpha_pk =  c.three_fk1
                                            OR p.alpha_pk =  c.three_fk2)
       AND NOT EXISTS
           (SELECT 1 FROM child_four  d  WHERE p.alpha_pk =  d.four_fk)
       AND NOT EXISTS
           (SELECT 1 FROM child_five  e  WHERE p.alpha_pk =  e.five_fk)
      WITH UR
    ;


Supposing any of the key attributes (parent primary as well as all child foreign key attributes) are indexed, which of these variants is supposed to be the most performant one? Or is there a much better way - maybe via outer join - to get the desired result?

Thank you very much for your help.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2011
Location: USA

PostPosted: Tue Oct 12, 2021 11:14 pm
Reply with quote

Option 1:
8 inner SELECT queries with potentially huge list of returned results after each of them. Might be the worst one, but fortunately 8 SELECT are executed only once each of them.

Option 2:
8 inner SELECT queries with minimized size of returned results after each of them. But each SELECT is running for each record of the main table...

Option 3:
4 inner SELECT queries with minimized size of returned results after each of them. Seems to be the best of those three options.
Also can be improved a little bit (non-important):
Code:
... WHERE p.aplha_pk IN (one_fk1, one_fk2 )


The decision depends on: what is bigger - the number of records in the main table, or the number of corresponding records in five of foreign key tables?
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)

PostPosted: Wed Oct 13, 2021 1:39 pm
Reply with quote

sergeyken wrote:
(…)
The decision depends on: what is bigger - the number of records in the main table, or the number of corresponding records in five of foreign key tables?

Lots of thanks for the first hints.

The parent table contains 750k Rows, one of the childs with two FK contains 850k Rows, any other childs contain less than 10k rows...
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2011
Location: USA

PostPosted: Wed Oct 13, 2021 6:11 pm
Reply with quote

Looks like you may need to get the EXPLAIN report for each of those queries. Hard to say in advance.
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)

PostPosted: Thu Oct 14, 2021 6:04 pm
Reply with quote

Extract of Plan_Table:
Code:
                                                                                                                               P
                                                                                                                               A     S
                                                                                     S  S        S  S                       P  R     C
                                                                                     O  O        O  O                       A  E     A
                                                                                     R  R        R  R                       R  N     N
                                                         A                     S  S  T  T  S  S  T  T                       E  T  T  _
                                                         C   M              I  O  O  N  N  O  O  N  N                       N  _  A  D
                               Q                         C   A              N  R  R  _  _  R  R  _  _        P  M           T  Q  B  I
                               B                         E   T              D  T  T  O  G  T  T  O  G        R  I           _  B  L  R
                               L  P  M                   S   C              E  N  N  R  R  C  C  R  R        E  X           P  L  E  E
                               O  L  E                T  S   H              X  _  _  D  I  _  _  D  I        F  O           L  O  _  C
                               C  A  T                A  T   C              O  U  J  E  U  U  J  E  U        E  P           A  C  T  T
                               K  N  H                B  Y   O              N  N  O  R  P  N  O  R  P  TS    T  S           N  K  Y  I
                               N  N  O                N  P   L              L  I  I  B  B  I  I  B  B  LOCK  C  E  QBLOCK_  N  N  P  O
EXPLAIN_TIME         QUERYNO   O  O  D  TNAME         O  E   S  ACCESSNAME  Y  Q  N  Y  Y  Q  N  Y  Y  MODE  H  Q  TYPE     O  O  E  N
2021-10-13-11.39.46  21285103  1  1  0  parent_alpha  1  I   0  p1pkx       Y  N  N  N  N  N  N  N  N    N      0  SELECT   0  0  T  F
2021-10-13-11.39.46  21285103  2  1  0  child_one     2  I   0  c1pkx       Y  N  N  N  N  N  N  N  N    N      0  NCOSUB   0  1  T  F
2021-10-13-11.39.46  21285103  2  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285103  3  1  0  child_one     3  R   0              N  N  N  N  N  N  N  N  N    N   S  0  NCOSUB   0  1  T
2021-10-13-11.39.46  21285103  3  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285103  4  1  0  child_two     4  I   0  c2fk1x      Y  N  N  N  N  N  N  N  N    N   S  0  NCOSUB   0  1  T  F
2021-10-13-11.39.46  21285103  4  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285103  5  1  0  child_two     5  R   0              N  N  N  N  N  N  N  N  N    N   S  0  NCOSUB   0  1  T
2021-10-13-11.39.46  21285103  5  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285103  6  1  0  child_three   6  I   0  c3fk1x      Y  N  N  N  N  N  N  N  N    N   S  0  NCOSUB   0  1  T  F
2021-10-13-11.39.46  21285103  6  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285103  7  1  0  child_three   7  I   0  c3fk2x      Y  N  N  N  N  N  N  N  N    N   S  0  NCOSUB   0  1  T  F
2021-10-13-11.39.46  21285103  7  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285103  8  1  0  child_four    8  I   0  c4pkx       Y  N  N  N  N  N  N  N  N    N      0  NCOSUB   0  1  T  F
2021-10-13-11.39.46  21285103  8  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285103  9  1  0  child_five    9  I   0  c3fk1x      Y  N  N  N  N  N  N  N  N    N      0  NCOSUB   0  1  T  F
2021-10-13-11.39.46  21285103  9  2  3                0      0              N  N  N  N  N  Y  N  Y  N           0  NCOSUB   0  1  ~
2021-10-13-11.39.46  21285203  1  1  0  parent_alpha  1  I   0  p1pkx       Y  N  N  N  N  N  N  N  N    N      0  SELECT   0  0  T  F
2021-10-13-11.39.46  21285203  2  1  0  child_one     2  I   1  c1pkx       Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285203  3  1  0  child_one     3  R   0              N  N  N  N  N  N  N  N  N    N   S  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285203  4  1  0  child_two     4  I   1  c2fk1x      Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285203  5  1  0  child_two     5  R   0              N  N  N  N  N  N  N  N  N    N   S  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285203  6  1  0  child_three   6  I   1  c3fk1x      Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285203  7  1  0  child_three   7  I   1  c3fk2x      Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285203  8  1  0  child_four    8  I   0  c4pkx       Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285203  9  1  0  child_five    9  I   1  c3fk1x      Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285303  1  1  0  parent_alpha  1  I   0  p1pkx       Y  N  N  N  N  N  N  N  N    N      0  SELECT   0  0  T  F
2021-10-13-11.39.46  21285303  2  1  0  child_one     2  R   0              N  N  N  N  N  N  N  N  N    N   S  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285303  3  1  0  child_two     3  R   0              N  N  N  N  N  N  N  N  N    N   S  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285303  4  1  0  child_three   4  MU  0              N  N  N  N  N  N  N  N  N           3  CORSUB   1  1  T
2021-10-13-11.39.46  21285303  4  1  0  child_three   4  MX  1  c3fk2x      Y  N  N  N  N  N  N  N  N    N      2  CORSUB   1  1  T
2021-10-13-11.39.46  21285303  4  1  0  child_three   4  MX  1  c3fk1x      Y  N  N  N  N  N  N  N  N    N      1  CORSUB   1  1  T
2021-10-13-11.39.46  21285303  4  1  0  child_three   4  M   0              N  N  N  N  N  N  N  N  N    N   L  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285303  5  1  0  child_four    5  I   0  c4pkx       Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285303  6  1  0  child_five    6  I   1  c3fk1x      Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285403  1  1  0  parent_alpha  1  I   0  p1pkx       Y  N  N  N  N  N  N  N  N    N      0  SELECT   0  0  T  F
2021-10-13-11.39.46  21285403  2  1  0  child_one     2  R   0              N  N  N  N  N  N  N  N  N    N   S  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285403  3  1  0  child_two     3  R   0              N  N  N  N  N  N  N  N  N    N   S  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285403  4  1  0  child_three   4  R   0              N  N  N  N  N  N  N  N  N    N   S  0  CORSUB   1  1  T
2021-10-13-11.39.46  21285403  5  1  0  child_four    5  I   0  c4pkx       Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F
2021-10-13-11.39.46  21285403  6  1  0  child_five    6  I   1  c3fk1x      Y  N  N  N  N  N  N  N  N    N      0  CORSUB   1  1  T  F


some imo irrelevant cols not shown,
ACCESS_DEGREE, ACCESS_PGROUP_ID, JOIN_DEGREE, JOIN_PGROUP_ID, SORTC_PGROUP_ID, SORTN_PGROUP_ID, PARALLELISM_MODE, MERGE_JOIN_COLS, CORRELATION_NAME all NULL,
ROUTINE_ID, CTEREF, PARENT_PLANNO, SECTNOI all 0,
BIND_EXPLAIN_ONLY, MERGC, MERGN all N
~ means NULL

QueryNo 21285103 refers to my 1st variant,
QueryNo 21285203 refers to my 2st variant,
QueryNo 21285303 refers to my 3rd variant,
QueryNo 21285403 refers to sergeyken's little improvement of my 3rd variant / my 4th.

Instead of what I was saying there are some FK Indexes missing.

But nevertheless I agree to SergeyKen that my 3rd variant seems to be best.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 14, 2021 7:52 pm
Reply with quote

Auryn,
What is wrong with third option from your original post? It looks optimized.

You could also try LEFT JOIN with Child tables and in WHERE use IS NULL for those columns (FK) and see how that goes..
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)

PostPosted: Thu Apr 21, 2022 1:54 pm
Reply with quote

Hi everybody,

first of all please excuse me for not responding. Simply forgotten.

But: Thanks for your hints. They were very helpful.

@ Rohit: There was nothing wrong with my third option. And your fourth was a pretty good idea (but in our setting less optimal).
But this is the confirmation I was asking for.

So again thanks a lot.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need to fetch data from so many DB2 t... DB2 9
No new posts Discrepancy b/w SYSIBM tables and BMC... DB2 0
No new posts SYSIBM Tables Query DB2 8
No new posts Column names in SYSIBM tables DB2 5
No new posts insert into session tables with UR DB2 0
Search our Forums:

Back to Top