第10课 数据加载.pdf

《第10课 数据加载.pdf》由会员分享，可在线阅读，更多相关《第10课 数据加载.pdf（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、DATAGURU专业数据分析网站 2012.2012.7 7.2 2 海量数据的设计海量数据的设计 第第十十课课-数据加载数据加载2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28OLAPOLAP数据库数据加载方式数据库数据加载方式sql*loader工具OCI借口调用sql*loader传统的insert2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28sqlsql*loaderloader机制机制2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28sqlsql*loaderloader较传统

2、的数据加载方式优势较传统的数据加载方式优势 批量数据加载 更灵活的加载控制 效率更高2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28sqlsql*loaderloader的重要参数的重要参数 direct=false direct=false Conventional Path Loads Conventional Path Loads 常规的数据加载常规的数据加载When accessing an indexed table concurrently with the load,or when applying inserts or updates to

3、a nonindexed table concurrently with the loadWhen loading data into a clustered tableA direct path load does not support loading of clustered tables.When loading a relatively small number of rows into a large indexed tableDuring a direct path load,the existing index is copied when it is merged with

4、the new index keys.If the existing index 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 relatively small number of rows into a large table with referential and column-check integrity constraintsWhen loadi

5、ng 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 errorIf the record is formatted incorrectly,so that SQL*Loader cannot find field boundariesIf the record violates a constraint or tries to make a unique

6、index non-unique2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28常规数据加载的性能常规数据加载的性能 Conventional Path Loads 常规的数据加载 BINDSIZE 设置每次提交数据的大小2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28直接加载的机制直接加载的机制 direct=true direct=true2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28直接加载的优势直接加载的优势 A direct path load is faster than

7、the conventional path for the following reasons: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 reduced.A direct path load calls on Oracle to l

8、ock 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 writes to the database files.During a direct path lo

9、ad,processes perform their own write I/O,instead of using Oracles buffer cache.This minimizes contention with other Oracle users.2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28直接加载的限制直接加载的限制 Tables are not clustered.Segments to be loaded do not have any active transactions pending.2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28并行加载并行加载 Concurrent conventional path loads Intersegment concurrency with the direct path load method Intrasegment concurrency with the direct path load method2012-9-2DATAGURU专业数据分析网站2012.5.282012.5.28直接加载和索引直接加载和索引 让Oracle跑得更快1p255DATAGURU专业数据分析网站ThanksThanksFAQ时间