05o海量數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)第10課數(shù)據(jù)加載

1、海量數(shù)據(jù)的設(shè)計(jì) 第十課-數(shù)據(jù)加載2012.7.2DATAGURU專業(yè)數(shù)據(jù)分析OLAP數(shù)據(jù)庫(kù)數(shù)據(jù)加載方式u sql*loader工具u OCI借口調(diào)用sql*loaderu 傳統(tǒng)的insert2012.5.282012-9-2DATAGURU專業(yè)數(shù)據(jù)分析sql*loader機(jī)制2012.5.282012-9-2DATAGURU專業(yè)數(shù)據(jù)分析sql*loader較傳統(tǒng)的數(shù)據(jù)加載方式優(yōu)勢(shì)u 批量數(shù)據(jù)加載u 更靈活的加載u 效率更高2012.5.282012-9-2DATAGURU專業(yè)數(shù)據(jù)分析sql*loader的重要參數(shù) direct=falseConventional Path Loads 常規(guī)的數(shù)

2、據(jù)加載 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專業(yè)數(shù)據(jù)分析常規(guī)數(shù)據(jù)加載的性能u Conventional Path Loads 常規(guī)的數(shù)據(jù)加載BINDSIZE設(shè)置每次提交數(shù)據(jù)的大小2012.5.282012-9-2DATAGURU專業(yè)數(shù)據(jù)分析直接加載的機(jī)制 direct=true2012.5.282012-9-2DATAGURU專業(yè)數(shù)

6、據(jù)分析直接加載的優(yōu)勢(shì)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專業(yè)數(shù)據(jù)分析直接加載的限制u Tables are not clustered.u Segments to be loaded do not have any active transactions pending.2012.5.282012-9-2DATAGURU專業(yè)數(shù)據(jù)分析并行加載u Concurrent conventional path loadsu Intersegment concurrency with the direct path load methodu Intrasegment concurrency with the