ORACLE PL/SQL：按情况排序性能
我目前有一个服务器端分页查询：
SELECT * FROM (SELECT a.*, rownum rnum FROM (
( /* Insert Query Here */ ) ) a
WHERE rownum <= ((page_number + 1) * page_size))
WHERE rnum >= (((page_number + 1) * page_size)) - (page_size - 1);
然而，问题是试图确定用户正在排序的内容，因为它与网格视图相关联。目前，我正在使用：
ORDER BY
CASE sort_direction
WHEN 'ASC' THEN
CASE sort_column
WHEN 'PRIMARY_KEY' THEN
primary_key
ELSE key
END
END
ASC,
CASE sort_direction
WHEN 'DESC' THEN
CASE sort_column
WHEN 'PRIMARY_KEY' THEN
primary_key
ELSE key
END
END
DESC
我在坚持服务器端分页方案的每个查询上都使用它。问题是，当我的网格包含相当多的字段时，性能会大幅下降。还有其他方法可以做到这一点吗？我是否需要简单地设置更少的字段来允许分页？
I currently have a server side paging query as such:
SELECT * FROM (SELECT a.*, rownum rnum FROM (
( /* Insert Query Here */ ) ) a
WHERE rownum <= ((page_number + 1) * page_size))
WHERE rnum >= (((page_number + 1) * page_size)) - (page_size - 1);
The problem, however, is trying to determine what the user is sorting on as this is tied to a gridview. Currently, I'm using:
ORDER BY
CASE sort_direction
WHEN 'ASC' THEN
CASE sort_column
WHEN 'PRIMARY_KEY' THEN
primary_key
ELSE key
END
END
ASC,
CASE sort_direction
WHEN 'DESC' THEN
CASE sort_column
WHEN 'PRIMARY_KEY' THEN
primary_key
ELSE key
END
END
DESC
I'm using this on every query I stick into the server side paging scheme. The problem is that when I have a grid that has quite a few fields, the performance degrades substantially. Is there any other way to do this? Do I need to simply set less fields to allow paging?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用动态 SQL 并在运行时构建 ORDER BY。 Oracle 的 SQL 引擎将看到一个简单的 ORDER BY。
Use Dynamic SQL and build the ORDER BY at runtime. Oracle's SQL engine will see a simple ORDER BY.
我怀疑优化器无法弄清楚这一点，因此无法使用索引。检查“解释计划”的内容。
明显的解决方案是让您的应用程序“评估”这种情况，并发送一个更简单的查询。我想您会发现
ORDER BY Primary_key ASC速度要快得多。I suspect the optimizer can't figure this out, and thus can't use indices. Check what EXPLAIN PLAN says.
Obvious solution is to have your app "evaluate" that case, and send a much simpler query. I think you'll find
ORDER BY primary_key ASCto be much faster.如果primary_key和key都是数字，那么降序查询与升序乘以-1相同。
上式中，direction 为整数。传入 1 或 -1。这是一个更简单的表达式，并且运行速度应该更快。
If the primary_key and key are numbers then a descending query is the same as an ascending multiplied by -1.
In the above, direction is an integer. Pass in 1 or -1. This is a much simpler expression and should run faster.