COmplex update takes a 90 minutes to run in Oracle

10 pts.
Tags:
BULK COLLECT statement
Oracle
Oracle administration
Oracle Processes
Oracle Query
In the following procedure, the update is taking 90 minutes.

The client table has 22 million records, the CPDS table has 40 records.

It has been written by someone else, I don't have access to Oracle so can't check my thinking, but I believe, if we select the data to be updated into pl/sql tables, and then using rowid just update the rows with the data stored in pl/sql tables (both the select and update using bulk binds), then the query will be faster.

Cheers

Abhi

*************

PROCEDURE POPULATE_CLIENT_CPDS_DATA( P_START_CLNTNUM IN NUMBER, P_END_CLNTNUM IN NUMBER, P_UPDATED OUT NUMBER) IS         L_STRSQL VARCHAR2(2000) ; BEGIN         L_STRSQL := 'UPDATE CLIENT C SET ( O2BFID, MSISDNVALIDATIONDATE, SOURCE ) = ( SELECT  O2BFID, MSISDNVALIDATIONDATE, SOURCE FROM C USTOMER_CPDS_DATA WHERE ID = C.ID ) WHERE CLNTNUM BETWEEN ' ;

        L_STRSQL := L_STRSQL || P_START_CLNTNUM || ' AND ' || P_END_CLNTNUM || ' AND C.DOMAIN = ''' || 'IFL.co.uk' || ''' AND EXISTS ( SELEC T 1 FROM CLIENT_CPDS_DATA WHERE ID = C.ID)';

        EXECUTE IMMEDIATE L_STRSQL ;         P_UPDATED := SQL%ROWCOUNT; EXCEPTION         WHEN OTHERS         THEN                 P_UPDATED := -1; END POPULATE_CLIENT_CPDS_DATA;



Software/Hardware used:
Oracle
ASKED: February 1, 2011  4:19 PM
UPDATED: February 1, 2011  5:30 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Bulk binding is useful to speed up multiple individual statements by reducing the context switches between the PL and the SQL engines, but in this case you have a single UPDATE statement, so context switches are not a factor and thus I don’t think bulk binding will help. In fact, I guess it would be slower.

You will probably be able to improve the response time by adding (if they are missing) some indexes.

For example, indexes on the following columns would help:

CUSTOMER_CPDS_DATA.ID
CLIENT.CLNTNUM,CLIENT.DOMAIN ( or maybe domain,clntnum )
CLIENT_CPDS_DATA.ID

“<i>I don’t have access to Oracle so can’t check my thinking</i>”

So, is this just a theoretical discussion ? :-)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following