Loading a Regular Table from the External Table- External Tables

Now, you can load data contained in the external table into a regular database table. When you do this, you can take advantage of Oracle’s direct-path loading and parallel features.

This example creates a regular database table that will be loaded with data from the external table:

SQL> create table exa_info( exa_id NUMBER ,machine_count NUMBER ,hide_flag        NUMBER,oracle      NUMBER,ship_date ,rack_type
DATE VARCHAR2(32)) nologging parallel 2;

You can direct-path load this regular table (via the APPEND hint) from the contents of the external table that was listed in the dba_external_tables view, as follows:

SQL> insert /*+ APPEND */ into exa_info select * from exadata_et;

You can verify that the table was direct-path loaded by attempting to select from it before you commit the data:

SQL> select * from exa_info;

After you commit the data, you can select from the table:

SQL> commit;

SQL> select * from exa_info;

Conversion errors may appear when reading or writing data with external tables. Conversion of numbers to dates or to character fields should be recognized, but when receiving these errors, it is possible to explicitly create the conversion in the statements. Using TO_NUMBER, TO_DATE, and TO_CHAR will help to avoid these issues if the conversion is not made implicitly.

The other way to direct-path load a table is to use the CREATE TABLE AS SELECT (CTAS) statement. A CTAS statement automatically attempts to do a direct-path load. In this example, the EXA_INFO table is created and loaded in one statement:

SQL> create table exa_info nologging parallel 2 as select * from exadata_et;

By using direct-path loading and parallelism, you can achieve loading performance similar to that of SQL*Loader.

The advantage of using SQL to create a table from an external table is that you can perform complex data transformations using standard SQL features when building your regular database table (EXA_INFO, in this example).

Any CTAS statements automatically process with the degree of parallelism that has been defined for the underlying table.

However, when you use INSERT AS SELECT statements, you may need to either use the statement-level hint ENABLE_PARALLEL_DML or enable parallelism for the session:

SQL> alter session enable parallel dml;

As a last step, you should generate statistics for any table that has been loaded with a large amount of data. Here is an example:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘SYS’, tabname => ‘EXA_ INFO’, cascade => true);