oracle 直接加载数据
oracle在数据加载或者数据插入的时候,可以通过用传统方式插入或者直接加载
传统方式插入比较类似于允许insert事务。
直接加载数据:
1、insert 中通过 APPEND hint方式
- SQL> insert /*+ append */ into test_f select * from dba_objects;
2、SQL*LODER直接路径加载(direct path)
- Sqlldr userid=user/pwd control=load.ctl direct=true
直接加载数据,会在一个段的HWM之上写入数据。数据不传递到缓冲池,直接写入到磁盘。也就避免了数据加载产生的redo。在nologging模式下,依然会产生少量的redo,维护数据字典的undo。然而,在一个表上直接路径加载数据,如果表上有索引,那么 加载数据可以生成很少量的redo,然后由于加载新的数据,会产生对索引的维护,这样依然会产生大量的redo(由于维护索引而产生)。
首先验证直接插入在段的HWM之上写入数据
SQL> create table test_e as select * from dba_objects ;
Table created.
SQL> create table test_e as select * from dba_objects;
Table created.
SQL> select segment_name ,extent_id from dba_extents where segment_name='TEST_E';
SEGMENT_NAME EXTENT_ID
--------------------------------------------------------------------------------- ----------
TEST_E 0
TEST_E 1
TEST_E 2
.
.
TEST_E 22
TEST_E 23
24 rows selected.
--可以看到,一共分配了24个区。
SQL> delete from test_e;
75303 rows deleted.
--删除表中的数据,但是表分配的区还在,高水位线并未下降。
SQL> select segment_name ,extent_id from dba_extents where segment_name='TEST_E';
SEGMENT_NAME EXTENT_ID
--------------------------------------------------------------------------------- ----------
TEST_E 0
TEST_E 1
TEST_E 2
.
.
TEST_E 22
TEST_E 23
24 rows selected.
--用传统方式加载数据
SQL>insert into test_e select * from dba_objects;
--查看区段
SQL> select segment_name ,extent_id from dba_extents where segment_name='TEST_E';
SEGMENT_NAME EXTENT_ID
--------------------------------------------------------------------------------- ----------
TEST_E 0
TEST_E 1
TEST_E 2
.
.
TEST_E 22
TEST_E 23
24 rows selected.
#--传统插入利用原有位于高水位线先以下的空闲区段。
--直接路径加载数据
SQL> delete from test_e;
SQL> commit;
SQL> insert /*+ append */ into test_e select * from dba_objects;
75303 rows created.
--此时查看区段信息
SQL> select segment_name,extent_id from dba_extents where segment_name='TEST_E';
SEGMENT_NAME EXTENT_ID
--------------------------------------------------------------------------------- ----------
TEST_E 0
TEST_E 1
TEST_E 2
.
.
TEST_E 30
TEST_E 31
32 rows selected.
---可以看到 ,直接路径加载数据,并没有利用之前已经清空数据,位于高水位线以下的数据块。而且在高水位线之上直接写入数据,然后将高水位线提高。
直接加载和并行
直接加载可以和并行执行一同使用,这样可以并行地向表中插入数据
SQL>alter session enable parallel dml; -- 这里必须显示的申明
SQL>insert /*+append parallel(test_f,2) */ into test_c select * from dba_objects;
SQL>insert /*+append */ into test_c select * from dba_objects;
注:在对insert 使用并行时,Oracle自动使用直接加载的方式进行数据加载,所以在这种情况下append是可以省略的。
当使用并行加载时,Oracle 会按照并行度启动相应数量的并行服务进程,像串行执行的直接加载的方式一样,每个并行服务进程都单独分配额外的空间用于加载数据,实际上Oracle 为每个并行服务进程分配了一个临时段,每个并行服务进程将数据首先加载到各自的临时段上,当所有的并行进程执行完毕后,将各自的数据块合并到一起,放到高水位之后,如果事务提交,则将高水位移到新加载的数据之后。
直接加载和SQL*LOADER
在SQL*LOADER中也可以使用直接加载,它比传统方式效率更高,因为它绕开了SQL的解析和数据缓冲区,直接将数据加载到数据文件,这对OLAP或者数据仓库系统非常有用。
指定加载:
Sqlldr userid=user/pwd control=control.ctl direct=true
指定并行和加载:
Sqlldr userid=user/pwd control=control.ctl direct=true parallel=true
SQL*LOADER直接加载对索引的影响:
(1)索引为非约束性,直接加载可以在加载完毕后维护索引的完整性。
(2)索引为约束性索引,比如主键,直接加载仍然会将数据加载入库,但是会将索引置为unusable.
如果使用SQL*LOADER的并行直接加载选项,并且表上有索引,将导致加载失败,这是我们可以在sqlloader中指定skip_index_maintenance=true, 来允许加载完成,但是索引状态会变成unusable,需要手工rebuild.
--详细见sqlloder使用