按顺序排序是否保证选择的顺序？ [英] Does order by in view guarantee order of select?

问题描述

我认为仅使用某种顺序才有意义。我想做的是在视图中包含 ORDER BY 子句，以便该视图上的所有 SELECT s视图可以忽略它。但是，我担心该顺序不一定会延续到 SELECT ，因为它没有指定顺序。

I have view for which it only makes sense to use a certain ordering. What I would like to do is to include the ORDER BY clause in the view, so that all SELECTs on that view can omit it. However, I am concerned that the ordering may not necessarily carry over to the SELECT, because it didn't specify the order.

是否存在一种情况，即视图指定的顺序不会反映在该视图上的select结果中（视图中的order by子句除外）？

Does there exist a case where an ordering specified by a view would not be reflected in the results of a select on that view (other than an order by clause in the view)?

推荐答案

在没有显式 ORDER BY 子句。如果查询有序视图，但没有包含 ORDER BY 子句，则如果它们的顺序正确，请感到惊喜，不要指望它会

You can't count on the order of rows in any query that doesn't have an explicit ORDER BY clause. If you query an ordered view, but you don't include an ORDER BY clause, be pleasantly surprised if they're in the right order, and don't expect it to happen again.

这是因为查询优化器可以自由地以不同的方式访问行，具体取决于查询，表统计信息，行数，索引等。如果知道查询中没有 ORDER BY 子句，则可以忽略行顺序，以便（咳嗽）更快地返回行。

That's because the query optimizer is free to access rows in different ways depending on the query, table statistics, row counts, indexes, and so on. If it knows your query doesn't have an ORDER BY clause, it's free to ignore row order in order (cough) to return rows more quickly.

稍微偏离主题。 。 。

排序顺序在各个平台上不一定相同，即使对于知名的排序规则也是如此。我知道在Mac OS X上对UTF-8进行排序特别奇怪。 （PostgreSQL开发人员称其为 broken 。）PostgreSQL依赖于strcoll（），据我了解它依赖于操作系统区域设置。

Sort order isn't necessarily identical across platforms even for well-known collations. I understand that sorting UTF-8 on Mac OS X is particularly odd. (PostgreSQL developers call it broken.) PostgreSQL relies on strcoll(), which I understand relies on the OS locales.

目前尚不清楚我对PostgreSQL 9.1的处理方式。在9.1中，您可以有多个索引，每个索引具有不同的排序规则。没有指定排序规则的ORDER BY通常将使用基础基表的列的排序规则，但是优化器将对 index 进行哪些操作，该 index 指定与基本表中未索引的列不同的排序规则桌子？

It's not clear to me how PostgreSQL 9.1 will handle this. In 9.1, you can have multiple indexes, each with a different collation. An ORDER BY that doesn't specify a collation will usually use the collation of the underlying base table's columns, but what will the optimizer do with an index that specifies a different collation than an unindexed column in the base table?

这篇关于按顺序排序是否保证选择的顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！