05o海量数据库架构设计第10课数据加载

1、海量数据的设计 第十课-数据加载2012.7.2DATAGURU专业数据分析OLAP数据库数据加载方式u sql*loader工具u OCI借口调用sql*loaderu 传统的insert2012.5.282012-9-2DATAGURU专业数据分析sql*loader机制2012.5.282012-9-2DATAGURU专业数据分析sql*loader较传统的数据加载方式优势u 批量数据加载u 更灵活的加载u 效率更高2012.5.282012-9-2DATAGURU专业数据分析sql*loader的重要参数 direct=falseConventional Path Loads 常规的数

2、据加载 When accessing an indexed table concurrently with the load, or when applying inserts or updates to a nonindexed table concurrently with the load When loading data into a clustered table A direct path load does not support loading of clustered tables. When loading a relatively small number of row

3、s into a large indexed tableDuring a direct path load, the existing index is copied when it is merged with the new index keys. If the existingindex is very large and the number of new keys is very small, then the index copy time can offset the time saved by a direct path load. When loading a relativ

4、ely small number of rows into a large table with referential and column-check integrity constraints When loading records and you want to ensure that a record is rejected under any of the following circumstances:If the record, upon insertion, causes an Oracle error If the record is formatted incorrec

5、tly, so that SQL*Loader cannot find field boundariesIf the record violates a constraint or tries to make a unique index non-unique2012.5.282012-9-2DATAGURU专业数据分析常规数据加载的性能u Conventional Path Loads 常规的数据加载BINDSIZE设置每次提交数据的大小2012.5.282012-9-2DATAGURU专业数据分析直接加载的机制 direct=true2012.5.282012-9-2DATAGURU专业数

6、据分析直接加载的优势A direct path load is faster than the conventional path for the followingreasons:Partial blocks are not used, so no reads are needed to find them, and fewer writes are performed.SQL*Loader need not execute any SQL INSERT statements; therefore, the processing load on the Oracle database is

7、reduced.A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement.A direct path load uses multiblock asynchronous I/O for writ

8、es to the database files.During a direct path load, processes perform their own write I/O, instead of using Oracle's buffer cache. This minimizes contention with other Oracle users.2012.5.282012-9-2DATAGURU专业数据分析直接加载的限制u Tables are not clustered.u Segments to be loaded do not have any active transactions pending.2012.5.282012-9-2DATAGURU专业数据分析并行加载u Concurrent conventional path loadsu Intersegment concurrency with the direct path load methodu Intrasegment concurrency with the