Re: Thinking about external tables and performance implications (Linux)

That is an interesting implementation, but it really sounds more like a use case for read only tablespaces. You cannot access external tables using indexes, which would limit the search capability I would think.

On Fri, Sep 30, 2016 at 9:00 AM, Jack Applewhite < jack.applewhite_at_austinisd.org> wrote:

> We love to use ETs as a place to dump data that "might" be needed, but
> takes up a lot of database space. Being a School District of 83,000+
> students, our main app is the Student Information System, which is
> vendor-supplied. That app has audit tables on many of the main tables.
> Those audit tables grow very large over the course of a year, but our SIS
> Support group sometimes needs to go back several months or even years to
> find out when data changed and who did it.
>
>
> So, we have a process that creates monthly archive ETs. That keeps the
> main audit tables smaller but gives SIS Support perpetual access to old
> data. We've put union views on several sets of archive tables that are
> dynamically re-created each month. They perform nicely, even with totals of
> millions of rows from, of course, unindexed ETs. The SIS Support folks
> expect tens of seconds of execution time for their queries so slowness is
> no problem.
>
>
> We also deployed a publicly-accessible PL/SQL package so our developers
> can back up and restore their tables, other objects, whole schemas, etc.
> themselves. Our package simplifies using the DBMS_Datapump supplied
> package. I published it quite a while back at my wise-guy named "blog"
>
> https://jackapplewhite.wordpress.com/
>
>
> Anyway, ETs are quite nice in keeping tablespaces leaner.
>
>
> --
> Jack C. Applewhite - Database Administrator
> Austin I.S.D. - MIS Department
> 512.414.9250 (wk)
>
> Prejudice:
> 1. Irrational, preconceived opinion that leads to preferential treatment
> of some people and unfavorable bias or hostility against others, due to
> ignorance (or in direct contradiction) of facts. ---
> BusinessDictionary.com
> 2. Deplorable. --- Me
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Mark J. Bobak <mark_at_bobak.net>
> *Sent:* Friday, September 30, 2016 7:54 AM
> *To:* christopherdtaylor1994_at_gmail.com; ORACLE-L
> *Subject:* Re: Thinking about external tables and performance
> implications (Linux)
>
>
> Hi Chris,
>
> I agree. Not a good use case for external tables. I think of external
> tables as strictly a more convenient drop in replacement for what we used
> to use SQL*Loader for. Move the data from flat file into DB. That's it.
> Making external table part of application is a bad idea. Performance and
> backup and recovery are two reasons that come to mind.
>
> -Mark
>
> On Fri, Sep 30, 2016, 08:08 Chris Taylor <christopherdtaylor1994_at_gmail.com>
> wrote:
>
>> I'm having a mini-debate (mostly with myself) about when to use Oracle
>> external tables and performance implications versus using internal Oracle
>> tables.
>>
>> We get large flat files that we're reading via external table
>> definitions. I've noticed some developers writings joins and views against
>> the external tables. The flat files beneath the external tables are
>> several million rows.
>>
>> I'm "thinking" this is not really a good use case for external tables as
>> Oracle has to access the data through the OS on ext4 filesystem and is
>> going to be quite slower than loading that data into an Oracle table with
>> appropriate indexes.
>>
>> I'm wondering if I'm overthinking this or if this is true? (That
>> performance will be handicapped when joining to external tables versus the
>> same data in an internal table).
>>
>> Thanks,
>> Chris
>>
>> Confidentiality Notice: This email message, including all attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> student and/or employee information. Unauthorized use of disclosure is
> prohibited under the federal Family Educational Rights & Privacy Act (20
> U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code
> 21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you
> may not use, disclose, copy or disseminate this information. Please call
> the sender immediately or reply by email and destroy all copies of the
> original message, including attachments.
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l