Re: External table performance

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: kibeha@xxxxxxxxx
  • Date: Sun, 14 Jan 2018 13:27:46 +0000

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@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 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@xxxxxxxxx
@kibeha <http://twitter.com/kibeha>



On Fri, Jan 12, 2018 at 9:29 PM, Sanjay Mishra <
dmarc-noreply@xxxxxxxxxxxxx> 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@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


------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx <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.

Tx
Sanjay





-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: