/*
常久以來我一直認為將資料先以條件挑出，再包在 inner view 裡，執行效能會比較好。
但是用 SQL Explain Tool，發現結果並不是這樣。
*/
/* 建立測試 Tables，relations below:
A1
└ A2 join on COL1
└ A3 join on COL1,COL3,COL4
*/
CREATE TABLE A1
(
COL1 VARCHAR(64)
) IN USERSPACE1;
CREATE TABLE A2
(
COL1 VARCHAR(64),
COL3 VARCHAR(64),
COL4 INTEGER
) IN USERSPACE1;
CREATE TABLE A3
(
COL1 VARCHAR(64),
COL3 VARCHAR(64),
COL4 INTEGER,
COL5 INTEGER
) IN USERSPACE1;
/* insert 測試資料 */
INSERT INTO A1 SELECT NAME FROM SYSIBM.SYSTABLES;
/* random 產生0或1，來新增測試資料 */
INSERT INTO A2
SELECT TBNAME,NAME,INT(RAND()* 2)
FROM SYSIBM.SYSCOLUMNS;
/* random 產生 negative number，來新增測試資料 */
INSERT INTO A3
SELECT COL1,COL3,COL4,
CASE WHEN COL5 < 0 THEN NULL ELSE COL5 END
FROM (SELECT A2.*,
INT(RAND()*10000)-INT(RAND()*10000) COL5
FROM A2) T;
/* 將 inner view 方式所下的 sql 存成 strsql.sql */
SELECT *
FROM A1
INNER JOIN (SELECT *
FROM A2
WHERE COL4 = 1) A2 ON A2.COL1 = A1.COL1
INNER JOIN (SELECT *
FROM A3
WHERE COL5 IS NULL) A3 ON A3.COL1 = A2.COL1
AND A3.COL3 = A2.COL3
AND A3.COL4 = A2.COL4;
/* 以 db2expln 測試使用 inner view 的結果 */
C:\>db2expln -database sample -stmtfile strsql.sql -terminator ; -output strsql.
exp -g
/* 截取結果如下 */
Section Code Page = 1208
Estimated Cost = 104.724731
Estimated Cardinality = 6.353128
Access Table Name = ORION.A2 ID = 2,47
| #Columns = 3
| Avoid Locking Committed Data
| Evaluate Block/Data Predicates Before Locking Committed Row
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 1
| | Process Build Table for Hash Join
Hash Join
| Estimated Build Size: 224000
| Estimated Probe Size: 104000
| Access Table Name = ORION.A3 ID = 2,48
| | #Columns = 4
| | Avoid Locking Committed Data
| | Evaluate Block/Data Predicates Before Locking Committed Row
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 2
| | | Process Probe Table for Hash Join
Hash Join
| Estimated Build Size: 4000
| Estimated Probe Size: 20000
| Bit Filter Size: 800
| Access Table Name = ORION.A1 ID = 2,36
| | #Columns = 1
| | Avoid Locking Committed Data
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Return Data to Application
| #Columns = 8
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
6.35313
RETURN
( 1)
104.725
|
6.35313
HSJOIN
( 2)
104.725
/ \
466 5.5624
TBSCAN HSJOIN
( 3) ( 4)
15.4103 89.2723
| / \
466 1423.23 2883
Table: TBSCAN TBSCAN
ORION ( 5) ( 6)
A1 44.9003 43.5846
| |
5544 5544
Table: Table:
ORION ORION
A3 A2
/* 將非 inner view 方式所下的 sql 存成 strsql1.sql */
SELECT *
FROM A1,A2,A3
WHERE A2.COL1 = A1.COL1
AND A3.COL1 = A2.COL1
AND A3.COL3 = A2.COL3
AND A3.COL4 = A2.COL4
AND A2.COL4 = 1
AND A3.COL5 IS NULL;
/* 以 db2expln 測試非使用 inner view 的結果 */
C:\>db2expln -database sample -stmtfile strsql1.sql -terminator ; -output strsql
1.exp -g
/* 截取結果如下(與使用 inner view 的結果一樣) */
Section Code Page = 1208
Estimated Cost = 104.724731
Estimated Cardinality = 6.353128
Access Table Name = ORION.A2 ID = 2,47
| #Columns = 3
| Avoid Locking Committed Data
| Evaluate Block/Data Predicates Before Locking Committed Row
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 1
| | Process Build Table for Hash Join
Hash Join
| Estimated Build Size: 224000
| Estimated Probe Size: 104000
| Access Table Name = ORION.A3 ID = 2,48
| | #Columns = 4
| | Avoid Locking Committed Data
| | Evaluate Block/Data Predicates Before Locking Committed Row
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 2
| | | Process Probe Table for Hash Join
Hash Join
| Estimated Build Size: 4000
| Estimated Probe Size: 20000
| Bit Filter Size: 800
| Access Table Name = ORION.A1 ID = 2,36
| | #Columns = 1
| | Avoid Locking Committed Data
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Return Data to Application
| #Columns = 8
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
6.35313
RETURN
( 1)
104.725
|
6.35313
HSJOIN
( 2)
104.725
/ \
466 5.5624
TBSCAN HSJOIN
( 3) ( 4)
15.4103 89.2723
| / \
466 1423.23 2883
Table: TBSCAN TBSCAN
ORION ( 5) ( 6)
A1 44.9003 43.5846
| |
5544 5544
Table: Table:
ORION ORION
A3 A2
