oracle 批量插入去重,Oracle优化技术

1、基本原理

Oracle的日志：Oracle中为了提高硬盘写的效率，采用内存中数据缓冲区来保存数据，等到一定量或一定时间后才写到磁盘(DBWR)。

这个时候假如断电之类的故障发生，数据缓冲区的数据将丢失。

所以需要日志来保存记录，它也有个缓冲区，每隔3秒或Commit或满3分之1 都会触发LGWR进程写到REDO文件中(日志文件)。

回滚：Oracle中采用多版本控制来进行并发。当写数据时，先把原数据写入UNDO中(回滚表空间)。然后再将原数据的位置修改为新数据。(其它进程读的时候，读回滚表中的数据，这就防止了脏读)。如果Commit了，那就回收回滚表空间。如果回滚了，那就将前镜像读出来覆盖原位置。

关于回滚的数据的所有操作，都要涉及日志，所以有UNDO就会有大量REDO！

DML的REDO：假如插入的日志为6(省略单位)(UNDO记录插入记录的rowid)。更新的日志就是7(UNDO记录更新记录的那列的旧字段)。而删除的日志就是32(UNDO记录原整行数据)。

2、批量插入

绑定变量：减少SQL的解析。

批量提交：减少与Oracle的交互。减少IO。

3、存储机制

SEGMENT -> 表示一张表。包含多个Extent。

EXTENT -> 表示扩展的最小单位。当数据增多时，按照一个EXTENT来进行扩充的。包含8个块。

Block -> 表示一个块，存储的最小单位(8KB)。头，表目录，行目录，可用空间(空余空间)，数据区。

4、临时表与分区与聚簇

临时表优点：1.高效的删除记录，基于事务的全局临时表COMMIT或SESSION连接退出后，临时表记录自动删除(产生的记录也要少的多)。

2.针对不同会话数据独立，不同SESSION访问看到的结果不同。

分区：数据的完全独立分离(不同与索引的产生地址)。所以它会产生多个SEGMENT(就是多张表)。

1.当根据分区字段查询时，可以高效的分区消除。只访问一部分。

2.强大的分区操作，truncate删除，数据转移，分区切割，分区合并。

3.全局索引，只有分区列才能创建全局索引(或前缀)。很多分区操作会使全局索引失效，必须重建。

4.局部索引，其它字段就只能构建局部索引。当进行查询时，需要访问所有分区的局部索引，有时候，反而效率更低(每个索引基本需要4次IO)。

聚簇表：只能使用主键索引来约束数据的存放位置(通过organization index)。(默认主键是非聚簇)。优点是查询所有时不需要有回表的操作。缺点是更新开销更大。

5、索引

B+树的存储方式。50W条的时候高度为3。这时需要4次IO(包括回表)。

索引的优点：

1.COUNT、SUM、AVG。但是注意当有空值时(B树不储存空值)，也许开销更大。

2.MAX、MIN。但是注意不能同时使用MAX+MIN。因为这个时候Oracle为了原子性。会全表扫描。

3.ORDER BY(排序消耗大)、DISTINCT(会排序后消除)、UNION(注意单独的UNION是需要排序去重的)。

4.组合索引：避免回表。但是注意查询范围时，必须按照组合的前缀顺序来查(等值无所谓)。有等值有范围时，等值在前，范围在后。

5.位图索引：大量重复，极少更新时建立。

6、表连接

1.嵌套连接：用的最多，读出驱动表的所有适合行，然后一个一个拿到被驱动表中去根据连接条件查询(所以为了减少IO，驱动表最好为小的)。所以需要驱动表限制条件有索引，被驱动表连接条件有索引。

2.哈希连接：读出驱动表建立hash(驱动表小)，读出被驱动表来查找hash。不支持大于小于的比较。

3.排序合并：读出两个表的元素，分别排序，然后归并。两个表的连接条件都要有索引。不支持不等于。

参考：收获，不止Oracle。