oracle海量數(shù)據(jù)架構(gòu)設(shè)計(jì)課件第10課加載

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

2、n applying inserts or updates toa nonindexed table concurrently with the loadWhen loading datao a clustered tableA direct path load does not support loading of clustered tables.When loading a relatively small number of rowso a large indexed tableDuring a direct path load, the existing index is copie

3、d 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 savedby a direct path load.When loading a relatively small number of rowso a large table with referential and column-checkegrity cons

4、trasWhen loading records and you want to ensuret a record is rejected under any of the followingcircumstan:If the record, upon insertion, causes an Oracle errorIf the record is formatted incorrectly, sot SQL*Loader cannot find field boundariesIf the record violates a constraor tries to make a unique

5、 index non-unique2012.5.282012-9-2常規(guī)數(shù)據(jù)加載的性能Conventional Path Loads 常規(guī)的數(shù)據(jù)加載BINDSIZE設(shè)置每次提交數(shù)據(jù)的大小2012.5.282012-9-2直接加載的機(jī)制 direct=true2012.5.282012-9-2直接加載的優(yōu)勢(shì)A direct path load is fasterreasons:n the conventional path for the followingPartial blocks are not used, so no reads are needed to find them, and

6、fewerwrites are performed.SQL*Loader need not execute any SQL INSERT sements; therefore, theprosing load on the Oracle database is reduced.A direct path load calls on Oracle to lock tables and indexes at the start of theload and releases them when the load is finished. A conventional path loadcalls

7、Oracle once for each array of rows to pros a SQL INSERT sement.A direct path load uses multiblock asynchronous I/O for writes to the databasefiles.During a direct path load, proses perform their own write I/O, instead ofusing Oracles buffer cache. This minimizes contention with other Oracle users.2012.5.282012-9-2直接加載的限制Tables are not clustered.Segments to be loaded do nove any active tranions pending.2012.5.282012-9-2并行加載Concurrent conventional path loadsersegment concurrency with the direct path load meth