Re: External tables

So if a couple of lines in the file acquired some dirty data (e.g. a tab character pushes a line out of position) there are two possible options -

1. The query crashes because the external table definition does not allow bad data.
2. The query returns the wrong answer because the external table definition allows for an unlimited number of errors, so the line is ignored.

Historically, your client may have had code to alert an administrator at load time, so that the data could be checked and cleaned before the user accessed it.

I've found a good use for external tables, so I have nothing against the concept per se - however, I do think it is important to throw a little cold water around when people suggest that an external table definition is a substitute for a sql load. In many cases the effect of the external table is to move the trivial part of the load into database.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 25 April 2003 00:17

>
The client
> I'm referring to simply kept the same file name each time, and the overhead
> of loading the material went down from occurring every hour to occurring
> only when the table was queried. Of course, there's the issue that the
> overhead of the load now occurred at the time of the query, but for this
> particular customer that wasn't so much of a concern. >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).