Re: [Import] CSV file

Richard wrote:
>
> Does anybody have an opinion on the performance of external tables when
> compared with normal (internal ?) tables? Is it still worth writing
> SQL*Loader scripts to import structured text files?
>

Look at the syntax needed to create an external table.

One of the first things you are likely to notice is 'TYPE ORACLE_LOADER'. The second thing you might notice is that the syntax for mapping between the file and the table structure is straight out of the Utilities manual, SQL*Loader section, including BAD FILE and LOG FILE.

Basically, it appears that (for now) External tables' are read using a part of the SQL Loader engine. From a warehouse perspective, this make it an ideal staging table.

Since you can't create indexes or do DML with external tables, gut feeling says the one would not want to use external tables in the same way as a regular indexable table. That is, I would not want to use it for heavily repeated SELECTS.

But as the source for an upsert (oops, sorry, MERGE) or pivot insert, I'd think this would be more efficient, and certainly has at least one fewer step, than the traditional 'copy file, load, access database. select' sequence.

/Hans Received on Sat Nov 15 2003 - 20:19:38 CST