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
============================================================================