CODEWITHSUNDEEP
oracle-databasestored-procedures
Eren Durmuş
Eren Durmuş

Reputation: 17

how to get better performance stored procedure oracle

I wrote a stored procedure, but I don't think it is performing at all. How can I make it work better? Thanks.

Table A has 800k records. Table B has 36k records. Is test data, there will be more records in production environment.

Table B has customer_no column index defined.

I ran it once and it took 22 minutes.

create or replace procedure      SP_KVKK
is
 TYPE json_data_type IS RECORD
(
  del_data   CLOB
);
customer_no number(10);
r_del_data                 C%ROWTYPE;
l_deleted_cur              SYS_REFCURSOR;
l_deleted_rec              json_data_type;
l_sel_sql                  VARCHAR2 (500);



cursor mbb_list is
 select customer_no from A;

begin 
   open mbb_list;
     loop
         fetch mbb_list into customer_no;
          exit when mbb_list%notfound;
        l_sel_sql := 'SELECT  JSON_OBJECT(* RETURNING CLOB) AS DEL_DATA 
        FROM B where  customer_no=' || customer_no;
        open l_deleted_cur for l_sel_sql;
        loop
            fetch l_deleted_cur into l_deleted_rec;
            exit when l_deleted_cur%notfound;                 
            r_del_data.DELETED_DOCUMENT_JSON := l_deleted_rec.del_data;
            r_del_data.DELETE_DATE := SYSTIMESTAMP;
            
            Insert into C
                values r_del_data;
        end loop;
        close l_deleted_cur;
    end loop;
    close mbb_list;
 end;

Upvotes: 0

Views: 39

Answers (1)

pmdba
pmdba

Reputation: 7043

The best thing you can do is deal with this construct as a single SQL statement. Stop thinking in terms of row-by-row nested loops which will always be slow, and look to SQL batch operations to handle this:

begin
   -- insert your two columns into table C using the data
   -- from table B returned as a json_object and systimestamp,
   -- where only records from table B that have a customer_no
   -- from table A will be selected

   insert into C (deleted_document_json, delete_date) 
   select json_object(B.* returning clob), systimestamp 
     from B 
    inner join A using (customer_no);

   commit;
end;

Upvotes: 2

Related Questions