Re: Using ROWID instead of indexes

Hi,

• Could you please expand on "vector" and send me a email on this.

  I have a concern on refreshing "vector" with the rowids on a   daily basis.

  To refresh the vector, you still have to scan the table anyway   (possibly the entire table) to get the rowids !

  It is not recommended to have logic built on/around ROWID outside   the scope of a transaction. As you know, this is strictly a internal   identifier that Oracle provides us and the value may change on   specific scenarios also (!) who knows !

  Anyway, what is the nature of the application/requirement ? Hope this   is a batch mode update ?

Regards
Rajagopal Venkataramany

----Original Message Follows----
From: mteehan_at_erggroup.com
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Subject: Using ROWID instead of indexes
Date: Sun, 21 May 2000 20:44:00 -0800

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

________________________________________________________________________