Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2012
    Posts
    5

    Unanswered: Strange SELECT query behaviour

    Hi all,

    I wonder if anybody could help me.

    Have the following SQL query used to query DB2 database tables at z/OS:
    SELECT DISTINCT
    A.FIELD1,
    A.FIELD2,
    A.FIELD3,
    FROM TABLE1 AS A,
    TABLE2 AS B,
    TABLE3 AS C,
    TABLE4 AS D,
    TABLE5 AS E
    WHERE A.FIELD4 = value1 AND
    A.FIELD5 IS NULL AND
    A.FIELD6 = value2 AND
    A.FIELD1 LIKE value3
    AND
    ((B.FIELD4 = value1 AND
    B.FIELD5 IS NULL AND
    B.FIELD6 = value2 AND
    B.FIELD1 = A.FIELD1 AND
    B.FIELD7 LIKE value4)
    OR
    (C.FIELD4 = value1 AND
    C.FIELD5 IS NULL AND
    C.FIELD6 = value2 AND
    C.FIELD1 = A.FIELD1 AND
    C.FIELD7 LIKE value4)
    OR
    (D.FIELD4 = value1 AND
    D.FIELD5 IS NULL AND
    D.FIELD6 = value2 AND
    D.FIELD1 = A.FIELD1 AND
    D.FIELD7 LIKE value4)
    OR
    (E.FIELD4 = value1 AND
    E.FIELD5 IS NULL AND
    E.FIELD6 = value2 AND
    E.FIELD1 = A.FIELD1 AND
    E.FIELD7 LIKE value4))
    ORDER BY A.FIELD1, A.FIELD2;

    The problem is that this performance killing thing works only when all of the tables B,C,D and E contain data. If one of these tables is empty - no rows is returned....

    Is it my hallucination or it is really OR stays in there for B-E tables search conditions? Any ideas?

  2. #2
    Join Date
    Jul 2012
    Posts
    5
    I'v just realized I have posted the issue to incorect place.
    So I wouldn't mind if moderators move this thread to ANSI SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    It is a inner join in your query。So if one of the tables in inner join is empty ,
    then the result is empty too. It is a basic rule。
    For perfomance consideration ,I do some test for you 。
    I create tables T1,T2,T3,T4 and run the query":
    select * from t1,t2,t3,t4 where t1.c1 = t2.c1 or t1.c1 = t3.c1 or t1.c1 = t4.c1.
    And capture the execute plan for it :
    Code:
    Original Statement:
    ------------------
    select * 
    from t1,t2,t3,t4 
    where t1.c1 = t2.c1 or t1.c1 = t3.c1 or t1.c1 = t4.c1
    
    
    Optimized Statement:
    -------------------
    SELECT Q4."C1" AS "C1", Q4."C2" AS "C2", Q3."C1" AS "C1", Q3."C2" AS "C2", 
            Q2."C1" AS "C1", Q2."C2" AS "C2", Q1."C1" AS "C1", Q1."C2" AS "C2" 
    FROM DB2INST1.T4 AS Q1, DB2INST1.T3 AS Q2, DB2INST1.T2 AS Q3, DB2INST1.T1 AS 
            Q4 
    WHERE (((Q4."C1" = Q3."C1") OR (Q4."C1" = Q2."C1")) OR (Q4."C1" = Q1."C1"))
    
    Access Plan:
    -----------
    	Total Cost: 		30.3606
    	Query Degree:		1
    
                                         Rows 
                                        RETURN
                                        (   1)
                                         Cost 
                                          I/O 
                                          |
                                         9.25 
                                        NLJOIN
                                        (   2)
                                        30.3606 
                                           4 
                                 /---------+--------\
                               4                    2.3125 
                            NLJOIN                  TBSCAN
                            (   3)                  (   8)
                            22.7508                 7.58838 
                               3                       1 
                      /--------+-------\              |
                    1                     4            4 
                 NLJOIN                TBSCAN   TABLE: DB2INST1
                 (   4)                (   7)         T3
                 15.1656               7.58519 
                    2                     1 
              /-----+-----\              |
            1                1            4 
         TBSCAN           TBSCAN   TABLE: DB2INST1
         (   5)           (   6)         T1
         7.58281          7.58281 
            1                1 
           |                |
            1                1 
     TABLE: DB2INST1  TABLE: DB2INST1
           T4               T2
    And i noticed the join
    between T4 and T2 (step 4) , and T3 (step 3)
    is a Cartesian product join。
    No predicate used in the join:
    Code:
    4) NLJOIN: (Nested Loop Join)
    		Cumulative Total Cost: 		15.1656
    		Cumulative CPU Cost: 		99074
    		Cumulative I/O Cost: 		2
    		Cumulative Re-Total Cost: 	0.00317217
    		Cumulative Re-CPU Cost: 	6888
    		Cumulative Re-I/O Cost: 	0
    		Cumulative First Row Cost: 	15.164
    		Estimated Bufferpool Buffers: 	2
    
    		Arguments:
    		---------
    		EARLYOUT: (Early Out flag)
    			NONE
    		FETCHMAX: (Override for FETCH MAXPAGES)
    			IGNORE
    		ISCANMAX: (Override for ISCAN MAXPAGES)
    			IGNORE
    		JN INPUT: (Join input leg)
    			OUTER
                            <----------------There should be a predicates section
    		Input Streams:
    		-------------
    			2) From Operator #5
    
    				Estimated number of rows: 	1
    				Number of columns: 		2
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q1."C2"+Q1."C1"
    
    			4) From Operator #6
    
    				Estimated number of rows: 	1
    				Number of columns: 		2
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q3."C2"+Q3."C1"
    
    
    		Output Streams:
    		--------------
    			5) To Operator #3
    
    				Estimated number of rows: 	1
    				Number of columns: 		4
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q1."C2"+Q1."C1"+Q3."C2"+Q3."C1"
    
    3) NLJOIN: (Nested Loop Join)
    		Cumulative Total Cost: 		22.7508
    		Cumulative CPU Cost: 		153777
    		Cumulative I/O Cost: 		3
    		Cumulative Re-Total Cost: 	0.00713738
    		Cumulative Re-CPU Cost: 	15498
    		Cumulative Re-I/O Cost: 	0
    		Cumulative First Row Cost: 	22.7461
    		Estimated Bufferpool Buffers: 	3
    
    		Arguments:
    		---------
    		EARLYOUT: (Early Out flag)
    			NONE
    		FETCHMAX: (Override for FETCH MAXPAGES)
    			IGNORE
    		ISCANMAX: (Override for ISCAN MAXPAGES)
    			IGNORE
    		JN INPUT: (Join input leg)
    			OUTER
     
                  <----------------There should be a predicates section                     
     
    		Input Streams:
    		-------------
    			5) From Operator #4
    
    				Estimated number of rows: 	1
    				Number of columns: 		4
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q1."C2"+Q1."C1"+Q3."C2"+Q3."C1"
    
    			7) From Operator #7
    
    				Estimated number of rows: 	4
    				Number of columns: 		2
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q4."C2"+Q4."C1"
    
    
    		Output Streams:
    		--------------
    			8) To Operator #2
    
    				Estimated number of rows: 	4
    				Number of columns: 		6
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q1."C2"+Q1."C1"+Q3."C2"+Q3."C1"+Q4."C2"
    				+Q4."C1"
    And db2 evaluate all the predicates in the last join (step 2)
    Code:
    2) NLJOIN: (Nested Loop Join)
    		Cumulative Total Cost: 		30.3606
    		Cumulative CPU Cost: 		261975
    		Cumulative I/O Cost: 		4
    		Cumulative Re-Total Cost: 	0.0357389
    		Cumulative Re-CPU Cost: 	77603
    		Cumulative Re-I/O Cost: 	0
    		Cumulative First Row Cost: 	30.3299
    		Estimated Bufferpool Buffers: 	4
    
    		Arguments:
    		---------
    		EARLYOUT: (Early Out flag)
    			NONE
    		FETCHMAX: (Override for FETCH MAXPAGES)
    			IGNORE
    		ISCANMAX: (Override for ISCAN MAXPAGES)
    			IGNORE
    
    		Predicates:
    		----------
    		2) Predicate used in Join
    			Comparison Operator: 		Not Applicable
    			Subquery Input Required: 	No
    			Filter Factor: 			0.578125
    
    			Predicate Text:
    			--------------
    			(((Q4."C1" = Q3."C1") OR (Q4."C1" = Q2."C1")) OR 
    			        (Q4."C1" = Q1."C1"))
    
    
    		Input Streams:
    		-------------
    			8) From Operator #3
    
    				Estimated number of rows: 	4
    				Number of columns: 		6
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q1."C2"+Q1."C1"+Q3."C2"+Q3."C1"+Q4."C2"
    				+Q4."C1"
    
    			10) From Operator #8
    
    				Estimated number of rows: 	2.3125
    				Number of columns: 		2
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q2."C2"+Q2."C1"
    
    
    		Output Streams:
    		--------------
    			11) To Operator #1
    
    				Estimated number of rows: 	9.25
    				Number of columns: 		8
    				Subquery predicate ID: 		Not Applicable
    
    				Column Names:
    				------------
    				+Q5."C2"+Q5."C1"+Q5."C2"+Q5."C1"+Q5."C2"
    				+Q5."C1"+Q5."C2"+Q5."C1"
    before the last join , number of rows of the intermediate result is the product of number of rows in table A,B,C,D or B,C,D,E etc (It is determined by the join order chose by db2 ).
    So you need to reconsider that "The result of the query is really what you want"?

  4. #4
    Join Date
    Jul 2012
    Posts
    5
    Thank you so much!
    I just fill like full dummy in SQL and that is not far from truth, so digging into the books again.

    Regarding the performance issues - don't mind, I know that the query is terrible. The DB2 database I work with is antient and there is no way i could change its tables structure.

    About your advice to reconsider if it is really what I want - you have missed one important thing in B-E tables subqueries - I'm checking field with LIKE there.
    And this is the major reason why i use this query.
    Let me explain a little.
    The table A contains summary info on some object named in OBJECTNAME column for example and the details on that object may contains in tables from B to E.
    These tables contain detailed information on the object including ATTRIBUTE column that may be common for all tables through B to E.

    So I want to get all distinct OBJECTNAME values from table A which are contained in tables B through E AND which have ATTRIBUTE value in either B,C,D or E tables like specified VALUE.

    I understand that I may ask too much, but can you just advice How I can make work this query the way I want to.
    I mean if you understand the logic I need, If I can use the single SELECT statement to reach the goal?

    thanks in advance
    Last edited by Pafnutiy; 07-04-12 at 05:55.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed that you might want to select rows in table A, if any rows in (B, C, D, E) which satisfy the conditions were exists.

    If my guess was right, please try EXISTS predicates.

    Example 1:
    Code:
    SELECT /*DISTINCT*/
           A.FIELD1
         , A.FIELD2
         , A.FIELD3
     FROM  TABLE1 AS A
     WHERE
           A.FIELD1 LIKE value3
       AND A.FIELD4 = value1
       AND A.FIELD5 IS NULL
       AND A.FIELD6 = value2
       AND
      (    EXISTS
          (SELECT 0
            FROM  TABLE2 AS B
            WHERE B.FIELD1 = A.FIELD1
              AND B.FIELD4 = value1
              AND B.FIELD5 IS NULL
              AND B.FIELD6 = value2
              AND B.FIELD7 LIKE value4
          )
       OR  EXISTS
          (SELECT 0
            FROM  TABLE3 AS C
            WHERE C.FIELD1 = A.FIELD1
              AND C.FIELD4 = value1
              AND C.FIELD5 IS NULL
              AND C.FIELD6 = value2
              AND C.FIELD7 LIKE value4
          )
       ...
       ...
      )
     ORDER BY
           A.FIELD1
         , A.FIELD2
    ;
    Example 2:
    Code:
    SELECT /*DISTINCT*/
           A.FIELD1
         , A.FIELD2
         , A.FIELD3
     FROM  TABLE1 AS A
     WHERE
           A.FIELD1 LIKE value3
       AND A.FIELD4 = value1
       AND A.FIELD5 IS NULL
       AND A.FIELD6 = value2
       AND
           EXISTS
          (SELECT 0
            FROM (
                  SELECT * FROM TABLE2
                  UNION ALL
                  SELECT * FROM TABLE3
                  UNION ALL
                  SELECT * FROM TABLE4
                  UNION ALL
                  SELECT * FROM TABLE5
                 ) U
            WHERE U.FIELD1 = A.FIELD1
              AND U.FIELD4 = value1 /* or U.FIELD4 = A.FIELD4 */
              AND U.FIELD5 IS NULL
              AND U.FIELD6 = value2 /* or U.FIELD6 = A.FIELD6 */
              AND U.FIELD7 LIKE value4
          )
     ORDER BY
           A.FIELD1
         , A.FIELD2
    ;

  6. #6
    Join Date
    Jul 2012
    Posts
    5
    That's cool thanks.

    The first one is exactly what i need and it works for me, but the second is not working as tables have different number of columns so UNION couldn't be built.

    So inner join is like multiplication, i mean it will join the tables data if they are not empty, once any of tables is empty - it like multiplication with zero.
    By the way, query with EXISTS works much more faster then the original query.
    Thank you very much.
    Last edited by Pafnutiy; 07-04-12 at 09:24.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If tables have different number of columns, specify necessary columns in each subselects of UNION.

    Example 3:
    Code:
    SELECT A.FIELD1
         , A.FIELD2
         , A.FIELD3
     FROM  TABLE1 AS A
     WHERE
           A.FIELD1 LIKE value3
       AND A.FIELD4 = value1
       AND A.FIELD5 IS NULL
       AND A.FIELD6 = value2
       AND
           EXISTS
          (SELECT 0
            FROM (
                  SELECT FIELD1 , FIELD4 , FIELD5 , FIELD6 , FIELD7
                   FROM  TABLE2
                  UNION ALL
                  SELECT FIELD1 , FIELD4 , FIELD5 , FIELD6 , FIELD7
                   FROM  TABLE3
                  UNION ALL
                  SELECT FIELD1 , FIELD4 , FIELD5 , FIELD6 , FIELD7
                   FROM  TABLE4
                  UNION ALL
                  SELECT FIELD1 , FIELD4 , FIELD5 , FIELD6 , FIELD7
                   FROM  TABLE5
                 ) U
            WHERE U.FIELD1 = A.FIELD1
              AND U.FIELD4 = A.FIELD4
              AND U.FIELD5 IS NULL
              AND U.FIELD6 = A.FIELD6
              AND U.FIELD7 LIKE value4
          )
     ORDER BY
           A.FIELD1
         , A.FIELD2
    ;

  8. #8
    Join Date
    Jul 2012
    Posts
    5
    Yes, I've already tried this one too
    Thank you very much!

Posting Permissions

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