Search code examples
oraclesql-loaderexternal-tables

External table limitations

Now i am working on external tables... While i do like its flexibility. I would like to know these things about external table -

  1. Like in SQL Loader we can append data to the table . Can we do that in External table ?

  2. In external table , we cannot create indexes neither can we perform DML operations. Is this kind of virtual table or this acquires space in the data base ?

  3. Also in SQL loader we can access the data from any server in external table we define the default directory. Can we in turn do the same in external table that is access the data from any server ?

Solution

  • External tables allow Oracle to query data that is stored outside the database in flat files as though the file were an Oracle table.

    The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

    You asked:

    1. like in SQL Loader we can append data to the table . Can we do that in External table ?

    Yes.

    1. In external table , we cannot create indexes neither can we perform DML operations. Is this kind of virtual table or this acquires space in the data basE ?

    As the name suggests, it is external to the database. You use ORGANIZATION EXTERNAL syntax. The directory is created at OS level.

    1. Also in SQL loader we can access teh data from any server in external table we define the DEfault directory. Can we in turn do the same in external table that is access the data from any server ?

    This is wrong. SQL*Loader is a client-side tool, while external table is a server-side tool. External Table can load file which is accessible from database server. You can't load External Table from file residing on your client. You need to save the files to a filesystem available to the Oracle server.

    Prior to version 10g, external tables were READ ONLY. DML could not be performed. Starting with version Oracle Database 10g, external tables can be written to as well as read from.

    From documentation, also read Behavior Differences Between SQL*Loader and External Tables