在视图中使用SQL表函数... [英] Using a SQL Table function in a view ...
问题描述
这是一个真正有希望的人！
我们想做什么：
1.我们必须为我们的应用程序提供一个可映射的数据库对象
对象，即表格或视图
有些观点非常复杂，它引发了另外几个
reqs。
1.视图的复杂性可能需要降低。使用表函数，
可以允许
a.predicating参数以获得最佳性能
b.and使用SQL / PL来帮助通过一些程序逻辑来降低复杂性。
但问题的核心是这个，
我们如何在视图中使用表函数并仍设法传递
参数？
我一直在想的一个想法是创建一个''参数表''
，应用程序填充了调用参数，然后
视图执行相关联接
即
调用应用程序:( JABSTFP =查看表函数参数表）
插入JABSTFP.ADD VALUES（1,1），（1,2），（1） ，3）;
从JABSTFV.ADD中选择*;
查看定义（JABSTFV）：
SELECT b.RESULT
来自JABSTFP.ADD a，TABLE（JABSTF.ADD（a.v1，a.v2））b
这适用于简单的情况但提出了问题
1.多个并发查询，我们如何在参数表中唯一识别
正确的行？
2.如何我们在选择后自动删除参数表中的行
？ - （我们不能在Table函数中执行它，因为
MODIFIES SQL DATA无效时
引用
在视图中）。
如果有任何人对此有任何意见，那么请随意。
谢谢。
Hi,
This is a real hopeful one!
What we are trying to do:
1. We MUST present a mappable database object for our application
objects i.e a Table or a View
Some of the views are very complex, which raises another couple of
reqs.
1. The view complexity may need to be reduced. Using Table Functions,
could allow for
a.predicating parameters to obtain best performance
b.and the use of SQL/PL to help reduce complexity with the
inclusion of some procedural logic.
But, the heart of the problem is this,
How do we use Table functions within a view and still manage to pass
parameters in ?
One idea I have been looking at, is to create a ''parameter table''
which is populated with call parameters by the application, which the
view then performs a correlated join with
i.e
Calling App: (JABSTFP = table function parameter table for view)
insert into JABSTFP.ADD VALUES (1,1),(1,2),(1,3);
Select * from JABSTFV.ADD;
View definition (JABSTFV):
SELECT b.RESULT
FROM JABSTFP.ADD a, TABLE(JABSTF.ADD(a.v1,a.v2)) b
This works in the simple case but raises questions of
1. Multiple concurrent queries, how do we uniquely identify the
correct rows in the parameter table ?
2. How do we automatically delete the rows from the parameter table
after the select ? - (we can''t do it within the Table function, as
MODIFIES SQL DATA is not valid when
referenced
inside a view).
If anybody has any input on any of this at all, then feel free.
Thanks.
Paul.
推荐答案
保罗，
对象？
或换句话说：
为什么你不能公开表函数？它是否具有可更新性？
一个rtaher狂野的想法是暴露参数在视图的
签名中（作为列！）当然在RETURNS子句中
表函数
然后你会使用WHERE子句来固定值。
之后，优化器可以通过混乱来挖掘
（谓词下推到RETURN语句中，传递
关闭等....）
您可能想在FP4中尝试这个。在那里教了几匹技巧）
不保证......取决于桌子的功能有多复杂。
让我发布:-)
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
Paul,
Can you define "mappable" object?
Or in other words:
Why can''t you expose a table-function? Is it for updatability?
One rtaher wild thought would be to expose the "parameters" in in the
signature of the view (as columns!) and of course in the RETURNS clause
of the table function
You would then use a WHERE clause to pin the values.
After that it''s up to the optimizer to tunnel through the mess
(predicate push down right into the RETURN statement, transitive
closure, etc....)
You may want to try this in FP4. Taught the horse a few more tricks there)
No guarantees... Depends on just how complex the table functions are.
Keep me posted :-)
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge，
Serge,
一个野心勃勃的想法是揭露参数"在视图的
签名中（作为列！）当然在表函数的RETURNS子句中
然后您将使用WHERE子句来固定值。
在那之后，由优化器来解决这个问题
（谓词按下RETURN语句，传递
关闭等等。）
你可能想要在FP4中尝试这个。在那里教了几个技巧）
One rtaher wild thought would be to expose the "parameters" in in the
signature of the view (as columns!) and of course in the RETURNS clause
of the table function
You would then use a WHERE clause to pin the values.
After that it''s up to the optimizer to tunnel through the mess
(predicate push down right into the RETURN statement, transitive
closure, etc....)
You may want to try this in FP4. Taught the horse a few more tricks there)
不容易理解。一个有用的例子会很有帮助
not easy to understand. A working example would be helpful
* gulp *我很害怕....
CREATE TABLE T1（c1 INT，c2 INT）;
创建函数foo（）返回表（INT，b INT）
返回SELECT *来自T1;
创建视图作为SELECT * FROM TABLE（foo（））AS F;
SELECT * FROM v WHERE a = 5;
DB2将解开所有这些并将其转换为：
SELECT * FROM T1 WHERE c1 = 5;
这当然是一个简单的例子。
关键是表函数在一天结束时只是一个
相关子查询。
干杯< br $>
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
*gulp*I was affraid of that....
CREATE TABLE T1(c1 INT, c2 INT);
CREATE FUNCTION foo() RETURNS TABLE(a INT, b INT)
RETURN SELECT * FROM T1;
CREATE VIEW v AS SELECT * FROM TABLE(foo()) AS F;
SELECT * FROM v WHERE a = 5;
DB2 will unravel all this and turn it into:
SELECT * FROM T1 WHERE c1 = 5;
This of course is a trivial example.
The point is that the table function is, at the end of the day just a
correlated subquery.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
这篇关于在视图中使用SQL表函数...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
