Oracle External Tables by Examples part 5 – missing features


This is maybe a bit unusual post, I almost never post about missing feature and rather focus what does exist. Although I love Oracle External tables, I must admit there are few missing features which can be easily implemented and would save a lot of time in some instances.

Please note most of the missing features below can be partially or fully patched by workarounds, I’ve done it million times before. The point I am trying to make is to have these implemented directly without any workarounds.

.

1. LOAD FIRST x

This is very simple, I would love to be able to specify to load first x for example first 2 records. This would be great in cases where you want to load for example header only.

Is there a workaround? Sure there is, but it’s a workaround and not a true solution

a. if you are lucky and the header has specific characteristic you can use filter LOAD WHEN

b. you can use REJECT LIMIT UNLIMITED – this is possible but I really do not like it, simply because you by definition expect bad records. What if the header is 5 rows and row 3 has an issue and is rejected?

c. SELECT * FROM EXTERNAL_TABLE WHERE ROWNUM < X

This is certainly possible, but SQL engine runs after the external table load (this is defined by readsize cache) and although this is somewhat working, you might still get rejects because Oracle might read 7 records instead of just 1.

d. use unix and separate header and body – this is clean solution but is extra step

.

2. LOAD LAST X

This is very similar to LOAD FIRST, this has however bigger implication. Just imagine you have 40GB file, if you use for example LOAD WHEN you will be waiting for a long time to get the trailing record. Unix file split is probably the most effective workaround here.

.

3. SKIP LAST X

External table supports SKIP X meaning skip first x records. This is great for skipping header during the load. I would love to see similar feature for skipping last X records (skipping loading trailing record). Again, there are possible workarounds, but why do we need them if it could to implement this directly to external tables?

Please share your ideas what could be added to external tables load.

Advertisements
Tagged , , ,

One thought on “Oracle External Tables by Examples part 5 – missing features

  1. Fahd Mirza says:

    Well, thanks for sharing this article. It would be great if you please publish something about physically implementing multi-terbyte data warehouse in Oracle from your experience. As how did you partitioned it and why, where you put the bitmap indexes, materialized views, stats gathering, purging etc.

    regards

    Fahd

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: