Peoplesoft 查询 - 性能
我遇到了 PeopleSoft 查询（使用 Oracle 后端数据库）的问题：当用户发起涉及多个记录的相当复杂的查询时，PS 会强制连接安全记录，从而生成如下 SQL：
选择...
ps_job a、PS_EMPL_SRCQRY a1、ps_table2 b、ps_sec_rcd2 b1、ps_table3 c、ps_sec_rcd3 c1
其中 (...security 连接 a->a1, b->b1, c->c1...) 和 (...a、b 和 c 的连接...) and
a.setid_dept = 'XYZ';
（假设最后一个条件具有高选择性并且该列上有索引） 显然，由于条件的安排，首先创建一个巨大的连接，写入临时段，并且当最终应用最后一个条件时，仅选择一个小子集。以这种方式制定的查询很可能会达到 APPSRV 甚至 QRYSRV 的预设超时。手动编写查询时，我宁愿将最具选择性的条件移至开头，从而将正在处理的数据量限制在相当大的水平。
关于如何让 PS 表现得像这样有什么想法吗？实际上，已经将“Oracle 风格”SQL 重写为 ANSI SQL 似乎可以加速查询 - 然而，PS 编写 Oracle 风格查询...
提前致谢
DBa
I'm facing a problem with PeopleSoft queries (using Oracle backend database): when a rather complex query involving multiple records is set off by a user, PS does an enforced join of security records, thus producing SQL like this:
select .... from
ps_job a, PS_EMPL_SRCQRY a1, ps_table2 b, ps_sec_rcd2 b1, ps_table3 c, ps_sec_rcd3 c1
where (...security joins a->a1, b->b1, c->c1...) and (...joins of a, b and c...) and
a.setid_dept = 'XYZ';
(let's assume the last condition has a high selectivity and there is an index on the column)
Obviously, due to the arrangement of the conditions, first a huge join is created, written to the temp segment, and when the last condition is finally applied, only a small subset is selected. A query formulated in this way is very likely to hit the preset timeout of the APPSRV, and even of the QRYSRV. When writing the query manually, I would rather move the most selective condition to the start, thus limiting the amount of the data being handled, to a considerable level.
Any ideas on how to make PS behave like this? Actually, already rewriting "Oracle-styled" SQL to ANSI SQL seems to accelerate the queries - however, PS writes Oracle-style queries...
Thanks in advance
DBa
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除了 Grant 所建议的之外，另一种方法是在用户将查询并执行正常连接的表上创建视图。
对于上述情况，您必须 -
1. 为查询中将使用的每条记录创建视图。
2. 将视图添加到查询安全树。
3. 使用 PS 查询中的视图。这将在视图上执行正常的连接，并且连接中不会有安全记录。
要对数据实施用户级安全性，您可以拥有另一个安全视图并将其连接到最终查询，并在 where 子句中设置一个条件来检查当前登录的用户。
这样您就可以创建固定数量的视图，而不是为每个用户查询创建一个视图。
Apart from what Grant had suggested, another way would be to create views on the tables that the user would query and perform normal joins.
For the above you will have to -
1. Create views for each of the records that would be used in the query.
2. Add the views to the query security tree.
3. Use the views in the PS query. This would perform the normal joins on the views and there would be no security records in the joins.
To enforce user level security on the data, you could have another security view and join it to the final query, and have a condition in the where clause checking the currently logged in user.
This way you would have a fixed number of views to create and not a view for each user query.
我所知道的唯一修复方法是创建一个正确执行连接的视图，以强制它按照应有的方式进行连接，而不是按照它的方式进行连接（并避免可怕的合并连接笛卡尔）。
The only fix I know of to force it to do the joins the way it should, instead of the way it does (and avoid the dreaded Merge Join Cartesian) is to create a view that does the join correctly.