Restrictions on External Tables

Certain operations on external tables are not supported or have limited scope.

Database Tables and External Tables compares table operations that are supported for database tables and external tables.

Table 1. Database Tables and External Tables
Table Operation	Database Table	External Table
Support for indexes and: Primary keys Foreign keys Unique and non-unique indexes Index scans Automatic index (autoindex) during query execution Index join	Yes	No, sequential scans are used.
Triggers are supported	Yes	No
Table can be a target in a MERGE statement	Yes	No. Not allowed as target but allowed as source. See MERGE Example
Table fragmentation is supported	Yes	No
Multiple database tables are allowed in the FROM clause	Yes	No. See Query Example
DB-Access LOAD FROM ... INSERT INTO statement is supported	Yes	No
The TRUNCATE TABLE statement truncates a table	Yes	No. Data in external tables is not truncated using the TRUNCATE statement. Unloading data from a database table to an external table automatically truncates the external table.
Table data is replicated	Yes	No
The UPDATE STATISTICS statement is supported	Yes	No
UPDATE and DELETE statements are supported	Yes	No
The ALTER TABLE statement is supported	Yes	No
LBAC is supported	Yes	No
Compression is supported	Yes	No
START and STOP VIOLATIONS statements supported	Yes	No
TEMP tables are supported	Yes	No
The EXTERNAL data type is supported for table columns	No	Yes
DEFAULT clause is supported	Yes	No
PUT clause is supported for BLOB and CLOB types	Yes	No. BLOBDIR and CLOBDIR can be specified using the DATAFILES clause.
SERIAL, SERIAL8, and BIGSERIAL data types generate serial numbers	Yes	No. These data types are converted to equivalent integer types and no serial value is generated.
Table can be replicated using Enterprise replication (ER)	Yes	No
Changes to tables are logged and can be replicated	Yes	No. External tables are not logged and cannot be replicated; however system catalogs are replicated.
ACID (atomicity, consistency, isolation, durability) properties are supported	Yes	No
ETL (extract, transform, load) is supported	SQL interface for ETL operations is not supported; however, utilities such as HPL, dbload, onload, onunload and LOAD, UNLOAD statements are supported.SQL interface for ETL operations is not supported; however, utilities such as dbload, onload, onunload and LOAD, UNLOAD statements are supported.	Supported using a simple SQL interface using the INSERT ... SELECT statement for high performance loading and unloading of data.

Certain high-availability cluster operations are not supported (see External Tables in High-Availability Cluster Environments in the HCL OneDB™ Administrator's Guide).

To load BLOB or CLOB objects from an external table, you must create a temporary sbspace and create temporary smart large objects in that space to store the BLOB or CLOB data from the external table. Loading BLOB or CLOB data from a read-only secondary server is not supported, because you cannot create a temporary smart large object on a read-only secondary server.

MERGE Example

An external table cannot be the target of the MERGE statement. For example, if ext is an external table, the following MERGE statement is valid with ext as the source table:

MERGE INTO t1
     USING ext ON t1.c1 = ext.c1
    WHEN MATCHED THEN UPDATE 
     SET t1.c2 = ext.c2
    WHEN NOT MATCHED THEN INSERT VALUES (99, '999');

The following statement, however, fails with ext as the target table:

  MERGE INTO  ext
     USING t1 ON ext.c1 = t1.c1
    WHEN MATCHED THEN UPDATE
     SET ext.c2 = t1.c2
    WHEN NOT MATCHED THEN INSERT VALUES (99, '999');

Query Example

Only the outermost query can have an external table reference. Only one external table can be specified in any query. For example, the following statement is allowed:

     SELECT * FROM ext, t2 WHERE ext.c1 = t2.c1;

However, the following statements are not allowed:

Multiple external tables cannot be specified within a query:
```
  SELECT * FROM ext, ext3 WHERE ext.c1 = ext3.c1;
```

An external table cannot be used in a subquery:

   SELECT * FROM t1 WHERE t1.c1  IN (SELECT c1 FROM ext);