Re: HELP ME

Comments embedded


On 11 Jun 2006 07:14:34 -0700, "shailesh" <ShaileshGothal@xxxxxxxxx>
wrote:

Dear Friends,

I need to write a Stored Procedure in Oracle 9i which should
insert/update data from temp table to staging tables. Table structure
will be exactly same. It has to be performance tunned. Destination
table will have records upto 9 million. So performace will be a key
factor.
Regrettably as the below procedure doesn't do any bulk processing, it
will be dead slow.

Following are the design level highlights which I could think
of with very little PL/SQL knowledge.

You don't need PL/SQL. One Merge command in a sql script and you are
there.


1) Insert or Update -

We need to insert/update data from temp table to staging tables.
Following is the general logic for the same.
We need to execute update first and if SQL%ROWCOUNT < 1 then need to
execute Insert. We can not execute Insert first since we have large
number records and select to match primary key on entire table will
affect the performance.

Incorrect. If the primary key is indexed
insert ... where not exists (correlated subquery) will work as a charm

CREATE OR REPLACE procedure_name
DECLARE
cursor c1 select empno,deptno from tem_emp;
BEGIN
For c1_rec is c1 loop
UPDATE emp
SET deptno = c1_rec.deptno
WHERE empno = c1_rec.empno;

I'm not sur whether this update wouldn't already raise no data found.
And exactly how many of those updates are going to fail because you
have a new record? This approach stinks and will be slow.


If SQL%ROWCOUNT < 1 then
INSERT INTO emp VALUES (c1_rec.empno,c1_rec.deptno)
End if;
End loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_PUTPUT.PUT_LINE('Employee Number Does Not Exist');
dbms_output.put_line
WHEN INVALID_CURSOR THEN
DBMS_PUTPUT.PUT_LINE('Employee Number Does Not Exist');
WHEN DUP_VAL_ON_INDEX THEN

will not occur
DBMS_PUTPUT.PUT_LINE('Primary Key Column can not be duplicated');
WHEN OTHERS THEN
horrible. Unexpected error messages shouldn't be suppressed at all.
DBMS_PUTPUT.PUT_LINE('No valid error message');
End;
END procedure_name;

2) Exception Handling -

I have specified only predefine Exceptions above, we need to identify
any User Define Exception to be handled and then use Raise Exception
statement.

3) Delete -

For Delete condition, we assume that temp table will have some flag
field XX_flag which indicates record to be deleted. We will just update
similar flag in Staging table.


4) Commit -

Above code template does not specify Commit statement. We can not have
commit on every insert or update as this will affect the performace
with large number records.
We need to set AUTO COMMIT option at specific number of records. Infact
we can have no of records as a input parameter to SP for AUTO COMMIT.

You don't need to set autocommit, you need to commit *AFTER* the loop,
and *NOWHERE ELSE*.


5) Error handling -

We can have a Error table which will be updated when exception is
raised. So instead of DBMS_PUTPUT.PUT_LINE above we will have update to
Error Table which will help to understand which record is failed to
Insert/Update. Error table should have column like Table Name, Column
Name, Table primary key etc.


6) Recorvery -

Do we have provide recovery feature, incase the SP fails to execute at
specific time?
This will be an additional fancy feature we will be providing. This can
be implemented using AUDIT TRAIL table which will have information like
Transaction ID, Transaction Date, Transaction Status.

PLEASE HELP ME.

Regards / Shailesh

You don't need 'fancy features', you need to learn PL/SQL.
What you have so far right now, should be thrown away.
And as Jim already stated: replace this mess by a simple MERGE
statement, which will be fully equivalent.


--
Sybrand Bakker, Senior Oracle DBA
.

Relevant Pages

  • Re: HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)
  • HELP ME
    ... We need to insert/update data from temp table to staging tables. ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ...
    (comp.databases.oracle.misc)
  • Re: HELP ME
    ... We need to execute update first and if SQL%ROWCOUNT < 1 then need to ... any User Define Exception to be handled and then use Raise Exception ... Above code template does not specify Commit statement. ... Name, Table primary key etc. ...
    (comp.databases.oracle.misc)
  • Re: on exception in SPL
    ... And I include an exception handler for the -206 error that results if the table were never created which ignores the error and continues. ... What I was trying to do was keep the proc from blowing up if the temp table already existed for some reason. ... -- trace nrows; ... ordate datetime year to day, ...
    (comp.databases.informix)
  • Re: Behavior of Connection.commit()
    ... >>> If the exception from the 1st insert was a statement level exception ... If the caller wants to continue, ... >>> commit(), then they should be allowed to do so. ... >>> connection, so the caller should be able to call commitimmediately. ...
    (microsoft.public.sqlserver.jdbcdriver)
Loading