3

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 ?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
divya.trehan573
  • 454
  • 1
  • 12
  • 28

1 Answers1

3

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

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Hi @lalit kumar . How do you append in external table ? syntax ? – divya.trehan573 Dec 10 '15 at 08:19
  • @divya.trehan573 You simply do `INSERT INTO ..SELECT.. FROM external_table...` – Lalit Kumar B Dec 10 '15 at 08:20
  • @divya.trehan573 Please mark it as answered, would help others too! – Lalit Kumar B Dec 10 '15 at 08:50
  • Lalit but in sql loader we can do that in the Loader statement itself..... so whenever the control file will be executed the data will be appended.. Is that possible in external table is my question – divya.trehan573 Dec 10 '15 at 09:41
  • @divya.trehan573 You are getting confused. It is simple. An external table is not actually a database table, but a OS file in Oracle format which looks like a table. You create an external table as a copy of the data file(s) and use direct SQL on it. Once you are done, it is a temporary table like structure and not a static database table. With SQL*Loader you load data directly into a static database table. From 10g onwards, **external tables can be written to as well as read from**. So, you don't have to worry about appending rows, you just keep loading into it. – Lalit Kumar B Dec 10 '15 at 10:54
  • Hi @lalit kumar B. I got a little what you are saying but for example : OPTIONS (ROWS=1) LOAD DATA INFILE * APPEND INTO TABLE DEPT (deptno POSITION(1:1) CHAR(1)) BEGINDATA a This is how we will append into table dept when we are creating the control file itself.... so is that a possibility with the external table... or i will have to handle it in the package itself ? – divya.trehan573 Dec 10 '15 at 17:38
  • @divya.trehan573 What happens when you load multiple files into external table i.e. in your words appending the data? Does it stop you or throw any error? Did you try? You just don't need any APPEND keyword with external tables. – Lalit Kumar B Dec 11 '15 at 06:38