Oracle插入选择，排序依据 [英] Oracle Insert Select with order by

问题描述

我正在处理一个plsql过程，其中我正在使用插入选择语句. 我需要按顺序插入表中.但是我在选择sql中使用的顺序不起作用.

I am working on a plsql procedure where i am using an insert-select statement. I need to insert into the table in ordered manner. but the order by i used in the select sql is not working.

oracle中是否有任何特定的方式以有序的方式插入行?

is there any specific way in oracle to insert rows in orderly fashion?

推荐答案

有序插入的典型用例是为了在同一块中共同定位特定值(有效地减少了列索引上的聚类因子，因此您已订购数据).

The typical use case for an ordered insert is in order to co-locate particular value in the same blocks (effectively reducing the clustering factor on indexes on columns by which you have ordered the data).

这通常需要直接路径插入...

This generally requires a direct path insert ...

insert /*+ append */ into ...
select ...
from   ...
order by ...

只要您接受它仅对批量数据有价值，数据将仅加载高水位线以上并且涉及锁定问题，则对此没有任何无效.

There's nothing invalid about this as long as you accept that it's only worthwhile for bulk data, that the data will load above the high water mark only, and that there are locking issues involved.

另一种可以达到大致相同效果，但可以说更适合OLTP系统的方法是在集群中创建表.

Another approach which achieves mostly the same effect, but which is more arguably more suitable for OLTP systems, is to create the table in a cluster.

这篇关于Oracle插入选择，排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！