-
1. Re: Same query returning different Output Rows through VDB
shawkins Dec 18, 2017 11:16 AM (in response to adi22)Can you clarify what is being shown with the plans? In the first query you show a plan with a dup removal node - which would be present when performing distinct or just a union. The second plan does not show the dup remove operation and seems to have additional parent nodes as it shows information related to a join at the bottom.
-
2. Re: Same query returning different Output Rows through VDB
adi22 Dec 22, 2017 7:52 AM (in response to adi22)I have created 3 VDBs' as below:
1. ST_VDB
This VDB imports data from 2 datasources ( Server A and Server B).
I have wrote DDL as below to get federated data from both:
CREATE VIEW D_HOUR(
HOUR_ID short,
OFFICE_TIME short,
DAY_TIME short,
NIGHT_TIME short)
AS
SELECT HOUR_ID ,
OFFICE_TIME ,
DAY_TIME ,
NIGHT_TIME FROM "SrcModel_3"."D_HOUR"
UNION
SELECT HOUR_ID ,
OFFICE_TIME ,
DAY_TIME ,
NIGHT_TIME FROM "SrcModel_4"."D_HOUR";
Above is an example of one table. Likewise, I have created for all the tables.
2. A_VDB
This VDB imports data from single data source ( Server A )
3. B_VDB
This VDB imports data from single data source ( Server B )
Now I am executing the below mentioned query on ST_VDB, A_VDB and, B_VDB.
Number of rows fetched from ST_VDB: 161
Number of rows fetched from A_VDB: 96
Number of rows fetched from B_VDB: 0
Ideally, ST_VDB should return 96 rows only. Why it is returning 161?
Below is the query and query plan for both(ST_VDB and A_VDB)
====================================================================
Query:
====================================================================
SELECT DISTINCT
DC.D_DATE.DATE_ID,
DC.D_TIME.HOUR_ID,
DC.D_TIME.MIN_ID,
DC.D_E_L_EUC_CE.OS_ID,
DC.D_E_L_EUC_CE.ER_ID,
DC.D_E_L_EUC_CE.EuId,
DC.E_ER_EU_V_RA.DC_Re
FROM
DC.D_DATE,
DC.D_TIME,
DC.D_E_L_EUC_CE,
DC.E_ER_EU_V_RA,
DC.D_RoSt
WHERE
( DC.E_ER_EU_V_RA.RoSt=DC.D_RoSt.RoSt )
AND ( DC.E_ER_EU_V_RA.HOUR_ID=DC.D_TIME.HOUR_ID and DC.E_ER_EU_V_RA.MIN_ID=DC.D_TIME.MIN_ID )
AND ( DC.E_ER_EU_V_RA.DATE_ID=DC.D_DATE.DATE_ID )
AND ( DC.E_ER_EU_V_RA.OS_ID=DC.D_E_L_EUC_CE.OS_ID )
AND ( DC.E_ER_EU_V_RA.ER=DC.D_E_L_EUC_CE.ER_ID )
AND ( DC.E_ER_EU_V_RA.SN=DC.D_E_L_EUC_CE.ER_FDN )
AND ( DC.E_ER_EU_V_RA.EUtrFD=DC.D_E_L_EUC_CE.EuId )
AND
(
( ( DC.D_RoSt.RoSt ) NOT IN ('DUPL','SUSP') )
AND
DC.D_DATE.DATE_ID = '2017-11-21'
AND
DC.E_ER_EU_V_RA.EUtrFD = 'ER-2'
)
GROUP BY
DC.D_DATE.DATE_ID,
DC.D_TIME.HOUR_ID,
DC.D_TIME.MIN_ID,
DC.D_E_L_EUC_CE.OS_ID,
DC.D_E_L_EUC_CE.ER_ID,
DC.D_E_L_EUC_CE.EuId,
DC.E_ER_EU_V_RA.DC_Re
====================================================================
Query Plan from ST_VDB
====================================================================
ProjectNode
+ Relational Node ID:1
+ Output Columns:
0: DATE_ID (date)
1: HOUR_ID (short)
2: MIN_ID (short)
3: OS_ID (string)
4: ER_ID (string)
5: EuId (string)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 161
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 152
3: Node Cumulative Process Time: 4164
4: Node Next Batch Calls: 23
5: Node Blocks: 22
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
GroupingNode
+ Relational Node ID:2
+ Output Columns:
0: gcol0 (date)
1: gcol1 (short)
2: gcol2 (short)
3: gcol3 (string)
4: gcol4 (string)
5: gcol5 (string)
6: gcol6 (string)
+ Statistics:
0: Node Output Rows: 161
1: Node Next Batch Process Time: 26
2: Node Cumulative Next Batch Process Time: 152
3: Node Cumulative Process Time: 4164
4: Node Next Batch Calls: 23
5: Node Blocks: 22
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:3
+ Output Columns:
0: DATE_ID (date)
1: HOUR_ID (short)
2: MIN_ID (short)
3: OS_ID (string)
4: ER_ID (string)
5: EuId (string)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 6762
1: Node Next Batch Process Time: 12
2: Node Cumulative Next Batch Process Time: 126
3: Node Cumulative Process Time: 4162
4: Node Next Batch Calls: 49
5: Node Blocks: 22
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:4
+ Output Columns:
0: HOUR_ID (short)
1: MIN_ID (short)
2: OS_ID (string)
3: ER_ID (string)
4: EuId (string)
5: DC_Re (string)
+ Statistics:
0: Node Output Rows: 6762
1: Node Next Batch Process Time: 14
2: Node Cumulative Next Batch Process Time: 110
3: Node Cumulative Process Time: 4162
4: Node Next Batch Calls: 71
5: Node Blocks: 44
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:5
+ Output Columns:
0: RoSt (string)
1: HOUR_ID (short)
2: MIN_ID (short)
3: OS_ID (string)
4: ER_ID (string)
5: EuId (string)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 6762
1: Node Next Batch Process Time: 22
2: Node Cumulative Next Batch Process Time: 92
3: Node Cumulative Process Time: 4161
4: Node Next Batch Calls: 115
5: Node Blocks: 88
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:6
+ Output Columns:
0: OS_ID (string)
1: ER (string)
2: SN (string)
3: RoSt (string)
4: HOUR_ID (short)
5: MIN_ID (short)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 6762
1: Node Next Batch Process Time: 35
2: Node Cumulative Next Batch Process Time: 63
3: Node Cumulative Process Time: 4161
4: Node Next Batch Calls: 203
5: Node Blocks: 176
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
UnionAllNode
+ Relational Node ID:7
+ Output Columns:
0: HOUR_ID (short)
1: MIN_ID (short)
2: OFFICE_TIME (short)
3: DAY_TIME (short)
4: NIGHT_TIME (short)
+ Statistics:
0: Node Output Rows: 2880
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 6
3: Node Cumulative Process Time: 354
4: Node Next Batch Calls: 57
5: Node Blocks: 55
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Relational Node ID:8
+ Output Columns:
0: HOUR_ID (short)
1: MIN_ID (short)
2: OFFICE_TIME (short)
3: DAY_TIME (short)
4: NIGHT_TIME (short)
+ Statistics:
0: Node Output Rows: 1440
1: Node Next Batch Process Time: 3
2: Node Cumulative Next Batch Process Time: 3
3: Node Cumulative Process Time: 354
4: Node Next Batch Calls: 57
5: Node Blocks: 56
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.HOUR_ID, g_0.MIN_ID, g_0.OFFICE_TIME, g_0.DAY_TIME, g_0.NIGHT_TIME FROM SrcModel_1.D_TIME AS g_0
+ Model Name:SrcModel_1
+ Child 1:
AccessNode
+ Relational Node ID:9
+ Output Columns:
0: HOUR_ID (short)
1: MIN_ID (short)
2: OFFICE_TIME (short)
3: DAY_TIME (short)
4: NIGHT_TIME (short)
+ Statistics:
0: Node Output Rows: 1440
1: Node Next Batch Process Time: 3
2: Node Cumulative Next Batch Process Time: 3
3: Node Cumulative Process Time: 40
4: Node Next Batch Calls: 16
5: Node Blocks: 15
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.HOUR_ID, g_0.MIN_ID, g_0.OFFICE_TIME, g_0.DAY_TIME, g_0.NIGHT_TIME FROM SrcModel_2.D_TIME AS g_0
+ Model Name:SrcModel_2
+ Child 1:
UnionAllNode
+ Relational Node ID:10
+ Output Columns:
0: HOUR_ID (short)
1: MIN_ID (short)
2: OS_ID (string)
3: ER (string)
4: SN (string)
5: RoSt (string)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 6762
1: Node Next Batch Process Time: 3
2: Node Cumulative Next Batch Process Time: 22
3: Node Cumulative Process Time: 4062
4: Node Next Batch Calls: 259
5: Node Blocks: 232
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Relational Node ID:11
+ Output Columns:
0: HOUR_ID (short)
1: MIN_ID (short)
2: OS_ID (string)
3: ER (string)
4: SN (string)
5: RoSt (string)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 4032
1: Node Next Batch Process Time: 14
2: Node Cumulative Next Batch Process Time: 14
3: Node Cumulative Process Time: 4062
4: Node Next Batch Calls: 259
5: Node Blocks: 243
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.HOUR_ID, g_0.MIN_ID, g_0.OS_ID, g_0.ER, g_0.SN, g_0.RoSt, g_0.DC_Re FROM SrcModel_1.E_ER_EU_V_RA AS g_0 WHERE (g_0.EUtrFD = 'ER-2') AND (g_0.RoSt NOT IN ('DUPL', 'SUSP')) AND (g_0.DATE_ID = {d'2017-11-21'})
+ Model Name:SrcModel_1
+ Child 1:
AccessNode
+ Relational Node ID:12
+ Output Columns:
0: HOUR_ID (short)
1: MIN_ID (short)
2: OS_ID (string)
3: ER (string)
4: SN (string)
5: RoSt (string)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 2730
1: Node Next Batch Process Time: 5
2: Node Cumulative Next Batch Process Time: 5
3: Node Cumulative Process Time: 959
4: Node Next Batch Calls: 171
5: Node Blocks: 160
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.HOUR_ID, g_0.MIN_ID, g_0.OS_ID, g_0.ER, g_0.SN, g_0.RoSt, g_0.DC_Re FROM SrcModel_2.E_ER_EU_V_RA AS g_0 WHERE (g_0.EUtrFD = 'ER-2') AND (g_0.RoSt NOT IN ('DUPL', 'SUSP')) AND (g_0.DATE_ID = {d'2017-11-21'})
+ Model Name:SrcModel_2
+ Join Strategy:ENHANCED SORT JOIN RAN AS SORT MERGE (SORT_DISTINCT/SORT)
+ Join Type:INNER JOIN
+ Join Criteria:
0: DC.D_TIME.HOUR_ID=DC.E_ER_EU_V_RA.HOUR_ID
1: DC.D_TIME.MIN_ID=DC.E_ER_EU_V_RA.MIN_ID
+ Child 1:
UnionAllNode
+ Relational Node ID:13
+ Output Columns:
0: noOfPCUsers (integer)
1: sPSI3 (integer)
2: OS_ID (string)
3: CE_ID (short)
4: EU_FD (string)
5: hoDUn (string)
6: maInSI15 (string)
7: sPSI9 (integer)
8: ta (integer)
9: sPSI10 (integer)
10: maInSI6 (string)
11: maInSI10 (string)
12: noOfPSUsers (integer)
13: CE_TY (string)
14: sPSI8 (integer)
15: sPSI2 (integer)
16: maInSI3 (string)
17: maInSI4 (string)
18: sPSI1 (integer)
19: eacn (integer)
20: pdCfMode (integer)
21: maInSI5 (string)
22: ea (integer)
23: ceRa (integer)
24: ER_ID (string)
25: sPSI5 (integer)
26: maInSi8 (string)
27: maInSI11 (string)
28: ulChBa (bigdecimal)
29: ENBFu (string)
30: eaul (integer)
31: maInSI12 (string)
32: usLa (string)
33: ER_FDN (string)
34: sPSI4 (integer)
35: sPSI7 (integer)
36: maInSI16 (string)
37: sPSI6 (integer)
38: EuId (string)
39: maInSi7 (string)
40: VE (string)
41: STA (string)
42: CRE (timestamp)
43: MODD (timestamp)
44: MODR (string)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 7
3: Node Cumulative Process Time: 1523
4: Node Next Batch Calls: 57
5: Node Blocks: 55
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Relational Node ID:14
+ Output Columns:
0: noOfPCUsers (integer)
1: sPSI3 (integer)
2: OS_ID (string)
3: CE_ID (short)
4: EU_FD (string)
5: hoDUn (string)
6: maInSI15 (string)
7: sPSI9 (integer)
8: ta (integer)
9: sPSI10 (integer)
10: maInSI6 (string)
11: maInSI10 (string)
12: noOfPSUsers (integer)
13: CE_TY (string)
14: sPSI8 (integer)
15: sPSI2 (integer)
16: maInSI3 (string)
17: maInSI4 (string)
18: sPSI1 (integer)
19: eacn (integer)
20: pdCfMode (integer)
21: maInSI5 (string)
22: ea (integer)
23: ceRa (integer)
24: ER_ID (string)
25: sPSI5 (integer)
26: maInSi8 (string)
27: maInSI11 (string)
28: ulChBa (bigdecimal)
29: ENBFu (string)
30: eaul (integer)
31: maInSI12 (string)
32: usLa (string)
33: ER_FDN (string)
34: sPSI4 (integer)
35: sPSI7 (integer)
36: maInSI16 (string)
37: sPSI6 (integer)
38: EuId (string)
39: maInSi7 (string)
40: VE (string)
41: STA (string)
42: CRE (timestamp)
43: MODD (timestamp)
44: MODR (string)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 4
2: Node Cumulative Next Batch Process Time: 4
3: Node Cumulative Process Time: 1523
4: Node Next Batch Calls: 58
5: Node Blocks: 57
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.noOfPCUsers, g_0.sPSI3, g_0.OS_ID, g_0.CE_ID, g_0.EU_FD, g_0.hoDUn, g_0.maInSI15, g_0.sPSI9, g_0.ta, g_0.sPSI10, g_0.maInSI6, g_0.maInSI10, g_0.noOfPSUsers, g_0.CE_TY, g_0.sPSI8, g_0.sPSI2, g_0.maInSI3, g_0.maInSI4, g_0.sPSI1, g_0.eacn, g_0.pdCfMode, g_0.maInSI5, g_0.ea, g_0.ceRa, g_0.ER_ID, g_0.sPSI5, g_0.maInSi8, g_0.maInSI11, g_0.ulChBa, g_0.ENBFu, g_0.eaul, g_0.maInSI12, g_0.usLa, g_0.ER_FDN, g_0.sPSI4, g_0.sPSI7, g_0.maInSI16, g_0.sPSI6, g_0.EuId, g_0.maInSi7, g_0.VE, g_0.STA, g_0.CRE, g_0.MODD, g_0.MODR FROM SrcModel_1.D_E_L_EUC_CE AS g_0 WHERE g_0.EuId = 'ER-2'
+ Model Name:SrcModel_1
+ Child 1:
AccessNode
+ Relational Node ID:15
+ Output Columns:
0: noOfPCUsers (integer)
1: sPSI3 (integer)
2: OS_ID (string)
3: CE_ID (short)
4: EU_FD (string)
5: hoDUn (string)
6: maInSI15 (string)
7: sPSI9 (integer)
8: ta (integer)
9: sPSI10 (integer)
10: maInSI6 (string)
11: maInSI10 (string)
12: noOfPSUsers (integer)
13: CE_TY (string)
14: sPSI8 (integer)
15: sPSI2 (integer)
16: maInSI3 (string)
17: maInSI4 (string)
18: sPSI1 (integer)
19: eacn (integer)
20: pdCfMode (integer)
21: maInSI5 (string)
22: ea (integer)
23: ceRa (integer)
24: ER_ID (string)
25: sPSI5 (integer)
26: maInSi8 (string)
27: maInSI11 (string)
28: ulChBa (bigdecimal)
29: ENBFu (string)
30: eaul (integer)
31: maInSI12 (string)
32: usLa (string)
33: ER_FDN (string)
34: sPSI4 (integer)
35: sPSI7 (integer)
36: maInSI16 (string)
37: sPSI6 (integer)
38: EuId (string)
39: maInSi7 (string)
40: VE (string)
41: STA (string)
42: CRE (timestamp)
43: MODD (timestamp)
44: MODR (string)
+ Statistics:
0: Node Output Rows: 0
1: Node Next Batch Process Time: 3
2: Node Cumulative Next Batch Process Time: 3
3: Node Cumulative Process Time: 60
4: Node Next Batch Calls: 17
5: Node Blocks: 16
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.noOfPCUsers, g_0.sPSI3, g_0.OS_ID, g_0.CE_ID, g_0.EU_FD, g_0.hoDUn, g_0.maInSI15, g_0.sPSI9, g_0.ta, g_0.sPSI10, g_0.maInSI6, g_0.maInSI10, g_0.noOfPSUsers, g_0.CE_TY, g_0.sPSI8, g_0.sPSI2, g_0.maInSI3, g_0.maInSI4, g_0.sPSI1, g_0.eacn, g_0.pdCfMode, g_0.maInSI5, g_0.ea, g_0.ceRa, g_0.ER_ID, g_0.sPSI5, g_0.maInSi8, g_0.maInSI11, g_0.ulChBa, g_0.ENBFu, g_0.eaul, g_0.maInSI12, g_0.usLa, g_0.ER_FDN, g_0.sPSI4, g_0.sPSI7, g_0.maInSI16, g_0.sPSI6, g_0.EuId, g_0.maInSi7, g_0.VE, g_0.STA, g_0.CRE, g_0.MODD, g_0.MODR FROM SrcModel_2.D_E_L_EUC_CE AS g_0 WHERE g_0.EuId = 'ER-2'
+ Model Name:SrcModel_2
+ Join Strategy:ENHANCED SORT JOIN (SORT/SORT_DISTINCT)
+ Join Type:INNER JOIN
+ Join Criteria:
0: DC.E_ER_EU_V_RA.OS_ID=DC.D_E_L_EUC_CE.OS_ID
1: DC.E_ER_EU_V_RA.ER=DC.D_E_L_EUC_CE.ER_ID
2: DC.E_ER_EU_V_RA.SN=DC.D_E_L_EUC_CE.ER_FDN
+ Child 1:
UnionAllNode
+ Relational Node ID:16
+ Output Columns:
0: RoSt (string)
1: DES (string)
+ Statistics:
0: Node Output Rows: 38
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 4
3: Node Cumulative Process Time: 161
4: Node Next Batch Calls: 11
5: Node Blocks: 9
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Relational Node ID:17
+ Output Columns:
0: RoSt (string)
1: DES (string)
+ Statistics:
0: Node Output Rows: 19
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 161
4: Node Next Batch Calls: 11
5: Node Blocks: 10
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.RoSt, g_0.DES FROM SrcModel_1.D_RoSt AS g_0 WHERE g_0.RoSt NOT IN ('DUPL', 'SUSP')
+ Model Name:SrcModel_1
+ Child 1:
AccessNode
+ Relational Node ID:18
+ Output Columns:
0: RoSt (string)
1: DES (string)
+ Statistics:
0: Node Output Rows: 19
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 42
4: Node Next Batch Calls: 3
5: Node Blocks: 2
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.RoSt, g_0.DES FROM SrcModel_2.D_RoSt AS g_0 WHERE g_0.RoSt NOT IN ('DUPL', 'SUSP')
+ Model Name:SrcModel_2
+ Join Strategy:ENHANCED SORT JOIN (SORT/SORT_DISTINCT)
+ Join Type:INNER JOIN
+ Join Criteria:DC.E_ER_EU_V_RA.RoSt=DC.D_RoSt.RoSt
+ Child 1:
SortNode
+ Relational Node ID:20
+ Output Columns:
0: DATE_ID (date)
1: YEAR_ID (short)
2: MONTH_ID (short)
3: DAY_ID (short)
4: WEEK_ID (short)
5: WEEKDAY_ID (short)
6: BUSINESSDAY (short)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 4
3: Node Cumulative Process Time: 367
4: Node Next Batch Calls: 8
5: Node Blocks: 7
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
UnionAllNode
+ Relational Node ID:19
+ Output Columns:
0: DATE_ID (date)
1: YEAR_ID (short)
2: MONTH_ID (short)
3: DAY_ID (short)
4: WEEK_ID (short)
5: WEEKDAY_ID (short)
6: BUSINESSDAY (short)
+ Statistics:
0: Node Output Rows: 2
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 4
3: Node Cumulative Process Time: 367
4: Node Next Batch Calls: 9
5: Node Blocks: 7
+ Child 0:
AccessNode
+ Relational Node ID:21
+ Output Columns:
0: DATE_ID (date)
1: YEAR_ID (short)
2: MONTH_ID (short)
3: DAY_ID (short)
4: WEEK_ID (short)
5: WEEKDAY_ID (short)
6: BUSINESSDAY (short)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 367
4: Node Next Batch Calls: 9
5: Node Blocks: 8
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.DATE_ID, g_0.YEAR_ID, g_0.MONTH_ID, g_0.DAY_ID, g_0.WEEK_ID, g_0.WEEKDAY_ID, g_0.BUSINESSDAY FROM SrcModel_1.D_DATE AS g_0 WHERE g_0.DATE_ID = {d'2017-11-21'}
+ Model Name:SrcModel_1
+ Child 1:
AccessNode
+ Relational Node ID:22
+ Output Columns:
0: DATE_ID (date)
1: YEAR_ID (short)
2: MONTH_ID (short)
3: DAY_ID (short)
4: WEEK_ID (short)
5: WEEKDAY_ID (short)
6: BUSINESSDAY (short)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 47
4: Node Next Batch Calls: 4
5: Node Blocks: 3
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.DATE_ID, g_0.YEAR_ID, g_0.MONTH_ID, g_0.DAY_ID, g_0.WEEK_ID, g_0.WEEKDAY_ID, g_0.BUSINESSDAY FROM SrcModel_2.D_DATE AS g_0 WHERE g_0.DATE_ID = {d'2017-11-21'}
+ Model Name:SrcModel_2
+ Sort Mode:DUP_REMOVE_SORT
+ Join Strategy:NESTED LOOP JOIN
+ Join Type:CROSS JOIN
+ Join Criteria
+ Grouping Columns:
0: DC.D_DATE.DATE_ID
1: DC.D_TIME.HOUR_ID
2: DC.D_TIME.MIN_ID
3: DC.D_E_L_EUC_CE.OS_ID
4: DC.D_E_L_EUC_CE.ER_ID
5: DC.D_E_L_EUC_CE.EuId
6: DC.E_ER_EU_V_RA.DC_Re
+ Grouping Mapping:
0: anon_grp0.gcol0=DC.D_DATE.DATE_ID
1: anon_grp0.gcol1=DC.D_TIME.HOUR_ID
2: anon_grp0.gcol2=DC.D_TIME.MIN_ID
3: anon_grp0.gcol3=DC.D_E_L_EUC_CE.OS_ID
4: anon_grp0.gcol4=DC.D_E_L_EUC_CE.ER_ID
5: anon_grp0.gcol5=DC.D_E_L_EUC_CE.EuId
6: anon_grp0.gcol6=DC.E_ER_EU_V_RA.DC_Re
+ Sort Mode:false
+ Select Columns:
0: anon_grp0.gcol0 AS DATE_ID
1: anon_grp0.gcol1 AS HOUR_ID
2: anon_grp0.gcol2 AS MIN_ID
3: anon_grp0.gcol3 AS OS_ID
4: anon_grp0.gcol4 AS ER_ID
5: anon_grp0.gcol5 AS EuId
6: anon_grp0.gcol6 AS DC_Re
+ Data Bytes Sent:5633
+ Planning Time:39
-----------------------------------
====================================================================
Query Plan from A_VDB
====================================================================
AccessNode
+ Relational Node ID:1
+ Output Columns:
0: DATE_ID (date)
1: HOUR_ID (short)
2: MIN_ID (short)
3: OS_ID (string)
4: ER_ID (string)
5: EuId (string)
6: DC_Re (string)
+ Statistics:
0: Node Output Rows: 96
1: Node Next Batch Process Time: 10
2: Node Cumulative Next Batch Process Time: 10
3: Node Cumulative Process Time: 4975
4: Node Next Batch Calls: 2
5: Node Blocks: 1
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.DATE_ID, g_1.HOUR_ID, g_1.MIN_ID, g_2.OS_ID, g_2.ER_ID, g_2.EuId, g_3.DC_Re FROM SrcModel_2.D_DATE AS g_0, SrcModel_2.D_TIME AS g_1, SrcModel_2.D_E_L_EUC_CE AS g_2, SrcModel_2.E_ER_EU_V_RA AS g_3, SrcModel_2.D_RoSt AS g_4 WHERE (g_3.RoSt = g_4.RoSt) AND (g_3.OS_ID = g_2.OS_ID) AND (g_3.ER = g_2.ER_ID) AND (g_3.SN = g_2.ER_FDN) AND (g_3.EUtrFD = g_2.EuId) AND (g_3.HOUR_ID = g_1.HOUR_ID) AND (g_3.MIN_ID = g_1.MIN_ID) AND (g_3.DATE_ID = g_0.DATE_ID) AND (g_0.DATE_ID = {d'2017-11-21'}) AND (g_2.EuId = 'ER-2') AND (g_3.EUtrFD = 'ER-2') AND (g_3.DATE_ID = {d'2017-11-21'}) AND (g_3.RoSt NOT IN ('DUPL', 'SUSP')) AND (g_4.RoSt NOT IN ('DUPL', 'SUSP')) GROUP BY g_0.DATE_ID, g_1.HOUR_ID, g_1.MIN_ID, g_2.OS_ID, g_2.ER_ID, g_2.EuId, g_3.DC_Re
+ Model Name:SrcModel_2
+ Data Bytes Sent:3415
+ Planning Time:15
-----------------------------------
-
3. Re: Same query returning different Output Rows through VDB
shawkins Dec 22, 2017 9:04 PM (in response to adi22)This makes it a little clearer. So effectively the first plan is from the the same logical user query, but each source access is effectively replaced by a union operation accessing both sources.
In the first plan there is a source query "SELECT g_0.noOfPCUsers, g_0.sPSI3, g_0.OS_ID, g_0.CE_ID, g_0.EU_FD, g_0.hoDUn, g_0.maInSI15, g_0.sPSI9, g_0.ta, g_0.sPSI10, g_0.maInSI6, g_0.maInSI10, g_0.noOfPSUsers, g_0.CE_TY, g_0.sPSI8, g_0.sPSI2, g_0.maInSI3, g_0.maInSI4, g_0.sPSI1, g_0.eacn, g_0.pdCfMode, g_0.maInSI5, g_0.ea, g_0.ceRa, g_0.ER_ID, g_0.sPSI5, g_0.maInSi8, g_0.maInSI11, g_0.ulChBa, g_0.ENBFu, g_0.eaul, g_0.maInSI12, g_0.usLa, g_0.ER_FDN, g_0.sPSI4, g_0.sPSI7, g_0.maInSI16, g_0.sPSI6, g_0.EuId, g_0.maInSi7, g_0.VE, g_0.STA, g_0.CRE, g_0.MODD, g_0.MODR FROM SrcModel_2.D_E_L_EUC_CE AS g_0 WHERE g_0.EuId = 'ER-2'" which is returning 0 rows.
That should explain why 0 rows are returned from B_VDB. However that is being unioned with 1 row from the other source such that if values of DC.D_E_L_EUC_CE.OS_ID, DC.D_E_L_EUC_CE.ER_ID, and DC.D_E_L_EUC_CE.ER_FDN are shared across the sources, then it's likely that you end up creating additional rows in your result.
The assumption that this union query should produce the same results as the individual source queries is true only if you are effectively partitioned on one of the grouping columns, and you have a situation that is a grouping directly over a union. In situations like these if Teiid were aware of this partitioning, via the multi-source feature or a partitioned view, it can create a better plan.