| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to do faster updates
Index, Hmmmmmm, doing that you would now also be updating the index, not to mention the overhead of creating the index. Sounds longer and more costly to me.
If you are joining or doing some type of where clause then you can get big performance improvements using an inline view. A few SQL books I've seen document it well (http://www.oreilly.com/catalog/mastorasql/), Oracle's doc's don't. You can do joins, exists, everything in the view as long as you preserve the unique key of the table to be updated. Heck, you might even get the select to run in parallel.
UPDATE TABLE(SELECT projs FROM dept d WHERE d.dno = 123) p SET p.budgets = p.budgets + 1;
If you need a better example, email me directly and I'll copy it from the book and email it to ya.
"Mercadante,
Thomas F" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<NDATFM_at_labor.sta cc:
te.ny.us> Subject: RE: How to do faster updates
Sent by:
root_at_fatcity.com
09/13/02 11:18 AM
Please respond to
ORACLE-L
Add an index using that column.
create a pl/sql block selecting the records to be updated and perform your update.
drop the index when finished.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Friday, September 13, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L
Hi All,
I've a table of 10 M records and I want to update some thousand records based on a column which is not a part of any index. Please suggest the quickest way to do this.
Thanks,
Rajesh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pillai, Rajesh
INET: Rajesh.Pillai_at_nordstrom.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Brian_P_MacLean_at_eFunds.Com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Sep 13 2002 - 15:48:24 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |