Re: External table performance

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
>
> INSERT INTO STAGING_TABLE SELECT * FROM EXTERNAL_TABLE;
> COMMIT;
>
> 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_at_gmail.com
> <mailto:kibeha_at_gmail.com>> 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
> that.
> 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 :-)
>
>
> Cheerio
> /Kim
>
>
> Regards
>
>
> Kim Berg Hansen
> Oracle ACE
>
> http://www.kibeha.dk
> kibeha_at_kibeha.dk <mailto:kibeha_at_kibeha.dk>
> _at_kibeha <http://twitter.com/kibeha>
>
>
>
> On Fri, Jan 12, 2018 at 9:29 PM, Sanjay Mishra
> <dmarc-noreply_at_freelists.org <mailto:dmarc-noreply_at_freelists.org>>
> wrote:
>
> Mark
>
> 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.
>
> Sanjay
>
> On Friday, January 12, 2018, 2:29:48 PM EST, Powell, Mark
> <mark.powell2_at_dxc.com <mailto:mark.powell2_at_dxc.com>> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>> on behalf of Sanjay
> Mishra <dmarc-noreply_at_freelists.org
> <mailto:dmarc-noreply_at_freelists.org>>
> *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.
>
> Tx
> Sanjay
>
>
>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info

-- 





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