Oracle 11gR2加载多个文件：sqlldr还是外部表？
我有 471 个文件，总计约 100GB。这些文件以“\t”分隔，事务数据采用以下格式：
char(10) not null,
char(8) not null,
char(1) not null,
char(4) not null,
number not null,
char(1) not null,
char(1) not null,
char(1) not null,
number not null
文件中事务的顺序很重要，需要保留，最好带有主键 id。最初，我使用 sqlldr 加载这些文件，但需要很长时间。我最近了解了外部表。从战略角度来看，哪种方法更好？外部表如何工作？谢谢。
I have 471 files totaling about 100GB. The files are "\t" separate, with transaction data in the following format:
char(10) not null,
char(8) not null,
char(1) not null,
char(4) not null,
number not null,
char(1) not null,
char(1) not null,
char(1) not null,
number not null
The order of the transactions in the files is important and needs to be preserved, ideally with a primary key id. Initially, I loaded these files with sqlldr but it takes a very long time. I recently learned about external tables. From a strategic perspective, which method is better? How does the external table work? Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
外部表和SQL*Loader的记录解析非常相似，所以通常
在相同的记录格式下，没有重大的性能差异。然而，
外部表可能更适合以下情况：
但是，在以下情况下，请使用 SQL*Loader 以获得最佳加载性能：
为了提高 SQL*Loader 的性能，提出了以下建议。
加载过程中的表
使用直接路径加载器绕过大部分 RDBMS 处理
而不是传统的路径加载器。然而，也有一些情况
当你不能使用直接负载时。这些限制可以从
Oracle 服务器实用程序指南
需要扫描记录的分隔符
密集
READSIZE 将在每次读取系统调用时获取更大的数据块。这
BINDSIZE 参数指定绑定数组的大小，而该大小又
指定每批加载的行数
来源： http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/sql_loader_faq.pdf
The record parsing of External Tables and SQL*Loader is very similar, so normally
there is not a major performance difference in the same record format. However,
External Tables may be more appropriate in the following situations:
However, in the following situations, use SQL*Loader for the best load performance:
To improve the performance of SQL*Loader the following suggestions have been made.
tables during the load process
bypass most of the RDBMS processing by using the direct path loader
instead of the conventional path loader. However, there are some cases
when you can’t use direct load. These restrictions can be obtained from the
Oracle Server Utilities Guide
record needs to be scanned for the delimiter
intensive
READSIZE will grab larger chunks of data per read system call. The
BINDSIZE parameter specifies the size of the bind array, which in turn
specifies the number of rows which will be loaded per batch
Source: http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/sql_loader_faq.pdf