3 Replies Latest reply on Dec 22, 2017 9:04 PM by shawkins

    Same query returning different Output Rows through VDB

    adi22

      I have created dynamic VDB with 2 source models and 1 view model.

      The expectation is to get a sum of records from Source 1 and Source 2 when we run a query through VDB.

      But in this case, I am getting more number of rows when the query is run through VDB.

      Individually when I am running the query on Source models, row count is correct and as expected.

       

      The query included sum, where clauses and group by.

      I have done initial analysis and come across some mismatch.

        • 1. Re: Same query returning different Output Rows through VDB
          shawkins

          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

            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

              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.