Re: External table performance

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.)

http://www.kibeha.dk
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> 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> 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
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Sanjay Mishra <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
>

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