改善sql loader的性能

king71

　　General Performance Improvement Hints
　　This section provides a few guidelines that can help to improve the performance of a load. If you must use a certain feature to load your data, by all means do so. But if you have control over the format of the data to be loaded, you can use the following hints to improve load performance:
这节给出了几个常见的方针帮助我们改善sql loader的性能。如果你必须使用一个确定的特征来导入数据，那么就不用考虑这些了。如果你能控制你要导入的数据的格式，你可以使用下面的hint来改善性能。
　　Make logical record processing efficient:
让逻辑数据处理高效：
Use one-to-one mapping of physical records to logical records (avoid continueif, concatenate)
让物理数据和逻辑一对一的映射(避免使用continueif和concatenate)
　　Make it easy for the software to figure out physical record boundaries. Use the file processing option string "FIX nnn" or "VAR." If you use the default (stream mode) on most platforms (for example, UNIX and NT) the loader must scan each physical record for the record terminator (newline character).
让sql loader容易找到物理纪录的边界。使用FIX和VAR来描述数据的格式。因为如果你用default的模式来导入数据，大部分的平台下，sql loader需要扫描每条物理纪录来找寻换行符。
　　Make field setting efficient. Field setting is the process of mapping fields in the datafile to their corresponding columns in the table being loaded. The mapping function is controlled by the description of the fields in the control file. Field setting (along with data conversion) is the biggest consumer of CPU cycles for most loads.
让field设置高效。field设置是将datafile的field映射到表的相应的列的处理过程，这个处理过程被control file中的描述来控制，这个过程是整个导入过程中最消耗CPU的。
　　Avoid delimited fields; use positional fields. If you use delimited fields, the loader must scan the input data to find the delimiters. If you use positional fields, field setting becomes simple pointer arithmetic (very fast).
避免分隔符fields，使用positional fields。如果你使用分隔符field，loader需要扫描输入数据来找到分隔符，如果使用positional fields，这个将会非常快。
　　Do not trim whitespace if you do not need to (use PRESERVE BLANKS).
不要用trim空格，如果必需的话，用preserve blanks.
　　Make conversions efficient. SQL*Loader performs character set conversion and datatype conversion for you. Of course, the quickest conversion is no conversion.
让转换高效。sql loader会自动地执行字符集转换和数据类型转换，所以我们应该避免或者减少这样的转换。
　　Avoid character set conversions if you can. The loader supports four character sets:
a) client character set (NLS_LANG of the client sqlldr process);
b) datafile character set (usually the same as the client character set);
c) server character set; and
d) server national character set.
Performance is optimized if all character sets are the same. For direct path loads, it is best if the datafile character set and the server character set are the same. If the character sets are the same, character set conversion buffers are not allocated.
避免字符集的转换。loader支持四种字符集：
a) client character set (NLS_LANG of the client sqlldr process);
b) datafile character set (usually the same as the client character set);
c) server character set; and
d) server national character set.
这四种字符集都一样的时候性能最优。direct path loads的时候，如果datafile character set和server character set一样的时候是最好的。如果字符集一样，转换的缓冲区不会被分配。
　　Use single-byte character sets if you can.
尽量使用单字节的字符集。
　　Use direct path loads.
使用direct path loads
　　Use the SORTED INDEXES clause.
使用SORTED INDEXES。
　　Avoid unnecessary NULLIF and DEFAULTIF clauses. Each clause must be evaluated on each column that has a clause associated with it for every row loaded.
避免不必要的NULLIF和DEFAULTIF。因为每条数据都需要评估每一列。
　　Use parallel direct path loads and parallel index create when you can.
尽量使用并行的direct path loads和parallel index。