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.
Received on Sun May 21 2000 - 22:39:05 CDT
