Ed Jennings wrote:
>
> I have a table that contans 20+ million rows. I need to perform
> an edit check on two columns, to verify data integrity. I can't
> do it at load time because of data latency in loading the lookup
> table. At the time of usage, I want to perform this check. I wrote
> a stored procedure, but it takes hours. Using the trace facility
> I determined that the SP is doing one fetch per row. Is there a way
> to use array processing within the SP, or do I need to search for
> an alterntive? Does anyone have any suggestions?
>
> Ed Jennings
> Oracle DBA
> EPS, Inc.
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> jennings_at_dca.net
- No. PL/SQL does not support array processing.
- Try defining the checks as one or more table- or column-level
constraints with the DISABLE option set during table creation.
Then ENABLE the constraint after loading the data. This will be much
more efficient (and uses set processing, which is even more efficient
than array processing). It also has the advantage that you can trap the
rows in error in a system table, which can then be used as the target
table for a repair utility written, for example, in FORMS.
Even if you have some already validated data in the table before you add
the new rows, it will be more efficient to DISABLE the constraint(s)
before loading the new rows and to re-ENABLE the constraint(s)
afterwards, provided you are loading more than, say, 20% new rows.
- If your constraints cannot be so specified, then you will have to use
a host program.
Received on Tue Feb 18 1997 - 00:00:00 CST
