| 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: Using ROWID instead of indexes
Mark,
If you're using a cursor, "update where current of" effectively
uses the rowid to perform the update. With many rows, the overhead
of locking rows individually can grow - you could try locking the table
in exclusive mode beforehand - I could be totally wrong on this and
(hopefully) one of our gurus will correct me if I am.
Another technique to try - is it worth / possible to store the data in
a hash cluster? If you have a reasonable spread of key values, this can
produce a huge reduction in select time - in a previous job, we had to
select 24 month's data and produce stats by customer/product for a report.
I moved this into a hash clustered table with 24 separate reads in a
database procedure, and got the run-time down from 30 mins+ to 6 mins.
HTH Malcolm
>I am tuning a large OLTP database. 95% of transactions are a SELECT +
>UPDATE of
>a master table, and an insert to a transaction table.
>The SELECT|UPDATE is too slow - I need to speed it up by about 50%, to over
>2000 transactions/second. To speed up the UPDATE portion, I now select the
>rowid, and update on the rowid, so that an index scan is not needed for the
>update. I am now thinking of taking it a step further, and allowing the
>application to select all of the primary key columns and corresponding
>rowids at
>startup, store it in a vector outside the database, and do all selects and
>updates on the rowid. Im pretty sure it will scan the vector faster than
>oracle
>can do an index lookup. The vector can be refreshed daily. All blocks are
>in the
>KEEP pool. [New] rows not found are easily dealt with by requerying on the PK.
>Am I right in assuming that rowid retrievals are faster than index scans,
>if all
>of the blocks are in the buffer cache? There are approx. 10m rows in the
>master
>table.
>(I have tried every possible combination of partitioning/IOTs/indexes.)
>
>Mark
>mteehan_at_erggroup.com
>
>
>---------------------------- ERG Group --------------------------
> The contents of this email and any attachments are confidential
> and may only be read by the intended recipient.
>-----------------------------------------------------------------
>
>
>
>--
>Author:
> INET: mteehan_at_erggroup.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: 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).
Oracle Developer,
Belfast
Received on Sun May 28 2000 - 18:38:42 CDT
 |
 |