-
1. subquery in teiid
rareddy Mar 31, 2011 10:50 AM (in response to meltedmetal)S.Q,
There are couple different ways you can do this.
1) Define costing information on the tables in the Designer (look at Cardinality property on View Table). What you are defining is roughly how rows of data is present in this table.
2) Or use hints like MAKEDEP.
Search for costing and MAKEDEP in the Reference guide.
Ramesh..
-
2. subquery in teiid
shawkins Mar 31, 2011 4:09 PM (in response to rareddy)MAKEDEP does not by default apply to subqueries.
There are a couple of things you can do.
You can set the system property: org.teiid.subqueryUnnestDefault=true
This will direct the rewriter to change this query (and many other situations involving subqueries) into:
select * from B, (select [distinct] id from A) AS X WHERE
where B.id = X.id
If you have marked also marked by as dependent or you have the costing set as you have stated, then the result will be a dependent join.
Alternatively you can use the /*+ mj */ hint instead of setting the org.teiid.subqueryUnnestDefault property.
select * from B where B.id in /*+mj*/ (select id from A);
Here again you would need to have the costing set appropriately or have be marked as dependent.
Steve
-
3. subquery in teiid
meltedmetal Apr 1, 2011 1:41 AM (in response to shawkins)Thanks Steve & Ramesh!
I have tried MAKEDEP for join query.
But I am not clear that how MAKEDEP is used in subquery.
Where do I need to put the key word 'MAKEDEP' in the SQL?
Would you like give me a example for the below SQL?
select * from B where B.id in /*+mj*/ (select id from A);
Best Regards,
S.Q.
-
4. subquery in teiid
shawkins Apr 1, 2011 3:32 PM (in response to meltedmetal)If you have the cardinalities set on the tables in your model, then an additional should not be needed.
Otherwise use:
select * from B
MAKEDEP
where B.id in /*+mj*/ (select id from A);