oracle SELECT * FROM TABLE（流水线函数）：我可以确定结果中行的顺序吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16713002/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT * FROM TABLE(pipelined function): can I be sure of the order of the rows in the result?
提问by Benoit
In the following example, will I always get “1, 2”, or is it possible to get “2, 1” and can you tell me where in the documentation you see that guarantee if it exists?
在下面的例子中，我会总是得到“1, 2”，还是有可能得到“2, 1”，你能告诉我你在文档中的哪个地方看到了保证是否存在？
If the answer is yes, it means that without ORDER BYnor ORDER SIBLINGSthere is a way to be sure of the result set order in a SELECT statement.
如果答案是肯定的，则意味着没有ORDER BY也ORDER SIBLINGS没有办法确定 SELECT 语句中的结果集顺序。
CREATE TYPE temp_row IS OBJECT(x number);
/
CREATE TYPE temp_table IS TABLE OF temp_row;
/
CREATE FUNCTION temp_func
RETURN temp_table PIPELINED
IS
BEGIN
PIPE ROW(temp_row(1));
PIPE ROW(temp_row(2));
END;
/
SELECT * FROM table(temp_func());
Thank you.
谢谢你。
采纳答案by Ben
I don't think that there's anywhere in the documentation that guaranteesthe order that data will be returned in.
我认为文档中没有任何地方可以保证数据返回的顺序。
There's an old Tom Kyte threadfrom 2003 (so mightbe out of date) which states that relying on the implicit order would not be advisable, for the same reasons as you would not rely on the order in ordinary SQL.
有一个来自 2003 年的旧Tom Kyte 线程（因此可能已经过时），它指出依赖隐式顺序是不可取的，原因与您不依赖普通 SQL 中的顺序相同。
1st: is the order of rows returned from the table function within a SQL statement the exact same order in which the entries were "piped" into the internal collection (so that no order by clause is needed)?
...
Followup May 18, 2003 - 10am UTC:
1) maybe, maybe not, I would not count on it. You should not count on the order of rows in a result set without having an order by. If you join or do something more complex then simply "select * from table( f(x) )", the rows could well come back in some other order.
empirically -- they appear to come back as they are piped. I do not believe it is documented that this is so.
In fact, collections of type NESTED TABLE are documented to explicitly not have the ability to preserve order.
第一：SQL 语句中从表函数返回的行的顺序是否与条目“管道”到内部集合中的顺序完全相同（因此不需要 order by 子句）？
...
跟进 2003 年 5 月 18 日 - UTC 时间上午 10 点：
1）也许，也许不是，我不会指望它。在没有 order by 的情况下，您不应该依赖结果集中的行顺序。如果您加入或做一些更复杂的事情，那么只需“从表中选择*（f（x））”，行很可能会以其他顺序返回。
根据经验——它们似乎在管道输送时又回来了。我不相信有文件证明是这样。
事实上， NESTED TABLE 类型的集合被记录为明确 不能保留 order。
To be safe, you should do as you always would in a query, state an explicit ORDER BY, if you want the query results ordered.
为安全起见，如果您希望查询结果排序，您应该像往常一样在查询中声明一个明确的 ORDER BY。
Having said that I've taken your function and run 10 million iterations, to check whether the implicit order was ever broken; it wasn't.
话虽如此，我已经使用您的函数并运行了 1000 万次迭代，以检查隐式顺序是否被破坏；不是。
SQL> begin
2 for i in 1 .. 10000000 loop
3 for j in ( SELECT a.*, rownum as rnum FROM table(temp_func()) a ) loop
4
5 if j.x <> j.rnum then
6 raise_application_error(-20000,'It broke');
7 end if;
8 end loop;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
回答by David Aldridge
This procedural logic works differently to table-based queries. The reason that you cannot rely on orders in a select from a table is that you cannot rely on the order in which the RDBMS will identify rows as part of the required set. This is partly because of execution plans changing, and partly because there are very few situations in which the physical order of rows in a table is predictable.
此过程逻辑的工作方式与基于表的查询不同。不能依赖从表中选择的顺序的原因是不能依赖 RDBMS 将行标识为所需集合的一部分的顺序。这部分是因为执行计划发生了变化，部分是因为很少有表中行的物理顺序是可预测的。
However here you are selecting from a function that does guarantee the order in which the rows are emitted from the function. In the absence of joins, aggregations, or just about anything else (ie. for a straight "select ... from table(function)") I would be pretty certain that the row order is deterministic.
但是，在这里您是从一个可以保证行从函数中发出的顺序的函数中进行选择。在没有连接、聚合或其他任何东西的情况下（即，对于直接的“从表（函数）中选择……”），我非常确定行顺序是确定性的。
That advice does not apply where there is a table involved unless there is an explicit order-by, so if you load your pl/sql collection from a query that does not use an order-by then of course the order of rows in the collection is not deterministic.
该建议不适用于涉及表的情况，除非有明确的 order-by，因此如果您从不使用 order-by 的查询加载 pl/sql 集合，那么当然集合中的行顺序不是确定性的。
