This discussion is archived
6 Replies Latest reply: Mar 31, 2013 8:23 AM by ranit B RSS

why is the need of external tables ?

newbi_egy Explorer
Currently Being Moderated
i want to know why is the need of creating an external table if i can create regular table , and any regular table can does all the external table can does .


i can make my regular table just for querying , and no dml operations can be done on it , and i can avoid creating indexes on my regular table ,.... , then
why do i need to create an external table ?

thanks in advance
  • 1. Re: why is the need of external tables ?
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi,
    why do i need to create an external table ?
    You need external tables if you need to read data from a flat file, as if the file was just like any other table.

    A typical use case is loading of data from file into database.

    Concepts:
    http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#CNCPT1141

    Regards
    Peter
  • 2. Re: why is the need of external tables ?
    Paul M. Oracle ACE
    Currently Being Moderated
    why do i need to create an external table ?
    Example :

    One of my customers has tens of databases on different machines, and every night RMAN backup and export are done for each database. I have external tables for RMAN and export logs, and for alert log for each database, and I can control all of them from a single machine using the easy connect method and Sql commands.
  • 3. Re: why is the need of external tables ?
    Solomon Yakobson Guru
    Currently Being Moderated
    Peter Gjelstrup wrote:
    You need external tables if you need to read data from a flat file, as if the file was just like any other table.
    Yes and no. The above (btw, I assume we all are in agreement file resides/accessible from database server) describes when you can use external table, not when you need. One should weigh in how many times we will read the file before it changes. If, for example, we receive a daily file but need to reference file data in db many times a day external table is not an optimal solution.

    SY.
  • 4. Re: why is the need of external tables ?
    ranit B Expert
    Currently Being Moderated
    If, for example, we receive a daily file but need to reference file data in db many times a day external table is not an optimal solution.
    If we receive the file daily, then we can use it only once to read the data and store it in the External table. And for further reads we can check this table.
    But you said like - External table is not an optimal solution in this case.
    This is just my understanding (might be wrong)

    Could you please explain your point?
    Thanks!
  • 5. Re: why is the need of external tables ?
    jeneesh Guru
    Currently Being Moderated
    ranit B wrote:
    If, for example, we receive a daily file but need to reference file data in db many times a day external table is not an optimal solution.
    If we receive the file daily, then we can use it only once to read the data and store it in the External table. And for further reads we can check this table.
    But you said like - External table is not an optimal solution in this case.
    This is just my understanding (might be wrong)

    Could you please explain your point?
    Thanks!
    If I got it right, your understanding is wrong..

    External table data will not get stored in the DB. Each time you query the external table, ORACLE will read the file and will provide you the data in a TABLE format.. So, if you need to read the external table data multiple times- the better solution will be to store it in a staging table at the time of first read.. External table is more powerful than sqlloader.
  • 6. Re: why is the need of external tables ?
    ranit B Expert
    Currently Being Moderated
    Indeed I was wrong... Thanks Jeneesh.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points