|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 84
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
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 |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 84
Location: Lower Saxony (DE)
|
|
|
|
| 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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Looks like you may need to get the EXPLAIN report for each of those queries. Hard to say in advance. |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 84
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 84
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
|
|
|
