在Oracle上使用ORDER插入 [英] INSERT with ORDER on Oracle

问题描述

在Oracle 10g上，我们需要将视图中的记录插入表中，以支持本身没有sort或ORDER选项的哑客户端应用程序.有什么方法可以控制我们的INSERT语句将记录添加到目标表的顺序?

解决方案

您可以不可靠地控制Oracle在没有ORDER BY的情况下以什么顺序检索表的行.

此外，如果没有/*+APPEND*/提示，Oracle会将行实际存储在堆空间中的堆表中，该空间可能不在表的末尾！您可能会认为Oracle按顺序插入它们，但是任何DML或并发活动(插入2个以上会话)可能会产生不同的物理组织.

您可以使用 INDEX ORGANIZED表以按PK的顺序存储行.此后在该表上进行的大多数简单查询将产生一组已排序的行. 但是，如果您未指定ORDER BY，这不能保证oracle将按该顺序选择行(取决于查询和访问路径，行可以按任何顺序排列.) /p>

您还可以使用带有排序顺序的视图，如果您无法触摸应用程序，则这可能是最好的选择(重命名表，使用表名创建视图，让应用程序认为它查询了桌子).我不知道您的情况是否可行.

On Oracle 10g we need to insert records from a view into a table to support a dumb client application that does not have sort or ORDER options itself. Is there any way to control the order in which our INSERT statement adds records to the destination table?

解决方案

You can not reliably control in what order Oracle retrieve the row of a table without an ORDER BY.

Furthermore, without the /*+APPEND*/ hint, Oracle will store the rows physically in a heap table where there is room, which may not be at the end of the table ! You may think Oracle inserts them sequentially but any DML or concurrent activity (2+ sessions inserting) might produce a different physical organization.

You could use an INDEX ORGANIZED table to store the rows in the order of the PK. Most simple queries thereafter on that table will produce a sorted set of rows. This would not however guarantee that oracle will select the rows in that order if you don't specify an ORDER BY (depending on the query and the access path, the rows may come in any order).

You could also use a view with an order by, this is probably your best bet if you can't touch the application (rename the table, create a view with the name of the table, let the application think it queries the table). I don't know if it is feasible in your case.

这篇关于在Oracle上使用ORDER插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！