在 Oracle 上使用 ORDER 插入

INSERT with ORDER on Oracle(在 Oracle 上使用 ORDER 插入)

问题描述

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

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?

推荐答案

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

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

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

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.

您可以使用 INDEX ORGANIZED 表 以PK的顺序存储行.此后对该表的大多数简单查询都将生成一组已排序的行.但是，如果您不指定 ORDER BY，这并不能保证 oracle 会按该顺序选择行(根据查询和访问路径，行可能以任何顺序出现).

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).

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

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 插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！