oracle 54,sql – Oracle直接加载插入54,061,487条记录

我有一个54,061,487记录的直接加载插入.我正在寻找速度.我根本不需要回滚.

涉及的所有表都设置为NOLOGGING.

这是一种方法

EXECUTE IMMEDIATE 'TRUNCATE TABLE meRegionsNow';

...

INSERT /*+ APPEND */ INTO meRegionsNow(

carrierId ,

region ,

zip ,

side ,

subPlanTypeId ,

monthIn )

SELECT

r.carrierId as carrierId ,

r.region as region ,

r.zip as zip ,

r.side as side ,

r.subPlanTypeId as subPlanTypeId ,

t.monthIn as monthIn

FROM

meTimeline t

INNER JOIN region r

ON t.monthIn >= r.effective AND

t.monthIn <= r.expiry;

此操作的执行计划符合预期(使用右侧

索引加快连接速度)：

Statement Id=5336 Type=

Cost=2.64022111505165E-308 TimeStamp=25-10-11::15::35:08

(1) SELECT STATEMENT ALL_ROWS

Est. Rows: 5,667 Cost: 483

(5) TABLE TABLE ACCESS BY INDEX ROWID SCHEMA.REGION [Analyzed]

(5) Blocks: 2,826 Est. Rows: 944 of 377,779 Cost: 80

Tablespace: USERS

(4) NESTED LOOPS

Est. Rows: 5,667 Cost: 483

(2) INDEX INDEX FULL SCAN SCHEMA.METL$MONTHIN [Analyzed]

Est. Rows: 6 Cost: 1

(3) INDEX INDEX RANGE SCAN SCHEMA.RGN$MULTI3 [Analyzed]

Est. Rows: 944 Cost: 72

这是另一种方法：

EXECUTE IMMEDIATE 'TRUNCATE TABLE meRegionsNow';

...

DECLARE

CURSOR meTimeline_cur IS

SELECT monthIn

FROM meTimeline

ORDER BY monthIn;

BEGIN

FOR meTimeline_rec IN meTimeline_cur LOOP

/* Cross regions with timeline */

INSERT /*+ APPEND */ INTO meRegionsNow(

carrierId ,

region ,

zip ,

side ,

subPlanTypeId ,

monthIn )

SELECT

r.carrierId as carrierId ,

r.region as region ,

r.zip as zip ,

r.side as side ,

r.subPlanTypeId as subPlanTypeId ,

meTimeline_rec.monthIn as monthIn

FROM region r

WHERE

meTimeline_rec.monthIn >= r.effective AND

meTimeline_rec.monthIn <= r.expiry;

COMMIT;

END LOOP;

END;

什么是最快的方式？我认为光标控制的直接加载插入和直接SQL直接加载插入之间没有太大区别.

同样,我不关心日志记录,回滚,保留任何撤消数据.我怀疑这个问题花费很长时间的原因是表空间数据文件使用太小的默认扩展区太频繁地自动扩展.

我认为只要调整meRegionsNow表空间的大小,这个问题就会得到解决

数据文件.