8 Replies Latest reply on Mar 6, 2014 10:48 AM by Steven Hawkins

    Question about joining temp tables

    gadeyne.bram Master

      Hi,

       

      I've created some temporary tables containing some id's (some hundreds).

       

      I'd like to use these id's in a query to execute a query on a table that contains millions of rows and only some thousands are for the id's in the temporary table.

       

      eg.

       

      select *

      from somelargetable slt

      join #tmp_table t on t.id = slt.id

       

      Sometimes I notice that the planner makes a query using an in statement. eg:

       

      select * from somelargetable slt

      where slt.id in ( the selected ids from the temporary table )

       

      Other times the planner just executes the query against the whole table (which takes a lot of time) and then afterwards filters the ids.

       

      Is there a way to tell the planner to creates such a IN statement (like /*+ MAKEDEP */? Below is my SHOWPLAN text.

       

       

      query:

      select

          dv.VariableID, dv.value as varvalue, dv.PatientID, dv.Datetime, rank() over(partition by dv.PatientID, dv.VariableID order by dv.Datetime) as varnr

          from #tmp_admissions adm

          join /*+ MAKEDEP */ tableXX dv on dv.patientid = adm.admissionid

          where dv.VariableID in (100

                              ,200) 

       

       

      LimitNode

        + Output Columns:

          0: VariableID (integer)

          1: varvalue (double)

          2: PatientID (integer)

          3: Datetime (timestamp)

          4: varnr (integer)

        + Statistics:

          0: Node Output Rows: 42

          1: Node Next Batch Process Time: 26

          2: Node Cumulative Next Batch Process Time: 4

          3: Node Cumulative Process Time: 30

          4: Node Next Batch Calls: 2

          5: Node Blocks: 1

        + Cost Estimates:Estimated Node Cardinality: 100.0

        + Child 0:

          ProjectNode

            + Output Columns:

              0: VariableID (integer)

              1: varvalue (double)

              2: PatientID (integer)

              3: Datetime (timestamp)

              4: varnr (integer)

            + Statistics:

              0: Node Output Rows: 42

              1: Node Next Batch Process Time: 26

              2: Node Cumulative Next Batch Process Time: 4

              3: Node Cumulative Process Time: 30

              4: Node Next Batch Calls: 2

              5: Node Blocks: 1

            + Cost Estimates:Estimated Node Cardinality: -1.0

            + Child 0:

              WindowFunctionProjectNode

                + Output Columns:

                  0: expr (integer)

                  1: VariableID (integer)

                  2: Value (double)

                  3: PatientID (integer)

                  4: Datetime (timestamp)

                + Statistics:

                  0: Node Output Rows: 42

                  1: Node Next Batch Process Time: 26

                  2: Node Cumulative Next Batch Process Time: 4

                  3: Node Cumulative Process Time: 30

                  4: Node Next Batch Calls: 2

                  5: Node Blocks: 1

                + Child 0:

                  JoinNode

                    + Output Columns:

                      0: VariableID (integer)

                      1: Value (double)

                      2: PatientID (integer)

                      3: Datetime (timestamp)

                    + Statistics:

                      0: Node Output Rows: 42

                      1: Node Next Batch Process Time: 27

                      2: Node Cumulative Next Batch Process Time: 4

                      3: Node Cumulative Process Time: 30

                      4: Node Next Batch Calls: 2

                      5: Node Blocks: 1

                    + Cost Estimates:Estimated Node Cardinality: -1.0

                    + Child 0:

                      AccessNode

                        + Output Columns:admissionid (integer)

                        + Statistics:

                          0: Node Output Rows: 3543

                          1: Node Next Batch Process Time: 29

                          2: Node Cumulative Next Batch Process Time: 2

                          3: Node Cumulative Process Time: 29

                          4: Node Next Batch Calls: 2

                          5: Node Blocks: 0

                        + Cost Estimates:Estimated Node Cardinality: 3543.0

                        + Query:SELECT #tmp_admissions.admissionid FROM #tmp_admissions ORDER BY #tmp_admissions.admissionid

                        + Model Name:__TEMP__

                    + Child 1:

                      AccessNode

                        + Output Columns:

                          0: PatientID (integer)

                          1: VariableID (integer)

                          2: Value (double)

                          3: Datetime (timestamp)

                        + Statistics:

                          0: Node Output Rows: 1690

                          1: Node Next Batch Process Time: 27

                          2: Node Cumulative Next Batch Process Time: 1

                          3: Node Cumulative Process Time: 27

                          4: Node Next Batch Calls: 3

                          5: Node Blocks: 2

                        + Cost Estimates:Estimated Node Cardinality: -1.0

                        + Query:SELECT g_0.PatientID AS c_0, g_0.VariableID AS c_1, g_0."Value" AS c_2, g_0.Datetime AS c_3 FROM tableXX AS g_0 WHERE g_0.VariableID IN (100, 200) ORDER BY c_0

                        + Model Name:izisprod

                    + Join Strategy:MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)

                    + Join Type:INNER JOIN

                    + Join Criteria:adm.admissionid=dv.PatientID

            + Select Columns:

              0: dv.VariableID

              1: dv."Value" AS varvalue

              2: dv.PatientID

              3: dv.Datetime

              4: RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr

        + Row Offset:null

        + Row Limit:100

        • 1. Re: Question about joining temp tables
          Steven Hawkins Master

          The planner is unsure what to do as the size of the temporary table grows and the size of tableXX is unknown.  We have fairly conservative defaults (which probably do need to be adjusted).  It would help if there were a cardinality associated with tableXX.

           

          As for the makedep hint, that appears to be proper usage.  Can you provide the full plan debug log?  That will show whether the hint was picked up correctly.

           

          Steve

          1 of 1 people found this helpful
          • 2. Re: Question about joining temp tables
            gadeyne.bram Master

            This was the original query:

             

            select  dv.VariableID, dv.value as varvalue, dv.PatientID, dv.Datetime, rank() over(partition by dv.PatientID, dv.VariableID order by dv.Datetime) as varnr

            from #tmp_admissions adm

            join /*+ MAKEDEP */ tableXX dv on dv.patientid = adm.admissionid

            where dv.VariableID in (100 ,200) 

             

            The temp table contains 3543 rows.

             

             

            Here's the content of the DEBUG_LOG

            ============================================================================

            USER COMMAND:

            SELECT dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr FROM #tmp_admissions AS adm INNER JOIN tableXX AS dv ON dv.PatientID = adm.admissionid WHERE dv.VariableID IN (100, 200) LIMIT 100

             

            ----------------------------------------------------------------------------

            OPTIMIZE:

            SELECT dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr FROM #tmp_admissions AS adm INNER JOIN tableXX AS dv ON dv.PatientID = adm.admissionid WHERE dv.VariableID IN (100, 200) LIMIT 100

             

            ----------------------------------------------------------------------------

            GENERATE CANONICAL:

            SELECT dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr FROM #tmp_admissions AS adm INNER JOIN tableXX AS dv ON dv.PatientID = adm.admissionid WHERE dv.VariableID IN (100, 200) LIMIT 100

             

            CANONICAL PLAN:

            TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true})

                Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200)})

                  Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid]})

                    Source(groups=[#tmp_admissions AS adm])

                    Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING PlaceAccess

             

            AFTER:

            TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true})

                Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200)})

                  Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid]})

                    Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__})

                      Source(groups=[#tmp_admissions AS adm])

                    Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca})

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING PushSelectCriteria

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true})

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid]})

                  Access(groups=[#tmp_admissions AS adm])

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca})

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200)})

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING PushNonJoinCriteria

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid]})

                  Access(groups=[#tmp_admissions AS adm])

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv])

                    Select(groups=[tableXX AS dv])

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING CleanCriteria

             

            AFTER:

            TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=null})

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], OUTPUT_COLS=null})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=null})

                    Source(groups=[#tmp_admissions AS adm], props={OUTPUT_COLS=null})

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=null})

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=null})

                      Source(groups=[tableXX AS dv], props={OUTPUT_COLS=null})

             

             

            ============================================================================

            EXECUTING RaiseAccess

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], OUTPUT_COLS=null})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=null})

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=null})

                    Select(groups=[tableXX AS dv])

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING CopyCriteria

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], OUTPUT_COLS=null, IS_COPIED=true})

                  Access(groups=[#tmp_admissions AS adm])

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv])

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=null, IS_COPIED=true})

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING CleanCriteria

             

            AFTER:

            TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=null})

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], OUTPUT_COLS=null, IS_COPIED=true})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=null})

                    Source(groups=[#tmp_admissions AS adm], props={OUTPUT_COLS=null})

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=null})

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=null, IS_COPIED=true})

                      Source(groups=[tableXX AS dv], props={OUTPUT_COLS=null})

             

             

            ============================================================================

            EXECUTING PlanJoins

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=null})

                Select(groups=[tableXX AS dv, #tmp_admissions AS adm], props={SELECT_CRITERIA=dv.PatientID = adm.admissionid, EST_SELECTIVITY=0.12577777})

                  Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP})

                    Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=null, EST_CARDINALITY=3543.0, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}})

                      Source(groups=[#tmp_admissions AS adm], props={OUTPUT_COLS=null, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}, EST_CARDINALITY=3543.0})

                    Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})

                      Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=-1.0})

                        Source(groups=[tableXX AS dv], props={OUTPUT_COLS=null, EST_COL_STATS={dv.Datetime=[-1.0, -1.0], dv.EnterTime=[-1.0, -1.0], dv.PatientID=[-1.0, -1.0], dv.Status=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})

             

             

            ============================================================================

            EXECUTING PushSelectCriteria

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=null})

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid]})

                  Access(groups=[#tmp_admissions AS adm])

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv])

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=-1.0})

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING RaiseAccess

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[dv.PatientID = adm.admissionid]})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=null, EST_CARDINALITY=3543.0, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}})

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})

                    Select(groups=[tableXX AS dv])

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING ChooseJoinStrategy

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], LEFT_EXPRESSIONS=[adm.admissionid], RIGHT_EXPRESSIONS=[dv.PatientID], NON_EQUI_JOIN_CRITERIA=[]})

                  Access(groups=[#tmp_admissions AS adm])

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv])

                    Select(groups=[tableXX AS dv])

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING ChooseDependent

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], LEFT_EXPRESSIONS=[adm.admissionid], RIGHT_EXPRESSIONS=[dv.PatientID], NON_EQUI_JOIN_CRITERIA=[]})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=null, EST_CARDINALITY=3543.0, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}})

                    Source(groups=[#tmp_admissions AS adm], props={OUTPUT_COLS=null, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}, EST_CARDINALITY=3543.0})

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=null, IS_COPIED=true, EST_CARDINALITY=-1.0})

                      Source(groups=[tableXX AS dv], props={OUTPUT_COLS=null, EST_COL_STATS={dv.Datetime=[-1.0, -1.0], dv.EnterTime=[-1.0, -1.0], dv.PatientID=[-1.0, -1.0], dv.Status=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})

             

             

            ============================================================================

            EXECUTING PushLimit

             

            AFTER:

            TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=null})

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv])

                  Access(groups=[#tmp_admissions AS adm])

                    Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv])

                    Select(groups=[tableXX AS dv])

                      Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING AssignOutputElements

             

            AFTER:

            TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr]})

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr]})

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], LEFT_EXPRESSIONS=[adm.admissionid], RIGHT_EXPRESSIONS=[dv.PatientID], NON_EQUI_JOIN_CRITERIA=[], OUTPUT_COLS=[dv.VariableID, dv."Value", dv.PatientID, dv.Datetime]})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[adm.admissionid], EST_CARDINALITY=3543.0, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}})

                    Source(groups=[#tmp_admissions AS adm], props={OUTPUT_COLS=[adm.admissionid], EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}, EST_CARDINALITY=3543.0})

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime], EST_CARDINALITY=-1.0})

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime], IS_COPIED=true, EST_CARDINALITY=-1.0})

                      Source(groups=[tableXX AS dv], props={OUTPUT_COLS=[dv.VariableID, dv.PatientID, dv."Value", dv.Datetime], EST_COL_STATS={dv.Datetime=[-1.0, -1.0], dv.EnterTime=[-1.0, -1.0], dv.PatientID=[-1.0, -1.0], dv.Status=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})

             

             

            ============================================================================

            EXECUTING CalculateCost

             

            AFTER:

            TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], EST_CARDINALITY=100.0, EST_COL_STATS={dv.VariableID=[100.0, 0.0], dv."Value" AS varvalue=[100.0, 0.0], dv.PatientID=[100.0, 0.0], dv.Datetime=[100.0, 0.0], ...}})

              Project(groups=[tableXX AS dv], props={PROJECT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], HAS_WINDOW_FUNCTIONS=true, OUTPUT_COLS=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr], EST_CARDINALITY=-1.0})

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], LEFT_EXPRESSIONS=[adm.admissionid], RIGHT_EXPRESSIONS=[dv.PatientID], NON_EQUI_JOIN_CRITERIA=[], OUTPUT_COLS=[dv.VariableID, dv."Value", dv.PatientID, dv.Datetime], EST_CARDINALITY=-1.0})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[adm.admissionid], EST_CARDINALITY=3543.0, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}})

                    Source(groups=[#tmp_admissions AS adm], props={OUTPUT_COLS=[adm.admissionid], EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}, EST_CARDINALITY=3543.0})

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime], EST_CARDINALITY=-1.0})

                    Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime], IS_COPIED=true, EST_CARDINALITY=-1.0})

                      Source(groups=[tableXX AS dv], props={OUTPUT_COLS=[dv.VariableID, dv.PatientID, dv."Value", dv.Datetime], EST_COL_STATS={dv.Datetime=[-1.0, -1.0], dv.EnterTime=[-1.0, -1.0], dv.PatientID=[-1.0, -1.0], dv.Status=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})

             

             

            ============================================================================

            EXECUTING ImplementJoinStrategy

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], LEFT_EXPRESSIONS=[adm.admissionid], RIGHT_EXPRESSIONS=[dv.PatientID], NON_EQUI_JOIN_CRITERIA=[], OUTPUT_COLS=[dv.VariableID, dv."Value", dv.PatientID, dv.Datetime], EST_CARDINALITY=-1.0})

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[adm.admissionid], EST_CARDINALITY=3543.0, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}})

                    Sort(groups=[], props={SORT_ORDER=ORDER BY adm.admissionid, OUTPUT_COLS=[adm.admissionid]})

                      Source(groups=[#tmp_admissions AS adm], props={OUTPUT_COLS=[adm.admissionid], EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}, EST_CARDINALITY=3543.0})

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime], EST_CARDINALITY=-1.0})

                    Sort(groups=[], props={SORT_ORDER=ORDER BY dv.PatientID, OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime]})

                      Select(groups=[tableXX AS dv], props={SELECT_CRITERIA=dv.VariableID IN (100, 200), OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime], IS_COPIED=true, EST_CARDINALITY=-1.0})

                        Source(groups=[tableXX AS dv], props={OUTPUT_COLS=[dv.VariableID, dv.PatientID, dv."Value", dv.Datetime], EST_COL_STATS={dv.Datetime=[-1.0, -1.0], dv.EnterTime=[-1.0, -1.0], dv.PatientID=[-1.0, -1.0], dv.Status=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})

             

             

            ============================================================================

            EXECUTING MergeCriteria

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv])

                  Access(groups=[#tmp_admissions AS adm])

                    Sort(groups=[])

                      Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv])

                    Sort(groups=[])

                      Select(groups=[tableXX AS dv])

                        Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING PlanSorts

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[dv.PatientID = adm.admissionid], LEFT_EXPRESSIONS=[adm.admissionid], RIGHT_EXPRESSIONS=[dv.PatientID], NON_EQUI_JOIN_CRITERIA=[], OUTPUT_COLS=[dv.VariableID, dv."Value", dv.PatientID, dv.Datetime], EST_CARDINALITY=-1.0})

                  Access(groups=[#tmp_admissions AS adm])

                    Sort(groups=[])

                      Source(groups=[#tmp_admissions AS adm])

                  Access(groups=[tableXX AS dv])

                    Sort(groups=[])

                      Select(groups=[tableXX AS dv])

                        Source(groups=[tableXX AS dv])

             

             

            ============================================================================

            EXECUTING CollapseSource

             

            AFTER:

            TupleLimit(groups=[])

              Project(groups=[tableXX AS dv])

                Join(groups=[#tmp_admissions AS adm, tableXX AS dv])

                  Access(groups=[#tmp_admissions AS adm], props={SOURCE_HINT=null, MODEL_ID=__TEMP__, OUTPUT_COLS=[adm.admissionid], EST_CARDINALITY=3543.0, EST_COL_STATS={adm.admissionid=[-1.0, -1.0]}, ATOMIC_REQUEST=SELECT adm.admissionid FROM #tmp_admissions AS adm ORDER BY adm.admissionid})

                  Access(groups=[tableXX AS dv], props={SOURCE_HINT=null, MODEL_ID=Schema name=izisprod, nameInSource=null, uuid=mmuuid:b363a5ba-79cc-4fd3-b987-6cab174224ca, OUTPUT_COLS=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=SELECT dv.PatientID, dv.VariableID, dv."Value", dv.Datetime FROM tableXX AS dv WHERE dv.VariableID IN (100, 200) ORDER BY dv.PatientID})

             

             

            ============================================================================

            CONVERTING PLAN TREE TO PROCESS TREE

             

            PROCESS PLAN =

            LimitNode(0) output=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr] limit 100

              ProjectNode(1) output=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr] [dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr]

                WindowFunctionProjectNode(2) output=[RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime), dv.VariableID, dv."Value", dv.PatientID, dv.Datetime]

                  JoinNode(3) [MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)] [INNER JOIN] criteria=[adm.admissionid=dv.PatientID] output=[dv.VariableID, dv."Value", dv.PatientID, dv.Datetime]

                    AccessNode(4) output=[adm.admissionid] SELECT #tmp_admissions.admissionid FROM #tmp_admissions ORDER BY #tmp_admissions.admissionid

                    AccessNode(5) output=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime] SELECT g_0.PatientID AS c_0, g_0.VariableID AS c_1, g_0."Value" AS c_2, g_0.Datetime AS c_3 FROM tableXX AS g_0 WHERE g_0.VariableID IN (100, 200) ORDER BY c_0

             

            ============================================================================

             

            ----------------------------------------------------------------------------

            OPTIMIZATION COMPLETE:

            PROCESSOR PLAN:

            LimitNode(0) output=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr] limit 100

              ProjectNode(1) output=[dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr] [dv.VariableID, dv."Value" AS varvalue, dv.PatientID, dv.Datetime, RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime) AS varnr]

                WindowFunctionProjectNode(2) output=[RANK() OVER (PARTITION BY dv.PatientID, dv.VariableID ORDER BY dv.Datetime), dv.VariableID, dv."Value", dv.PatientID, dv.Datetime]

                  JoinNode(3) [MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)] [INNER JOIN] criteria=[adm.admissionid=dv.PatientID] output=[dv.VariableID, dv."Value", dv.PatientID, dv.Datetime]

                    AccessNode(4) output=[adm.admissionid] SELECT #tmp_admissions.admissionid FROM #tmp_admissions ORDER BY #tmp_admissions.admissionid

                    AccessNode(5) output=[dv.PatientID, dv.VariableID, dv."Value", dv.Datetime] SELECT g_0.PatientID AS c_0, g_0.VariableID AS c_1, g_0."Value" AS c_2, g_0.Datetime AS c_3 FROM tableXX AS g_0 WHERE g_0.VariableID IN (100, 200) ORDER BY c_0

             

            ============================================================================

            • 3. Re: Question about joining temp tables
              Steven Hawkins Master

              From the start of the query plan the MAKEDEP hint is not present.  Can you turn up the logging to debug, or use the command log, and see what the server reports as the raw user query?

               

              Steve

              • 4. Re: Question about joining temp tables
                gadeyne.bram Master

                Hi Steven,

                 

                This is what the server reports on the COMMAND_LOG. The MAKEDEP hint is also not present.

                 

                Well it's a little different now since I have replaced the temporary table with a real table but the result is just the same.

                 

                10:24:52,200 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #3) o9S1/iUpRHuy     START USER COMMAND:     startTime=2014-02-27 10:24:52.2 requestID=o9S1/iUpRHuy.8     txID=null       sessionID=o9S1/iUpRHuy  applicationName=JDBC    principal=icuaqinf@teiid-security       vdbName=icuaqinf        vdbVersion=1    sql=select
                        dv.VariableID, dv.value as varvalue, dv.PatientID, dv.Datetime, rank() over(partition by dv.PatientID, dv.VariableID order by dv.Datetime) as varnr
                        from icuaqinf_admissions adm
                        join  tableXX dv on dv.patientid = adm.admissionid
                        where dv.VariableID in (100                                                ,200)      and dv.Datetime >= PARSEDATE('2009-12-30','yyyy-MM-dd')

                 

                 

                I've also changed the query by removing the HINT and adding it as an option clause but now it fails again because apparently the maximum number of IN parameters is more then 2000.

                 

                 

                select

                    dv.VariableID, dv.value as varvalue, dv.PatientID, dv.Datetime, rank() over(partition by dv.PatientID, dv.VariableID order by dv.Datetime) as varnr

                    from icuaqinf_admissions adm

                    join tableXX dv on dv.patientid = adm.admissionid

                    where dv.VariableID in (100,200)

                    and dv.Datetime >= PARSEDATE('2009-12-30','yyyy-MM-dd')

                    OPTION MAKEDEP wh_P_DerVals

                 

                Originally TeiidProcessingException 'Prepared or callable statement has more than 2000 parameter markers.' SQLParser.java:1254. Enable more detailed logging to see the entire stacktrace.

                • 5. Re: Question about joining temp tables
                  Steven Hawkins Master

                  > This is what the server reports on the COMMAND_LOG. The MAKEDEP hint is also not present.

                   

                  What is your client environment?  It may be stripping the comment for some reason.

                   

                  > I've also changed the query by removing the HINT and adding it as an option clause but now it fails again because apparently the maximum number of IN parameters is more then 2000.

                   

                  What Teiid version are you on, and what source (and via which driver) are you targeting?  We should generally have the defaults set per source such that it will work out of the box.  There are translator properties that can be used to override the defaults, see MaxInCriteriaSize and MaxDependentInPredicates - Translators - Teiid 8.7 (draft) - Project Documentation Editor

                  1 of 1 people found this helpful
                  • 6. Re: Question about joining temp tables
                    gadeyne.bram Master

                    Hi,

                     

                    I'm using SQuirrel SQL client 3.5.0 and I'm using Teiid 8.6 Maybe this client handles the markup like a comment. I'll use the OPTION clause from now on.

                    The connections itself are in this example to a Sybase server using a jtds driver (version 1.2.8). Other drivers I'm using are mysql 5.1.23 and ojdbc6 (oracle).

                     

                    It's sounds logical to me that there is a limitation on the IN statement. I've changed the query to 4 joins using LIMIT 1000 clauses with a different offset. That works fine.

                     

                    Thank you for the tip on the translator properties.

                    • 7. Re: Question about joining temp tables
                      Steven Hawkins Master

                      Yes SQuirreL seems to rarely send the exact sql.  It is bad about comments and statement delimiters.

                       

                      It looks like our default for sybase is 2000 (8 in predicates with 250 values each) dependent join values / source query.  However with prepared statements enabled, this doesn't take into account if there are other bind values beyond the dependent join, so you may be hitting an issue there.  If you set the max in predicates to 7 via a translator property, that should comfortably allow for other parameters.  Or you could turn using prepared statements off, although that may not be desirable in general.

                       

                      Steve