在子查询中使用 LIMIT ALL 有什么好处? [英] What are the benefits of using LIMIT ALL in a subquery?

问题描述

在研究一个不相关的主题时，我注意到在 IBM 知识中心网站上的 Netezza 示例中使用了 LIMIT ALL.我不清楚在这里指定 LIMIT ALL 的好处，我正在寻求 IBM 对解释(下面引用)的澄清.我什么时候需要指定 LIMIT ALL?

While researching an unrelated topic, I noticed the use of LIMIT ALL in an example on the IBM Knowledge Center website for Netezza. I'm unclear about the benefits of specifying LIMIT ALL here, and I am seeking clarification of the explanation (quoted below) from IBM. When might I need to specify LIMIT ALL?

SELECT CASE WHEN rand = .1 THEN 'A' WHEN rand = .2 THEN 'B' ELSE 'C' END
FROM (SELECT random() rand FROM tblA LIMIT ALL) subset

来自 IBM 知识中心:

From the IBM Knowledge Center:

"子查询中的 LIMIT ALL 阻止其被拉入父查询中，并且对于 tblA 的每一行仅调用一次 random() 函数，因此在每个 WHEN 中测试相同的 random() 结果条款."

"The LIMIT ALL in the subquery prevents it from being pulled up into the parent query, and the random() function is invoked only once for each row of tblA, and therefore the same random() result is tested in each WHEN clause."

顺便说一句……我的问题与 IBM 示例中random()"的使用无关.

BTW... My question is unrelated to the use of "random()" in IBM's example.

谢谢！

推荐答案

我已经看到它在查询系统目录视图(以_"开头的那些)时经常使用，因为优化器提示将导致计划不提取主机上 (Postgres) 数据库中的底层目录表的全部内容，并将其内容发送到 SPU.总的来说，这是一个好主意.

I have seen it used frequently when querying system catalog views (those that begins with a ‘_’) since the optimizer hint will cause the plan to NOT pull the entire content of the underlying catalog tables out of the (Postgres) database on the host and send their content to the SPUs. In general that is a good idea to try.

这篇关于在子查询中使用 LIMIT ALL 有什么好处?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！