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

Question about joining temp tables

gadeyne.bram Feb 25, 2014 11:23 AM

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

shawkins Feb 25, 2014 4:07 PM (in response to gadeyne.bram)

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
Actions
2. Re: Question about joining temp tables

gadeyne.bram Feb 26, 2014 3:00 AM (in response to shawkins)

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

============================================================================
Actions
3. Re: Question about joining temp tables

shawkins Feb 26, 2014 7:10 AM (in response to gadeyne.bram)

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
Actions
4. Re: Question about joining temp tables

gadeyne.bram Feb 27, 2014 4:34 AM (in response to shawkins)

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.
Actions
5. Re: Question about joining temp tables

shawkins Feb 27, 2014 7:45 AM (in response to gadeyne.bram)

> 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
Actions
6. Re: Question about joining temp tables

gadeyne.bram Feb 27, 2014 10:15 AM (in response to shawkins)

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.
Actions
7. Re: Question about joining temp tables

shawkins Feb 27, 2014 10:59 AM (in response to gadeyne.bram)

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
Actions
8. Re: Question about joining temp tables

shawkins Mar 6, 2014 10:48 AM (in response to shawkins)

[TEIID-2886] Sybase dependent join IN clause too large - JBoss Issue Tracker was logged to update the Sybase translator defaults.
Actions

Go to original post