Re: External table performance

  • From: Lothar Flatz <l.flatz@xxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sun, 14 Jan 2018 14:47:29 +0100

Agreed. Data that is repetitively queried should not be kept in an external table.

Am 14.01.2018 um 14:27 schrieb Niall Litchfield:

I *think* I'd take the approach of treating this as a WH load job and run something like


Staging table can and should be indexed as required.

Depending on the nature of the data in the external tables and the requirements around old data you might well have yet another real, partitioned data and post-load you would partition exchange into a new partition, but the base argument, like Kims, is get the data into the database where the normal power of the dbms engine can be appropriately used.

There are also enhancements to external tables in 12.2 that might be worth looking at.

On Sun, Jan 14, 2018 at 9:24 AM, Kim Berg Hansen <kibeha@xxxxxxxxx <mailto:kibeha@xxxxxxxxx>> wrote:

    Hello, Sanjay

    You write the external table is extensively used and indexes could
    be helpful. You've thought about MVs, and I would suggest you try
    The "problem" with the external table in your case seems that you
    often query just "some rows" (particularly a given
    state/county/period), but querying an external table has to read
    the entire file every time you query it.

    What I often do for such situations is something like this:

    - Create external table.
    - Create materialized view REFRESH COMPLETE ON DEMAND as select *
    from external table.
    - Create desired index(es) on state, county, period, whatever you
    need on the mview.
    - In the procedure or script or whatever updates a new file,
    include a call to DBMS_REFRESH.REFRESH to do a complete refresh of
    the mview right after the new file is in place.
    - Use the mview in all your queries. (In this case since you have
    existing queries, perhaps rename the external table to something
    else and give the mview the original name of the ext.table.)

    I've used this method often. For example we had a case of having
    to retrieve datafiles from a datasupplier regularly via FTP.
    I wrote a procedure that used FTP_UTIL_PKG (from Tim Hall,
    ORACLE-BASE) to retrieve the files and then call DBMS_REFRESH.REFRESH.
    This procedure was then called periodically by scheduler (or
    DBMS_JOB) and that way the mview was continually refreshed with
    the content of the files from the FTP server.
    And all our queries used the mview that had suitable indexes on it.

    Just a suggestion :-)



    Kim Berg Hansen
    Oracle ACE
    kibeha@xxxxxxxxx <mailto:kibeha@xxxxxxxxx>
    @kibeha <>

    On Fri, Jan 12, 2018 at 9:29 PM, Sanjay Mishra
    <dmarc-noreply@xxxxxxxxxxxxx <mailto:dmarc-noreply@xxxxxxxxxxxxx>>


        Oracle 12.1.2 and it is Single Node RAC instance and is
        Enterprise Edition. The issue with Select is where Clause for
        2-3 column where Index can definitely be helpful but External
        table dont allow index. Select was Tried with Parallel but no
        difference in terms of performance. One option trying is MV
        and as data is not much but table is extensively used for
        various purpose.


        On Friday, January 12, 2018, 2:29:48 PM EST, Powell, Mark
        <mark.powell2@xxxxxxx <mailto:mark.powell2@xxxxxxx>> wrote:

        Sanjay, you did not identify your Oracle version and edition
        nor post the actual code you have an issue with but consider
        the following.

        I do not have access to a server where I can test right now
        but I believe that you can select from an external table using
        parallel query if you are on EE.

        Mark Powell
        Database Administration
        (313) 592-5148 <tel:%28313%29%20592-5148>

        *From:* oracle-l-bounce@xxxxxxxxxxxxx
        <mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf of Sanjay
        Mishra <dmarc-noreply@xxxxxxxxxxxxx
        *Sent:* Friday, January 12, 2018 2:23:40 PM
        *To:* Oracle-L Freelists
        *Subject:* External table performance
        Can someone share the Tuning activites that can be done for
        Selecting data from External table in 12c (12.1)? I am working
        on project that is getting every 6hourly data as flat file
        from external source and then has to be selected from ORacle
        table for some processing. Data is only 50K records but Select
        is taking time. It is Local Storage where File is located. I
        don;t think we can have indexes yet and so checking experience
        from the experts.


Niall Litchfield
Oracle DBA


Other related posts: