Home » SQL & PL/SQL » SQL & PL/SQL » Need help to optimize select-update (Oracle, 10g)
Need help to optimize select-update [message #444258] Fri, 19 February 2010 14:27 Go to next message
virendra.jaipur
Messages: 6
Registered: December 2009
Location: Jaipur
Junior Member
Gentlemen/Ladies,

We have a fact table t1 in the warehouse which has above 6 million records.
There is to be an update like this where t2 has aid+bid as composite primary key.
column aid repeats in t1.
There's performance problem and we'v been told to break this huge update into pieces with few commits in the middle.
update t1
set t1.aid =
(select t2.aid from t2
where t1.bid = t2.bid
)
Any suggestions on how this could be done?
I've tried cursor loop with 3 commits in the middle based on if condition that evaluates on every iteration. Didn't help.

Thanks
Virendra
Re: Need help to optimize select-update [message #444259 is a reply to message #444258] Fri, 19 February 2010 14:52 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>There's performance problem and we'v been told to break this huge update into pieces with few commits in the middle.

Which very well could make the situation worse.

Exactly what kind of "performance problem"?
Re: Need help to optimize select-update [message #444260 is a reply to message #444259] Fri, 19 February 2010 15:05 Go to previous messageGo to next message
virendra.jaipur
Messages: 6
Registered: December 2009
Location: Jaipur
Junior Member
Basically the process just doesn't completed even in hours .. 8-10. Killing the process then leads to rollback which again takes hours in the given undo space.
DBA says that smaller chunks would resolve memory problems because the code is run at Dev environment which may be opt with highest capacity.

Unfortunately I'm just a developer and haven't had a very detailed word with the DBA.
Re: Need help to optimize select-update [message #444261 is a reply to message #444260] Fri, 19 February 2010 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Basically the process just doesn't completed even in hours .. 8-10.

You need to determine where time is being spent & why so slow.

post EXPLAIN PLAN for problem SQL.

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the SQL code
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here
Re: Need help to optimize select-update [message #444262 is a reply to message #444258] Fri, 19 February 2010 15:18 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
As long as you do not run out of space in the redo segment, the fastest way to do the update is with a single sql command. That being said, such small tables should not take hours for such a simple update. Are the bid columns in t1 and t2 indexed. They must be in an index where the bid is the first or only column. This will speed up your update to a couple of minutes.
Re: Need help to optimize select-update [message #444263 is a reply to message #444258] Fri, 19 February 2010 15:33 Go to previous messageGo to next message
virendra.jaipur
Messages: 6
Registered: December 2009
Location: Jaipur
Junior Member
Getting the execution trace isn't possible for me, (atleast for now).
We already have an index on t1 though. Does anything else need to be done to let Oracle utilize this index while the update? I guess this much was enough.
create index idx1 on t1(bid) logging tablespace idx noparallel;

Table t2 is not owned by my team.Will confirm and can create index on t2 if not already.

Thanks a lot,
Virendra
Re: Need help to optimize select-update [message #444264 is a reply to message #444258] Fri, 19 February 2010 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
How many rows in T1?
How many rows in T2?
How many rows where t1.bid = t2.bid?

[Updated on: Fri, 19 February 2010 15:45]

Report message to a moderator

Re: Need help to optimize select-update [message #444269 is a reply to message #444264] Fri, 19 February 2010 19:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The fastest way to update every row in a large table is to rebuild the table
CREATE TABLE new_t1
AS SELECT t2.aid, t1.col1, t1.col2...
FROM t1
LEFT JOIN t2 on t1.bid = t2.bid


Then drop and rename tables. That should take no more than about 30 minutes.

If you want it to run a bit slower, but not as slow as your current version, then you can use a MERGE statement to avoid the nested subquery.

(untested syntax)
MERGE INTO t1
USING (
    SELECT t1.rowid AS rid, t2.aid
    FROM t1
    JOIN t2 on t1.bid = t2.bid
) new
ON new.rid = t1.rowid
WHEN MATCHED THEN SET aid = new.aid


Ross Leishman
Re: Need help to optimize select-update [message #444432 is a reply to message #444269] Sun, 21 February 2010 23:58 Go to previous message
virendra.jaipur
Messages: 6
Registered: December 2009
Location: Jaipur
Junior Member
Thank you rleishman,

Will try both the alternatives and post results.
Good day!

Virendra
Previous Topic: Saving procedures with compilation erros
Next Topic: ora-12516 ora-06512 after repair chained rows
Goto Forum:
  


Current Time: Mon Dec 05 18:56:07 CST 2016

Total time taken to generate the page: 0.26638 seconds