UNION ALL: Precedence Rules or Order of Access

kdima71 · #1 (**permalink**) 12-24-04, 01:24

Hi all! I am sorry for my poor English.

I have DB2 UDB 8.2 for Win2000 (after FixPack 7).

Quote:

Graeme Birchall in the book DB2 UDB V8.2 SQL Cookbook writes in
the notes to the operators UNION, INTERSECT, and EXCEPT (page 245):

Precedence Rules
When multiple operations are done in the same SQL statement, there are precedence rules:
- Operations in parenthesis are done first.
- INTERSECT operations are done before either UNION or EXCEPT.
- Operations of equal worth are done from top to bottom.

(Is chosen by me)

We look further...

Quote:

IBM in the DB2 Universal Database SQL Reference, Volume 1 version 8.2 in Chapter 4.Queries writes in a part Fullselect following (page 487):

When multiple operations are combined in an expression, operations
within parentheses are performed first. If there are no parentheses,
the operations are performed from left to right with the exception
that all INTERSECT operations are performed before UNION or EXCEPT operations.

(Is chosen by me)

I have made, following:

Code:

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
DROP TABLE t6;
DROP TABLE t7;
DROP TABLE t8;
DROP TABLE t9;

CREATE TABLE t1 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t1_NODE_CHK 
  CHECK (NODE_ID = 1)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t2 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t2_NODE_CHK 
  CHECK (NODE_ID = 2)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t3 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t3_NODE_CHK 
  CHECK (NODE_ID = 3)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t4 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t4_NODE_CHK 
  CHECK (NODE_ID = 4)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t5 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t5_NODE_CHK 
  CHECK (NODE_ID = 5)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t6 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t6_NODE_CHK 
  CHECK (NODE_ID = 6)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t7 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t7_NODE_CHK 
  CHECK (NODE_ID = 7)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t8 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t8_NODE_CHK 
  CHECK (NODE_ID = 8)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

CREATE TABLE t9 (
 DOC_ID         INTEGER     NOT NULL,
 NODE_ID        INTEGER     NOT NULL,
 CONSTRAINT t9_NODE_CHK 
  CHECK (NODE_ID = 9)
  ENFORCED 
  ENABLE QUERY OPTIMIZATION
);

Now I create a view and I load the data...

Code:

CREATE VIEW t_v AS
SELECT * FROM t1 UNION ALL
SELECT * FROM t2 UNION ALL
SELECT * FROM t3 UNION ALL
SELECT * FROM t4 UNION ALL
SELECT * FROM t5 UNION ALL
SELECT * FROM t6 UNION ALL
SELECT * FROM t7 UNION ALL
SELECT * FROM t8 UNION ALL
SELECT * FROM t9;

INSERT INTO T_V (DOC_ID, NODE_ID)
VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9);

COMMIT;

Now we shall look the plan access for the following request:
SELECT * FROM T_V

Code:

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002

Licensed Material - Program Property of IBM

IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================


	Isolation Level          = Cursor Stability

	Blocking                 = Block Unambiguous Cursors

	Query Optimization Class = 5

	Partition Parallel       = No

	Intra-Partition Parallel = No

	SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "KDIMA"


SQL Statement:

  SELECT * FROM T_V

Section Code Page = 1251

Estimated Cost = 96.904671

Estimated Cardinality = 1503.000000

(

|  Access Table Name = KDIMA.T8  ID = 9,263

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T7  ID = 9,262

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T6  ID = 9,261

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T5  ID = 9,260

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T4  ID = 9,259

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T3  ID = 9,258

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T2  ID = 9,257

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T1  ID = 9,256

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

UNION

|  Access Table Name = KDIMA.T9  ID = 9,264

|  |  #Columns = 2

|  |  Relation Scan

|  |  |  Prefetch: Eligible

|  |  Lock Intents

|  |  |  Table: Intent Share

|  |  |  Row  : Next Key Share

)

Return Data to Application

|  #Columns = 2

End of section

Optimizer Plan:

                                 RETURN 

                                 (   1) 

                                   |    

                                 UNION  

                                 (   2) 

   +-------+-------+-------+-------+-------+-------+-------+-------+

 TBSCAN  TBSCAN  TBSCAN  TBSCAN  TBSCAN  TBSCAN  TBSCAN  TBSCAN  TBSCAN 

 (   3)  (   4)  (   5)  (   6)  (   7)  (   8)  (   9)  (  10)  (  11) 

   |       |       |       |       |       |       |       |       |    

 Table:  Table:  Table:  Table:  Table:  Table:  Table:  Table:  Table: 

 KDIMA   KDIMA   KDIMA   KDIMA   KDIMA   KDIMA   KDIMA   KDIMA   KDIMA  

 T8      T7      T6      T5      T4      T3      T2      T1      T9

From the plan follows, that DB2 executes access to the tables not in that order, in which the tables were listed in the T_V view.

Let's execute this request for check

Code:

DOC_ID      NODE_ID
----------- -----------
          1           8
          1           7
          1           6
          1           5
          1           4
          1           3
          1           2
          1           1
          1           9

  9 records are selected.

I concluded sets in parentheses and changed a class of optimization (dft_queryopt) and did much other, but it has not decided a problem. And most interesting in that, that for want of INSERT the order of access what I have set for want of creation of view, and in other operations (SELECT, INSERT, DELETE) is not present.

I consider it as rather unpleasant problem, which can in the total result to
to undesirablis consequences in operation of application (for example: deadlock) !!!

Therefore I would like to receive the answers to the following questions:

1. Such situation exists only in DB2 8.2 (and only at me) either it was and in the early versions? Can somebody it check up at itself and to inform here?
2. Can somebody recommend checked work-around of this problem or any solution?
3. Can somebody explain in what business? Or it all the same bug?

Beforehand I thank for any help!

With upcoming Christmas and New Year!

Best regards, Dmitriy.

Marcus_A · #2 (**permalink**) 12-24-04, 09:22

I am sorry, but I don't understand the problem. For the select query, all the tables are accessed in parallel (I hope you have the tables on seperate disk drives or seperate disk arrays).

kdima71 · #3 (**permalink**) 12-24-04, 10:40

Hello! Thank you, that have responded! I am sorry for my poor English!

Let to set to you a leading questions?

1. What signifies, the access to the tables implements in a parallel mode? You see Intra-Partition Parallel = No!
2. Could you explain about what parallel access there is a speech?

To me the order of access to the tables is important to exclude probability of occurrence of a deadlock situation, during simultaneous access from the party of "reader" (SELECT) and "writer" (INSERT) through view, to the base tables!!! It is actual also, if Intra-Partition Parallel = Yes.

What you think concerning it?

Best regards, Dmitriy.

n_i · #4 (**permalink**) 12-24-04, 15:14

I see what you're concerned about. However, I think you're mixing together two unrelated things: "abstract" operations upon sets (the SQL part) and "concrete" implementation of those operations by the DB2 engine (the execution plan part).

From the SQL point of view, given two sets A and B the operations "A union all B" and "B union all A" are equivalent - they produce the same resulting set. Further, SQL operations (or rather relational operations) deal with unordered sets, which means that the elements in the source sets are not considered to be ordered in any particular way, and the resulting set is also not ordered (unless you specify ORDER BY).

Now, knowing this the optimizer doesn't have to read your tables in any particular order, especially given multiple ioservers, which seems to be the case.

I don't believe there is a way to force the database engine read your tables in the order they are specified in the view definition, especially considering the fact the a view is just a syntactical construct and not a physical object.

And, by the way, the computerized translator you're using is horrible :-)

n_i · #5 (**permalink**) 12-24-04, 15:19

I see what you're concerned about. However, I think you're mixing together two unrelated things: "abstract" operations upon sets (the SQL part) and "concrete" implementation of those operations by the DB2 engine (the execution plan part).

From the SQL point of view, given two sets A and B the operations "A union all B" and "B union all A" are equivalent - they produce the same resulting set. Further, SQL operations (or rather relational operations) deal with unordered sets, which means that the elements in the source sets are not considered to be ordered in any particular way, and the resulting set is also not ordered (unless you specify ORDER BY).

The precedence rules you were quoting are applied at that level. However, no matter how you group the UNION ALL operations you will still get the same resuling set with elements in no particular order.

Now, knowing this the optimizer doesn't have to read your tables in any specific order, especially given multiple ioservers, which seems to be the case.

I don't believe there is a way to force the database engine read your tables in the order they are specified in the view definition, especially considering the fact the a view is just a syntactical construct and not a physical object.

And, by the way, the computerized translator you're using is horrible :-)

Marcus_A · #6 (**permalink**) 12-25-04, 09:56

Intra-partition parallelism is a tricky subject and I don't know whether the same rules apply when using a UNION ALL view and other types of intra-partition parallelism. Based on the SELECT statement that you explained, I would guess that the parallelism is in effect for that statement.

kdima71 · #7 (**permalink**) 12-27-04, 06:48

Hi all! I am sorry for my poor English.

Many thanks to all for explanations! I did not take into account some things!

I want to replace, that I do not do tragedy of this problem but...

Whether I can ascertain once again the following unpleasant fact following from your explanations and my experience? As DB2 cannot provide the order of access to tables, as they have been listed in the view definition, in cases of simultaneous access to these tables through such view (pseudo-division of the data with help UNION ALL), "writer" who inserts the big number of new records (and records will be distributed on are several to tables) and "reader" (and the query will return the data from several tables), is possible deadlock!

I shall explain, the deadlock will take place, as at an insert, DB2 access to tables in that order as in definition of view will provide, and at query other order will be used!

Are You agree with me, what it is very unpleasant problem (for example, in SQL Replication an environment)? If, yes that you can advise? And why IВМ till now has not resolved such problem?

P.S. I while the beginner in DB2, but have sufficient experience in Oracle to put before myself problems and them to solve now while I am in a stage of studying DB2. Shortly I am waited with heavy practice...

Beforehand I thank for any help!

Best Regards, Dmitriy.

Peter.Vanroose · #8 (**permalink**) 12-29-04, 08:31

Dear Dimitriy,

I cannot give a checked work-around of this problem, neither do I have any idea about this being a V8.2 specific problem.
I just want to comment on your quote of the "SQL Cookbook":

The top-bottom order in which UNION etc. are performed is just an indication for the writers/readers of SQL to obtain a correct interpretation.
The actual implementation (optimizer) is free to use a different order, as long as the resulting table is identical (up to the ordering of the rows, which is never guaranteed when no ORDER BY is issued).

-- Peter Vanroose.

Internet Services

Web Development

Digital Marketing

Consumer Tech