Home » SQL & PL/SQL » SQL & PL/SQL » procedure taking time (oracle,9.2.0.8,window)
procedure taking time [message #447923] Thu, 18 March 2010 08:29 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

Mentioned below procedure is taking more then 20 hrs. Could you please advice what could be the problem in this procedure


CREATE OR REPLACE PROCEDURE geidm.longvarchar_to_blob (
   oldtablename       IN   VARCHAR,
   newtablename       IN   VARCHAR,
   idcolumnname       IN   VARCHAR,
   oldlobcolumnname   IN   VARCHAR,
   newlobcolumnname   IN   VARCHAR
)
AUTHID CURRENT_USER
IS
   TYPE mycurtype IS REF CURSOR;

   my_cursor   mycurtype;
   clob_loc    NCLOB;
   blob_loc    BLOB;
   charbuf     NVARCHAR2 (8000);
   rawbuf      RAW (32000);
   amount      NUMBER;
   offset      NUMBER;
   LENGTH      NUMBER;
   ID          VARCHAR (50);
   counter     NUMBER           := 0;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE geidm.tempnclob (tempnclob NCLOB)';

   OPEN my_cursor FOR 'select ' || idcolumnname || ' FROM ' || newtablename;

   LOOP
      FETCH my_cursor
       INTO ID;

      EXIT WHEN my_cursor%NOTFOUND;

      --First convert the LONG VARCHAR to an NCLOB
      EXECUTE IMMEDIATE    'INSERT INTO geidm.tempnclob select TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename
                        || ' WHERE '
                        || idcolumnname
                        || ' = :1'
                  USING ID;

      --Get the clob_loc
      EXECUTE IMMEDIATE 'select tempnclob FROM geidm.tempnclob'
                   INTO clob_loc;

      --Set the empty_blob
      EXECUTE IMMEDIATE    'update '
                        || newtablename
                        || ' SET '
                        || newlobcolumnname
                        || ' = empty_blob() '
                        || ' WHERE '
                        || idcolumnname
                        || ' = :1'
                  USING ID;

      --Get the blob_loc
      EXECUTE IMMEDIATE    'select '
                        || newlobcolumnname
                        || ' FROM '
                        || newtablename
                        || ' WHERE '
                        || idcolumnname
                        || ' = :1 FOR UPDATE'
                   INTO blob_loc
                  USING ID;

      --convert the nclob to a blob
      DBMS_LOB.OPEN (blob_loc, DBMS_LOB.lob_readwrite);
      DBMS_LOB.OPEN (clob_loc, DBMS_LOB.lob_readonly);
      offset := 1;
      LENGTH := DBMS_LOB.getlength (clob_loc);

      WHILE (offset <= LENGTH)
      LOOP
         amount := 8000;
         DBMS_LOB.READ (clob_loc, amount, offset, charbuf);
         rawbuf := UTL_RAW.cast_to_raw (charbuf);
         DBMS_LOB.writeappend (blob_loc, UTL_RAW.LENGTH (rawbuf), rawbuf);
         offset := offset + amount;
      END LOOP;

      DBMS_LOB.CLOSE (blob_loc);
      DBMS_LOB.CLOSE (clob_loc);

      --delete the temporary nclob
      EXECUTE IMMEDIATE 'delete from geidm.tempnclob';

      --commit every 100 iterations to avoid exhausting rollback segments
      counter := counter + 1;

      IF (MOD (counter, 100) = 0)
      THEN
         COMMIT;
      END IF;
   END LOOP;

   CLOSE my_cursor;

   EXECUTE IMMEDIATE 'DROP TABLE geidm.tempnclob';

   COMMIT;
END;
/



Thanks,
Sagar
Re: procedure taking time [message #447925 is a reply to message #447923] Thu, 18 March 2010 08:33 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
see the performance tuning guide sticky at performance forum.

Quote:
EXECUTE IMMEDIATE 'CREATE TABLE geidm.tempnclob (tempnclob NCLOB)';

And its very Bad idea to create tables like this...

Post your explain plan
sriram Smile

[Updated on: Thu, 18 March 2010 08:36]

Report message to a moderator

Re: procedure taking time [message #447932 is a reply to message #447923] Thu, 18 March 2010 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's slow basically because you're looping through a table, and doing 5 dynamic SQL statements per row.

Do you really have so many tables to run this conversion on that you couldn't just create a procedure for each? Dynamic SQL makes the whole thing slower.

It looks like you're trying to convert a LONG column to a BLOB, but that doesn't make a lot of sense, as LONGs hold character data, and should get converted to CLOBS - can you provide a little info about what you're trying to do.

This may work (I havent got a 9.2 db to try it out on)
...
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE geidm.tempnclob (id  varchar2(50), tempblob BLOB)';

  EXECUTE IMMEDIATE    'INSERT INTO geidm.tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;

  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newcolumnname||' = (select '||oldcolumnname||
                        'from   '||oldtablename||' o '||
                        'where  n.'||idcolumnname||'= o.'||idcolumnname||')';

  EXECUTE IMMEDIATE 'DROP TABLE geidm.tempnclob';
end;




Failing that, I might be tempted to try:
CREATE OR REPLACE FUNCTION nclob_to_blob (p_nclob  in nclob) return blob as
  v_return   blob := empty_blob();
  v_length   pls_integer;
  v_offset   pls_integer := 0;
  v_amount   pls_integer;
  v_raw      raw(32000);
  v_vc       varchar2(32000);
begin
  v_LENGTH := DBMS_LOB.getlength(p_nclob);

  WHILE (v_offset <= LENGTH) LOOP
    v_amount := 8000;
    DBMS_LOB.READ (p_nclob, v_amount, v_offset, v_vc2);
    rawbuf := UTL_RAW.cast_to_raw (v_vc2);
    DBMS_LOB.writeappend (v_return, UTL_RAW.LENGTH (v_raw), v_raw);
    v_offset := v_offset + v_amount;
  END LOOP;

  return v_return;
end;


Then I'd create and populate a table as you do originally, and then run a single update to convert the NCLOB to BLOB and populate the new table.




Re: procedure taking time [message #447986 is a reply to message #447932] Thu, 18 March 2010 22:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Just some changes...
The Function will through

SQL> sho err
Errors for FUNCTION NCLOB_TO_BLOB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/3     PL/SQL: Statement ignored
11/22    PLS-00306: wrong number or types of arguments in call to 'LENGTH'
SQL>


SQL> sho err
Errors for FUNCTION NCLOB_TO_BLOB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/5     PL/SQL: Statement ignored
12/49    PLS-00201: identifier 'V_VC2' must be declared
13/5     PL/SQL: Statement ignored
13/5     PLS-00201: identifier 'RAWBUF' must be declared
SQL>


So edit that Function as per requirement and do it as suggested.

Sriram Smile
Re: procedure taking time [message #448136 is a reply to message #447986] Fri, 19 March 2010 13:17 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
I tried something like this.....

CREATE OR REPLACE PROCEDURE longvarchar_to_blob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER
IS 
   /*TYPE MyCurType IS REF CURSOR;  
   my_cursor MyCurType;
   clob_loc NCLOB;
   blob_loc BLOB;
   charbuf NVARCHAR2(8000);
   rawbuf RAW(32000);
   amount number;
   offset number;
   length number;
   id VARCHAR(50);*/
   counter number := 0;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE tempnclob (id  varchar2(50), tempnclob NCLOB)';

  EXECUTE IMMEDIATE    'INSERT INTO tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;
                        
commit;

  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select empty_blob()  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';


execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select nclob_to_blob(tempnclob)  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
     
     
   EXECUTE IMMEDIATE 'DROP TABLE tempnclob ';
   
   commit;

end;
/



CREATE OR REPLACE FUNCTION nclob_to_blob (p_nclob  in nclob) return blob as
  v_return   blob := empty_blob();
  v_length   pls_integer;
  v_offset   pls_integer := 1;
  v_amount   pls_integer;
  v_raw      raw(32000);
  v_vc       nvarchar2(32000);
   
begin

  v_LENGTH := DBMS_LOB.getlength(p_nclob);
  
  DBMS_LOB.createtemporary(v_return,true);

  WHILE (v_offset <= v_LENGTH) LOOP
    v_amount := 8000;
    DBMS_LOB.READ (p_nclob, v_amount, v_offset, v_vc);
    v_raw := UTL_RAW.cast_to_raw (v_vc);
    DBMS_LOB.writeappend (v_return, UTL_RAW.LENGTH (v_raw), v_raw);
    v_offset := v_offset + v_amount;
  END LOOP;

  return v_return;
end;
/


1. It is not inserting any data in the new table though the procedure executes successfully without any errors.
2. I suppose the update will be calling the function for each record and this may affect the performance.

Is there a better way to do this ?
My requirement would be - Old table has data in long. Convert this data and insert in New table which has datatype as CLOB.
Both tables share the common ID. Basically long varchar to CLOB conversion.

Thanks,
Ritesh
Re: procedure taking time [message #448153 is a reply to message #448136] Fri, 19 March 2010 18:15 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Since you seem to ignore and not respond to questions & comments posted by those who are trying to assist you, I won't waste my time with this thread.

Re: procedure taking time [message #448167 is a reply to message #448153] Fri, 19 March 2010 23:41 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
@BlackSwan
Apologies if i missed anything.
Implemented the suggestions to create a function as suggested by JRowBottom. Also removed the dynamic SQL and clarified on what the actual need for this code would be.
Made necessary changes as suggested by ramoradba.
As of now, also removed the creation of temporary table on the fly.

Please let me know if I have missed out on something.

Thanks,
Ritesh Confused
Re: procedure taking time [message #448168 is a reply to message #448167] Fri, 19 March 2010 23:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Implemented the suggestions to create a function as suggested by JRowBottom.

In which post did you show the results above?

>Also removed the dynamic SQL and clarified on what the actual need for this code would be.

In which post did you show the results above?

>Made necessary changes as suggested by ramoradba.

In which post did you show the results above?

>As of now, also removed the creation of temporary table on the fly.

In which post did you show the results above?

>Please let me know if I have missed out on something.
see all of the above
Re: procedure taking time [message #448173 is a reply to message #448168] Sat, 20 March 2010 01:19 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
Sorry about that. Attached all the updated codes in below post.

Test data setup :
--create source table
CREATE TABLE OBJECT
( ID   VARCHAR2(50 BYTE)  NOT NULL,  XML LONG NOT NULL);
--create target table
CREATE TABLE task
(ID   VARCHAR2(50 BYTE)  NOT NULL, XML  BLOB);
--create temp table
CREATE TABLE tempnclob (id  varchar2(50), tempnclob NCLOB);

INSERT INTO OBJECT values (1,'zzzzzz');
INSERT INTO OBJECT values (2,'ksiutgoihoasr');
INSERT INTO OBJECT values (3,'ksiutgoihoasr');
INSERT INTO OBJECT values (4,'jkadgfuqhgoihoigfhiodghoidhgoahgd');

--new table task should have the id column matching with old
INSERT INTO task(id) (select id from object);
commit;


Procedure and Function :
CREATE OR REPLACE PROCEDURE longvarchar_to_blob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER
IS 
BEGIN
  --Insert all the long values into the temp table
  EXECUTE IMMEDIATE    'INSERT INTO tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;
commit;
--Update the CLOB column in the new table with an empty_blob()
  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select empty_blob()  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
--Update the new table using data from the temp table by calling function  nclob_to_blob
execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select nclob_to_blob(tempnclob)  from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
   commit;
end;
/

CREATE OR REPLACE FUNCTION nclob_to_blob (p_nclob  in nclob) return blob as
  v_return   blob := empty_blob();
  v_length   pls_integer;
  v_offset   pls_integer := 1;
  v_amount   pls_integer;
  v_raw      raw(32000);
  v_vc       nvarchar2(32000);
   
begin
  v_LENGTH := DBMS_LOB.getlength(p_nclob);
  
  DBMS_LOB.createtemporary(v_return,true);

  WHILE (v_offset <= v_LENGTH) LOOP
    v_amount := 8000;
    DBMS_LOB.READ (p_nclob, v_amount, v_offset, v_vc);
    v_raw := UTL_RAW.cast_to_raw (v_vc);
    DBMS_LOB.writeappend (v_return, UTL_RAW.LENGTH (v_raw), v_raw);
    v_offset := v_offset + v_amount;
  END LOOP;
  return v_return;
end;
/


Results :
--before execution 
 1* select id,DBMS_LOB.getlength(xml) from task
SQL> /

ID                                                 DBMS_LOB.GETLENGTH(XML)
-------------------------------------------------- -----------------------
1                                                                      
2                                                                      
6                                                                      
4   

 1  begin
  2  longvarchar_to_blob('object','task','id','xml','xml');
  3* end;
SQL> /

PL/SQL procedure successfully completed.

 1* select id,DBMS_LOB.getlength(xml) from task
SQL> /

ID                                                 DBMS_LOB.GETLENGTH(XML)
-------------------------------------------------- -----------------------
1                                                                       12
2                                                                       26
6                                                                       26
4                                                                       66


It is working good. (I had missed synchronizing the id in both tables).

My concern : The last update statement in the procedure for newtable calls the function for each row. Considering a large dataset (say 20 to 30k records), will this statement impact the performance ?
If so, is there any alternate way to do it ?

Thanks,
Ritesh
Re: procedure taking time [message #448240 is a reply to message #448173] Mon, 22 March 2010 04:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure you can tweak the performance by removing the first update - the one setting the values to empty_blob(). Every row that the first statement updates will be overwritten by the second statement.

You're going to have to call the function for each row - there's no way round this.
Re: procedure taking time [message #448241 is a reply to message #448173] Mon, 22 March 2010 04:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You still haven't explained why you're trying to store character information in BLOB columns
Re: procedure taking time [message #448399 is a reply to message #448241] Tue, 23 March 2010 03:30 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
Is there no need to be setting up the column with an empty_blob() ? I had picked this up from an existing code wherin I was told that such columns need to be updated with an empty value so that there are no problems while reading them through reporting tool.(OBIEE and Java reports)

As regards to why I am using BLOB instead of CLOB - i guess im just blindly following instructions Sad (which obviously should not be the case)
Re: procedure taking time [message #448402 is a reply to message #448399] Tue, 23 March 2010 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've never heard of a requirement to initialise a Lob in a table, but I've never used the reporting tools youe using.
I'd be suprised if it was neccessary.

I'd check about this BLOB/CLOB thing if I were you - storing character data n a BLOB doesn't make any sense to me.

If you can use a CLOB column, then your code gets even simpler:
CREATE OR REPLACE PROCEDURE longvarchar_to_blob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER IS 
BEGIN
  --Insert all the long values into the temp table
  EXECUTE IMMEDIATE    'INSERT INTO tempnclob '||
                       ' select '||idcolumnname||', TO_LOB('
                        || oldlobcolumnname
                        || ') FROM '
                        || oldtablename;
--Update the new table using data from the temp table 
  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select tempnclob from   tempnclob  o '|| 'where  n.'||idcolumnname||'= o.'||idcolumnname||')';
   commit;
end;
/
Re: procedure taking time [message #448424 is a reply to message #448402] Tue, 23 March 2010 05:44 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
hey this works like a breeze for CLOB !

Apparently i wont even need the temporary table. I can directly update the newtable with the oldtable values using the to_lob function. Please correct me if this approach is wrong.

CREATE OR REPLACE PROCEDURE longvarchar_to_clob( oldTableName IN VARCHAR,
                                                         newTableName IN VARCHAR,
                                                         idColumnName IN VARCHAR,
                                                         oldLobColumnName IN VARCHAR,
                                                         newLobColumnName IN VARCHAR)
AUTHID CURRENT_USER IS 
BEGIN
--Update the new table using data from the oldtable. Implement the function to_lob() 
  execute immediate 
    'UPDATE '||newtablename||' n set '||
     newLobColumnName||' = (select TO_LOB(o.'|| oldlobcolumnname|| ') from '|| oldtablename ||'  o  where  n.'||idcolumnname||'= o.'||idcolumnname||')';
   commit;
end;
/
Re: procedure taking time [message #448431 is a reply to message #448424] Tue, 23 March 2010 06:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd be suprised if that works - TO_LOB isn't like most functions - it is documented only to work in INSERT statements.
Re: procedure taking time [message #448437 is a reply to message #448431] Tue, 23 March 2010 07:00 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
It actually did!
I don't know how to post the results here as im working in Toad.
But if that is what the documents say then i'll rather keep the temporary table to be on the safe side.
Re: procedure taking time [message #448439 is a reply to message #448437] Tue, 23 March 2010 07:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Interesting - I just ran some tests, and TO_LOB fails if you use it in a straight UPDATE statment, but works if you use it in a sub-query in an UPDATE statement.
Previous Topic: Function that multiple the number when there is more than 3 decimal point (mered by CM)
Next Topic: Trigger
Goto Forum:
  


Current Time: Sun Dec 04 10:31:08 CST 2016

Total time taken to generate the page: 0.05511 seconds