External Table

从12.2版本外部表可以为分区表，同样享受分区表带来的性能优势（如partition pruning）

As of Oracle Database 12c Release 2 (12.2.0.1), you can partition data contained in external tables, which allows you to take advantage of the same performance improvements provided when you partition tables stored in a database (for example, partition pruning).

创建外部表使用ORGANIZATION EXTERNAL 子句：

TYPE — specifies the type of external table. Each type of external table is supported by its own access driver.

Oracle_loader不能导出，但oracle_datapump可以在创建外部表时把数据导出到dumpfile，但之后不能进行DML

1. ORACLE_LOADER — this is the default access driver. It loads data from external tables to internal tables. The data must come from text data files. (The ORACLE LOADER access driver cannot perform unloads; that is, it cannot move data from an internal table to an external table.)
2. ORACLE_DATAPUMP — this access driver can perform both loads and unloads. The data must come from binary dump files. Loads to internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the binary dump files of the external table. The ORACLE_DATAPUMP access driver can write dump files only as part of creating an external table with the SQL CREATE TABLE AS SELECT statement. Once the dump file is created, it can be read any number of times, but it cannot be modified (that is, no DML operations can be performed).
3. ORACLE_HDFS — extracts data stored in a Hadoop Distributed File System (HDFS).
4. ORACLE_HIVE — extracts data stored in Apache HIVE.

DEFAULT DIRECTORY — specifies the default directory to use for all input and output files that do not explicitly name a directory object.

ACCESS PARAMETERS — Access parameters are optional.不同的type有自己的access parameters，相关选项分开介绍了

LOCATION — specifies the data files for the external table.

For ORACLE_LOADER and ORACLE_DATAPUMP, the files are named in the form directory:file. The directory portion is optional. If you are using the ORACLE_LOADER access driver, then you can use wildcards in the file name: an asterisk (*) signifies multiple characters, a question mark (?) signifies a single character.

For ORACLE_HDFS, the LOCATION clause is a list of Uniform Resource Identifiers (URIs) for a directory or for a file. There is no directory object associated with a URI.

For ORACLE_HIVE, the LOCATION clause is not used. Instead, the Hadoop HCatalog table is read to obtain information about the location of the data source (which could be a file or another database).