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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Mark J. Bobak <mark@xxxxxxxxx>
*Sent:* Friday, September 30, 2016 7:54 AM
*To:* christopherdtaylor1994@xxxxxxxxx; 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@xxxxxxxxx>
wrote:
I'm having a mini-debate (mostly with myself) about when to use Oraclefor the sole use of the intended recipient(s) and may contain confidential
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
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.
