Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 15, 2014 - 6:37 pm UTC

Last updated: April 18, 2014 - 8:59 pm UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

What is the best practice as far as processing an input file in PL/SQL? Is it best to place all the records in an array first and then loop through the array? Or is it just as well to process each record as you read it in from the input file? The number of records per file will vary from just a few records to several thousand records.

and Tom said...

The best practice will be to use an external table!!!!!!


And then a simple:

for x in (select * from external-table)
loop
    .....


will naturally array fetch 100 records at a time. If your goal is to LOAD the data, a simple:

insert /*+ append */ into table select * from external-table;


will direct path load for you. If you need to transform the data using procedural code, you would either

a) realize that 99% of procedural code, when you really think about it, can be expressed as a nice bit of set based processing - and you discard the procedural code and just use a SQL statement - exploiting the full power of SQL

b) use a pipelined function so that you can:

insert /*+ append */ into table
select * from table( your_plsql_pipelined_function_here );


like this:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:19481671347143



do not write "1980's" code that reads a record, processes a record, writes a record - use the database, use sets!

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 15, 2014 - 10:40 pm UTC

The prompt response is much appreciated. I hear you loud and clear. I just needed to hear from an expert.

And I will leave the 80s, 90s, and Rock n Roll where they belong...

External table and direct path load

Alex Soriano, April 18, 2014 - 6:38 am UTC

Hi Tom,

We have been working in a very similar loading process; we create an external table, pre-process the file (oracle 11G) and unzip the information, load a log table and then load the table using

The log table is used as we need to check that a file is load only once, and we create a sequence for the file that we area loading; and create an PK on such sequence.

Then we load the table using

insert /*+ append */ into table select seq_vql_var, e.* from external-table e;

What we have found is that if we have an FK from the information_table to the log_table the information is not compress, but if we drop the FK constraint the information does compress.

Any suggestion how to check if direct path is used in this scenario, and how to make sure that direct path is used so that the data is compress. As we can do an alter table move during initial loading (aprox 200 GB) but not on daily basis.



Tom Kyte
April 18, 2014 - 8:59 pm UTC

direct path loads cannot be done with an enabled foreign (or triggers).

you could test that direct pathing is available with;

ops$tkyte%ORA11GR2> create table t2 ( x int primary key );

Table created.

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2> insert /*+ append */ into t select 1 from dual;

1 row created.

ops$tkyte%ORA11GR2> select count(*) from t where 1=0;
select count(*) from t where 1=0
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t add constraint t_fk_to_t2 foreign key (x) references t2(x);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert /*+ append */ into t select 1 from dual;
insert /*+ append */ into t select 1 from dual
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.T_FK_TO_T2) violated - parent key
not found


ops$tkyte%ORA11GR2> select count(*) from t where 1=0;

  COUNT(*)
----------
         0

ops$tkyte%ORA11GR2> rollback;

Rollback complete.



you are expecting the select count(*) to fail, if it does not - it did not direct path - you had a foreign key or a trigger in place.


if you are loading on a daily basis, you should be using partitioning (to enable purging later) but also to allow you to manage things in a bit sized chunk.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions