Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Unanswered: Speed up a stored procedure

    Hello,

    I have a stored proc that Update a table
    based on a match between 2 columns

    Is there a way to speed up a process
    because it takes about 30 mi to update a table


    --Update to get a CXIX --1-12
    is
    ddl_str varchar2(4000);

    begin

    --Update to get a--1-12

    ddl_str := 'UPDATE T_DATA EU SET (EU.CXIX) =
    (SELECT A.CXIX FROM C_RIX A
    WHERE substr(A.CIX,1,12) = substr(EU.B_LOG,1,12)
    and rownum = 1)
    WHERE substr(EU.B_LOG,1,12)
    IN (SELECT substr(A.CIX,1,12) FROM C_RIX A)' ;
    apadmin.ddl_exec(ddl_str);
    ddl_str := 'commit';
    apadmin.ddl_exec(ddl_str);

    --Update to get --1-11

    ddl_str := 'UPDATE T_DATA EU SET (EU.CXIX) =
    (SELECT A.CXIX FROM C_RIX A
    WHERE substr(A.CIX,1,11) = substr(EU.B_LOG,1,11)
    and rownum = 1)
    WHERE substr(EU.B_LOG,1,11)
    IN (SELECT substr(A.CIX,1,11) FROM C_RIX A)' ;
    apadmin.ddl_exec(ddl_str);
    ddl_str := 'commit';
    apadmin.ddl_exec(ddl_str);


    --Update to get --2-10

    ddl_str := 'UPDATE T_DATA EU SET (EU.CXIX) =
    (SELECT A.CXIX FROM C_RIX A
    WHERE substr(A.CIX,2,10) = substr(EU.B_LOG,1,10)
    and rownum = 1)
    WHERE substr(EU.B_LOG,1,10)
    IN (SELECT substr(A.CIX,2,10) FROM C_RIX A)' ;
    apadmin.ddl_exec(ddl_str);
    ddl_str := 'commit';
    apadmin.ddl_exec(ddl_str);

    end ;

    Thank you.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Speed up a stored procedure

    First of all, there is no reason to use dynamic SQL here:
    PHP Code:
    begin

       
    --Update to get a--1-12

    UPDATE T_DATA EU SET 
    (EU.CXIX) =
       (
    SELECT A.CXIX FROM C_RIX A
         WHERE substr
    (A.CIX,1,12) = substr(EU.B_LOG,1,12
           and 
    rownum 1)
     
    WHERE substr(EU.B_LOG,1,12IN (SELECT substr(A.CIX,1,12FROM C_RIX A);

    commit;

       --
    Update to get --1-11

    UPDATE T_DATA EU SET 
    (EU.CXIX) =
       (
    SELECT A.CXIX FROM C_RIX A
         WHERE substr
    (A.CIX,1,11) = substr(EU.B_LOG,1,11
           and 
    rownum 1)
     
    WHERE substr(EU.B_LOG,1,11IN (SELECT substr(A.CIX,1,11FROM C_RIX A) ;

    commit;


       --
    Update to get --2-10

    UPDATE T_DATA EU SET 
    (EU.CXIX) =
       (
    SELECT A.CXIX FROM C_RIX A
         WHERE substr
    (A.CIX,2,10) = substr(EU.B_LOG,1,10
           and 
    rownum 1)
     
    WHERE substr(EU.B_LOG,1,10IN (SELECT substr(A.CIX,2,10FROM C_RIX A);

    commit;

    end 
    Now, your use of SUBSTR is preventing any use of indexes on the columns. If you can't avoid the SUBSTRs you may want to consider creating function-based indexes e.g.

    create index fbi1 on C_RIX (substr(CIX,2,10) );

  3. #3
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Re: Speed up a stored procedure

    Originally posted by andrewst
    First of all, there is no reason to use dynamic SQL here:
    PHP Code:
    begin

       
    --Update to get a--1-12

    UPDATE T_DATA EU SET 
    (EU.CXIX) =
       (
    SELECT A.CXIX FROM C_RIX A
         WHERE substr
    (A.CIX,1,12) = substr(EU.B_LOG,1,12
           and 
    rownum 1)
     
    WHERE substr(EU.B_LOG,1,12IN (SELECT substr(A.CIX,1,12FROM C_RIX A);

    commit;

       --
    Update to get --1-11

    UPDATE T_DATA EU SET 
    (EU.CXIX) =
       (
    SELECT A.CXIX FROM C_RIX A
         WHERE substr
    (A.CIX,1,11) = substr(EU.B_LOG,1,11
           and 
    rownum 1)
     
    WHERE substr(EU.B_LOG,1,11IN (SELECT substr(A.CIX,1,11FROM C_RIX A) ;

    commit;


       --
    Update to get --2-10

    UPDATE T_DATA EU SET 
    (EU.CXIX) =
       (
    SELECT A.CXIX FROM C_RIX A
         WHERE substr
    (A.CIX,2,10) = substr(EU.B_LOG,1,10
           and 
    rownum 1)
     
    WHERE substr(EU.B_LOG,1,10IN (SELECT substr(A.CIX,2,10FROM C_RIX A);

    commit;

    end 
    Now, your use of SUBSTR is preventing any use of indexes on the columns. If you can't avoid the SUBSTRs you may want to consider creating function-based indexes e.g.

    create index fbi1 on C_RIX (substr(CIX,2,10) );

    How I can implimant this function in a stored proc?
    Haven't use them much.

    Thank you very much for help.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Speed up a stored procedure

    You just create function-based index same as any other index - using the CREATE INDEX command. For them to be used the QUERY_REWRITE_ENABLED parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED.

    Your stored procedure wouln't need any changes.

  5. #5
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Re: Speed up a stored procedure

    Originally posted by andrewst
    You just create function-based index same as any other index - using the CREATE INDEX command. For them to be used the QUERY_REWRITE_ENABLED parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED.

    Your stored procedure wouln't need any changes.

    Sorry, I'm new to oracle and I did not get this part, table that I use is a production table.
    Where to set this?
    QUERY_REWRITE_ENABLED parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY parameter must be set to TRUSTED.

    thank you

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Speed up a stored procedure

    These can be set "permanently" by the DBA, or you can set them in your session:

    SQL> alter session set query_rewrite_enabled=true;

    Session altered.

    SQL> alter session set query_rewrite_integrity=trusted;

    Session altered.

    In a stored procedure you could call your apadmin.ddl_exec procedure to perform these alter session statements.

  7. #7
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Re: Speed up a stored procedure

    Originally posted by andrewst
    These can be set "permanently" by the DBA, or you can set them in your session:

    SQL> alter session set query_rewrite_enabled=true;

    Session altered.

    SQL> alter session set query_rewrite_integrity=trusted;

    Session altered.

    In a stored procedure you could call your apadmin.ddl_exec procedure to perform these alter session statements.
    Ah. ok
    I i need more substrings
    like create index fbi1 on C_RIX (substr(CIX,2,10) );
    I can use this as well ???

    create index fbi2 on C_RIX (substr(CIX,1,11) );
    create index fbi3 on C_RIX (substr(CIX,1,12) );

    and then ...if I would have this - how can i use fbi1 , fbi2 , fbi3 in a stored procedure besides the update I already have???

    thank you

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Speed up a stored procedure

    Yes, you can create more indexes like that. You might want to name them more meaningfully than my "fbi1" though, e.g. C_RIX_SUBSTR_CIX_1_10. Whatever.

    You don't have to do anything in your stored procedure, other than perhaps those two ALTER SESSION statements to enable use of FBIs. When the optimizer sees a condition like "WHERE substr(A.CIX,1,10) = ..." it will find that there is an FBI based on that function, and use it automatically - just like any other index.

  9. #9
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Re: Speed up a stored procedure

    Originally posted by andrewst
    Yes, you can create more indexes like that. You might want to name them more meaningfully than my "fbi1" though, e.g. C_RIX_SUBSTR_CIX_1_10. Whatever.

    You don't have to do anything in your stored procedure, other than perhaps those two ALTER SESSION statements to enable use of FBIs. When the optimizer sees a condition like "WHERE substr(A.CIX,1,10) = ..." it will find that there is an FBI based on that function, and use it automatically - just like any other index.
    I see, thank you very much for explanation...
    you saved my day

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    why would you need the extra where statement?
    just do this:

    PHP Code:
    begin

       
    --Update to get a--1-12

    UPDATE T_DATA EU 
    SET 
    (EU.CXIX) = (SELECT A.CXIX FROM C_RIX A
      WHERE substr
    (A.CIX,1,12) = substr(EU.B_LOG,1,12) and rownum 1);

    commit;

       --
    Update to get --1-11

    UPDATE T_DATA EU 
    SET 
    (EU.CXIX) = (SELECT A.CXIX FROM C_RIX A
      WHERE substr
    (A.CIX,1,11) = substr(EU.B_LOG,1,11) and rownum 1);

    commit;


       --
    Update to get --2-10
    /* don't see how 2-10 will ever equal 1-10 unless 1-10 
       is really 9 characters or less, but then shouldn't it be 1-9?? */

    UPDATE T_DATA EU 
    SET 
    (EU.CXIX) = (SELECT A.CXIX FROM C_RIX A
      WHERE substr
    (A.CIX,2,10) = substr(EU.B_LOG,1,10) and rownum 1);

    commit;

    end 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by The_Duck
    why would you need the extra where statement?
    So as not to update the T_DATA records that don't have a match and set CXIX to NULL.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    ah. ok.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Re: Speed up a stored procedure

    Originally posted by alexis200487
    I see, thank you very much for explanation...
    you saved my day

    Do I have to create a material. view for production table in order to use
    this:
    query_rewrite_enabled=true
    query_rewrite_integrity=trusted

    and for procedure then which table to use view or a table itself to compare???

    thank you

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Speed up a stored procedure

    No, you don't have to do that.

  15. #15
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Re: Speed up a stored procedure

    Originally posted by andrewst
    No, you don't have to do that.
    I was tying to create an index as were suggested erlier, but it gener. an error insufficen privilege

    i connected to a schema with right password and user id, the permisiona are granted to create any indexes

    what could it be besids the permissions ?

    thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •