在 Oracle 上使用 ORDER 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1339991/
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
INSERT with ORDER on Oracle
提问by Stuart Ellis
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 10g 上，我们需要将记录从视图插入到表中，以支持本身没有排序或 ORDER 选项的哑客户端应用程序。有什么方法可以控制我们的 INSERT 语句向目标表添加记录的顺序吗？
回答by Vincent Malgrat
You can notreliably control in what order Oracle retrieve the row of a table without an ORDER BY.
您无法可靠地控制 Oracle 在没有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.
此外，如果没有/*+APPEND*/提示，Oracle 会将行物理存储在有空间的堆表中，该表可能不在表的末尾！您可能认为 Oracle 按顺序插入它们，但任何 DML 或并发活动（2 个以上会话插入）可能会产生不同的物理组织。
You could use an INDEX ORGANIZED tableto 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).
您可以使用INDEX ORGANIZED 表按 PK 的顺序存储行。此后对该表的大多数简单查询都将生成一组已排序的行。但是，如果您不指定 ORDER BY（根据查询和访问路径，行可能以任何顺序出现），这并不能保证 oracle 会按该顺序选择行。
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.
您也可以使用带有 order by 的视图，如果您无法触摸应用程序，这可能是您最好的选择（重命名表，使用表名创建视图，让应用程序认为它查询表）。不知道你的情况是否可行。
回答by cagcowboy
Unless you specify an ORDER BY, you can never guarantee the order in which Oracle will return rows from a SELECT
除非您指定 ORDER BY，否则您永远无法保证 Oracle 从 SELECT 返回行的顺序
回答by fras
Just use an ORDER BY. Something like
只需使用 ORDER BY。就像是
INSERT INTO table
(
SELECT
column1, column2
FROM
view
ORDER BY
column2
)
EDIT, this won't work actually. You could create a temporary view with the rows in the appropriate order, then do the insert.
编辑，这实际上行不通。您可以按适当的顺序使用行创建临时视图，然后进行插入。
回答by Jeffrey Kemp
Your main problem, that of your application not adding an ORDER BY to its query, might possibly be solved by the use of an index on the column(s) you wish to order by, then use a stored outline to cause the query to use the index to access the table.
您的主要问题，即您的应用程序未向其查询添加 ORDER BY 的问题，可能可以通过在您希望排序的列上使用索引来解决，然后使用存储的大纲使查询使用访问表的索引。
You'd have to test to see if this will work or not - be aware that it is possibly not enough to just add an INDEX() hint because the optimiser may find a way to honour the hint while not accessing the index in the right order; and if the query joins to other tables the ordering might be lost anyway.
您必须进行测试以查看这是否有效 - 请注意，仅添加 INDEX() 提示可能还不够，因为优化器可能会找到一种方法来兑现提示，而不会访问右侧的索引命令; 如果查询连接到其他表，排序可能会丢失。
回答by vapcguy
YES, there ISa way to control your order. I found through experience where I had a table of Countries, we'll call it OLD_COUNTRIES, that looked like this:
YES，还有IS控制订单的方式。我通过经验发现我有一个国家表，我们称之为OLD_COUNTRIES，它看起来像这样：
-----------------------------------
| ID | CODE | NAME |
-----------------------------------
|112099 | AF | Afghanistan |
|112100 | AA | Albania |
|... | .. | ... |
|112358 | ZB | Zimbabwe |
|112359 | AZ | Azores Islands |
|... | .. | ... |
-----------------------------------
Where I wanted the items that were added after the main list of countries (like the Azores and other islands that were added later) to actually appear in alphabetical order with the rest of the countries when I inserted them into a new table:
当我将它们插入新表格时，我希望在国家主要列表之后添加的项目（如亚速尔群岛和后来添加的其他岛屿）与其他国家按字母顺序实际显示：
CREATE TABLE MYAPP.COUNTRIES
(
ID NUMBER(*, 0)
, CODE NVARCHAR2(20)
, NAME NVARCHAR2(250)
);
I then ran this to make it work:
然后我运行它以使其工作：
INSERT /*+ append */ INTO MYAPP.COUNTRIES (ID, CODE, NAME)
SELECT ID, CODE, NAME FROM MYAPP.OLD_COUNTRIES_TABLE ORDER BY NAME ASC;
COMMIT;
And my new COUNTRIEStable came through alphabetized by name just fine.
我的新COUNTRIES桌子按名称按字母顺序排列就好了。
NOTE: COMMITis required or you'll get the error: ORA-12838: cannot read/modify an object after modifying it in parallelwhen you try to open the table to look at it in Oracle SQL Developer.
注意： COMMIT是必需的，否则您将收到错误： ORA-12838: cannot read/modify an object after modifying it in parallel当您尝试在 Oracle SQL Developer 中打开表查看它时。
NOTE: If you don't use /*+ append */, it will not insert in the order you specify - it will ignore ORDER BY. And I know that it used my ORDER BYwhen I used /*+ append */and not just defaulted to a primary key on the old or new tables because neither had primary keys.
注意：如果您不使用/*+ append */，它将不会按照您指定的顺序插入 - 它会忽略ORDER BY. 而且我知道它ORDER BY在我使用时使用了我的/*+ append */，而不只是默认为旧表或新表上的主键，因为它们都没有主键。
CAVEAT: According to the accepted answer's author, the append hint, as with "any DML or concurrent activity...might produce a different physical organization". While possibly true, don't do any concurrent operations during this and have a COMMITstatement in your code where I do, so that doesn't happen!
警告：根据接受的答案的作者，附加提示，如“任何 DML 或并发活动......可能会产生不同的物理组织”。虽然可能是真的，但在此期间不要执行任何并发操作，并COMMIT在您的代码中声明我所做的，这样就不会发生！
