Home » SQL & PL/SQL » SQL & PL/SQL » running update on 200000 records (oracle)
running update on 200000 records [message #348621] Wed, 17 September 2008 07:41 Go to next message
odelya
Messages: 5
Registered: September 2008
Junior Member
Hi,
I have two tables:

TABLE MAILLISTSUB (
ID VARCHAR2(20) NOT NULL,
USERNAME VARCHAR2(60) NULL
)

TABLE MEMLOGIN (
USERNAME VARCHAR2(13) NOT NULL,
PASSWORD VARCHAR2(50) NOT NULL,
USERID NUMBER(20,0) NOT NULL,
EMAIL VARCHAR2(60) NOT NULL,
STATUS NUMBER(5,0) NOT NULL,
AUTOLOGIN VARCHAR2(32) NULL,
ACTIVE_DATE DATE NOT NULL,
RENEW_DATE DATE NOT NULL,
NAME_FIRST VARCHAR2(32) NULL,
NAME_LAST VARCHAR2(32) NULL,
GENDER VARCHAR2(1) NULL,
BIRTH_DATE DATE NULL,
HHOLD_INCOME NUMBER(5,0) NULL,
INDUSTRY NUMBER(5,0) NULL,
JOB_TITLE NUMBER(5,0) NULL,
COUNTRY VARCHAR2(2) NULL,
DONTMAIL VARCHAR2(1) NULL,
ZIP VARCHAR2(10) NULL,
COMMENTS VARCHAR2(50) NULL,
STATUS_INFO VARCHAR2(10) NULL,
FREE_SUB_ID VARCHAR2(Cool NULL,
PHONE VARCHAR2(30) NULL,
ADDRESS VARCHAR2(80) NULL
)

I want to run a query, that will update username field in MAILLISTSUB as the EMAIL from table MEMLOGIN where the USERNAME in MAILLISTSUB is the same as in MEMLOGIN (i decided to use email instead of username and i have to update the table)

UPDATE MAILLISTSUB mlSub
SET USERNAME = (
SELECT email
FROM MEMLOGIN ml
WHERE mlSub.USERNAME = ml.username or mlSub.USERNAME = to_char(ml.userid) or mlSub.USERNAME = ml.email)

I was trying to run this query on 200,000 records. it's running for already 2 hours!!!
is there a way to optimize it?

Thanks!
Odelya
Re: running update on 200000 records [message #348624 is a reply to message #348621] Wed, 17 September 2008 07:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Post an explain plan of the query - and wrap it in CODE tags (the little icon just to the left of the Size dropdown list above the text box you type in)

Re: running update on 200000 records [message #348625 is a reply to message #348624] Wed, 17 September 2008 07:47 Go to previous messageGo to next message
odelya
Messages: 5
Registered: September 2008
Junior Member
what do you mean an explain plan?
I would like the query to take the email from MEMLOGIN and update the USERNAME field in MAILLISTSUB where the USERNAME in MEMLOGIN and MAILLISTSUB are equal..
Re: running update on 200000 records [message #348626 is a reply to message #348625] Wed, 17 September 2008 07:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Run the following in SQL*Plus:
EXPLAIN PLAN FOR UPDATE MAILLISTSUB mlSub
SET USERNAME = (
SELECT email
FROM MEMLOGIN ml
WHERE mlSub.USERNAME = ml.username or mlSub.USERNAME = to_char(ml.userid) or mlSub.USERNAME = ml.email);

SELECT * FROM TABLE(dbms_xplan.display);

And post the result here (in Code tags)
Re: running update on 200000 records [message #348637 is a reply to message #348626] Wed, 17 September 2008 08:11 Go to previous messageGo to next message
odelya
Messages: 5
Registered: September 2008
Junior Member
here are the results:
 PLAN_TABLE_OUTPUT                                                       
 ----------------------------------------------------------------------- 
                                                                         
 ----------------------------------------------------------------------- 
 | Id  | Operation            |  Name          | Rows  | Bytes | Cost  | 
 ----------------------------------------------------------------------- 
 |   0 | UPDATE STATEMENT     |                |       |       |       | 
 |   1 |  UPDATE              | JPMAILLISTSUB  |       |       |       | 
 |   2 |   TABLE ACCESS FULL  | JPMAILLISTSUB  |       |       |       | 
 |   3 |   TABLE ACCESS FULL  | JPMEMLOGIN     |       |       |       | 
 ----------------------------------------------------------------------- 
                                                                         
 Note: rule based optimization, PLAN_TABLE' is old version               

 11 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 15/ms] 


Thanks so much!
Re: running update on 200000 records [message #348640 is a reply to message #348637] Wed, 17 September 2008 08:16 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
What version of Oracle are you on, and was this an upgrade from a previous version?

You will need to get the correct version of the PLAN_TABLE. Talk to your DBA about getting utlxplan.sql run on this database.

Do you have up to date statistics on all the tables?

[Updated on: Wed, 17 September 2008 08:17]

Report message to a moderator

Re: running update on 200000 records [message #348643 is a reply to message #348640] Wed, 17 September 2008 08:28 Go to previous messageGo to next message
odelya
Messages: 5
Registered: September 2008
Junior Member
hey,
I got the correct version and here are the results:
PLAN_TABLE_OUTPUT                                                       
 ----------------------------------------------------------------------- 
                                                                         
 ----------------------------------------------------------------------- 
 | Id  | Operation            |  Name          | Rows  | Bytes | Cost  | 
 ----------------------------------------------------------------------- 
 |   0 | UPDATE STATEMENT     |                |       |       |       | 
 |   1 |  UPDATE              | JPMAILLISTSUB  |       |       |       | 
 |   2 |   TABLE ACCESS FULL  | JPMAILLISTSUB  |       |       |       | 
 |*  3 |   TABLE ACCESS FULL  | JPMEMLOGIN     |       |       |       | 
 ----------------------------------------------------------------------- 
                                                                         
 Predicate Information (identified by operation id):                     
 ---------------------------------------------------                     
                                                                         
    3 - filter("ML"."USERNAME"=:B1 OR TO_CHAR("ML"."USERID")=:B2 OR      
               "ML"."EMAIL"=:B3)                                         
                                                                         
 Note: rule based optimization
Re: running update on 200000 records [message #348657 is a reply to message #348637] Wed, 17 September 2008 08:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try the following:
CREATE INDEX MEMLOGIN_IDX1 ON MEMLOGIN(username);
CREATE INDEX MEMLOGIN_IDX2 ON MEMLOGIN(to_char(userid));
CREATE INDEX MEMLOGIN_IDX3 ON MEMLOGIN(email);


I'd recommend that you gather statistics for the schema as well
Re: running update on 200000 records [message #348686 is a reply to message #348657] Wed, 17 September 2008 10:36 Go to previous message
odelya
Messages: 5
Registered: September 2008
Junior Member
It didn't help me.
How will the statistics help?
Previous Topic: Help with splitting partitioned table
Next Topic: Return Associative Arrays in a Stored Proc (Oracle)
Goto Forum:
  


Current Time: Sun Dec 11 06:15:41 CST 2016

Total time taken to generate the page: 0.08490 seconds