oracle 外部表与 SQLLoader
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1771684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
External Tables vs SQLLoader
提问by moleboy
So, I often have to load data into holding tables to run some data validation checks and then return the results. Normally, I create the holding table, then a sqlldr control file and load the data into the table, then I run my queries. Is there any reason I should be using external tables for thing instead? In what way will they make my life easier?
因此，我经常不得不将数据加载到保存表中以运行一些数据验证检查，然后返回结果。通常，我先创建保持表，然后创建 sqlldr 控制文件并将数据加载到表中，然后运行查询。有什么理由我应该使用外部表来代替吗？它们会以何种方式让我的生活更轻松？
回答by APC
The big advantage of external tables is that we can query them from inside the database using SQL. So we can just run the validation checks as SELECT statements without the need for a holding table. Similarly if we need to do some manipulation of the loaded data it is almost always easier to do this with SQL rather than SQLLDR commands. We can also manage data loads with DBMS_JOB/DBMS_SCHEDULER routines, which further cuts down the need for shell scripts and cron jobs.
外部表的一大优点是我们可以使用 SQL 从数据库内部查询它们。因此，我们可以将验证检查作为 SELECT 语句运行，而无需持有表。同样，如果我们需要对加载的数据进行一些操作，使用 SQL 而不是 SQLLDR 命令几乎总是更容易做到这一点。我们还可以使用 DBMS_JOB/DBMS_SCHEDULER 例程管理数据加载，这进一步减少了对 shell 脚本和 cron 作业的需求。
However, if you already have a mature and stable process using SQLLDR then I concede it is unlikely you would realise tremendous benefits from porting to external tables.
但是，如果您已经有一个成熟且稳定的使用 SQLLDR 的过程，那么我承认您不太可能从移植到外部表中获得巨大的好处。
There are also some cases - especially if you are loading millions of rows - where the SQLLDR approach may be considerably faster. Howver, the difference will not be as marked with more recent versions of the database. I fully expect that SQLLDR will eventually be deprecated in favour of external tables.
还有一些情况——特别是当你加载数百万行时——SQLLDR 方法可能要快得多。但是，该差异不会与更新版本的数据库相同。我完全希望 SQLLDR 最终会被弃用，取而代之的是外部表。
回答by DCookie
If you look at the External Table syntax, it looks suspiciously like SQL*Loader control file syntax :-)
如果您查看外部表语法，它看起来很像 SQL*Loader 控制文件语法 :-)
If your external table is going to be repeatedly used in multiple queries it might be faster to load a table (as you're doing now) rather than rescan your external table for each query. As @APC notes, Oracle is making improvements in them, so depending on your DB version YMMV.
如果您的外部表将在多个查询中重复使用，则加载表（如您现在所做的那样）可能会更快，而不是为每个查询重新扫描外部表。正如@APC 所指出的，Oracle 正在对它们进行改进，因此取决于您的数据库版本 YMMV。
回答by David Aldridge
I would use external tables for their flexibility.
我会使用外部表的灵活性。
It's easier to modify the data source on them to be a different file alter table ... location ('my_file.txt1','myfile.txt2')
将它们上的数据源修改为不同的文件更容易 alter table ... location ('my_file.txt1','myfile.txt2')
You can do multitable inserts, merges, run it through a pipelined function etc...
您可以执行多表插入、合并、通过流水线函数运行等...
Parallel query is easier ...
并行查询更容易...
It also establishes dependencies better ...
它还可以更好地建立依赖关系......
The code is stored in the database so it's automatically backed up ...
代码存储在数据库中，因此它会自动备份......
回答by BobC
Another thing that you can do with external tables is read compressed files. If your files are gzip compressed for example, then you can use the PREPROCESSOR directive within your external table definition, to decompress the files as they are read.
您可以对外部表做的另一件事是读取压缩文件。例如，如果您的文件是 gzip 压缩的，那么您可以在外部表定义中使用 PREPROCESSOR 指令，在读取文件时对其进行解压缩。
