RE: Using ROWID instead of indexes

Mark

Sometimes there is only so far you can go with tuning the code...beyond that is hardware. If you have room in the budget for a solid state disk (the low-end is around 10K) you should be able to get significant increases in transaction rate. A lot of the really high transaction-rate Oracle users, such as online brokerages, are using SSD technology to do things like putting redo logs, sorts and other bottlenecks onto memory. Then your only limiting factor becomes your bus speed.

Chris Gait

> 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
>

-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).