RE: Using ROWID instead of indexes

Mark,

   I would not play this game. Even if your ideas are sensible and look logical, they smack of desperation and you're on your way to building an unmaintainable monster. Vector of rowids, right. But with 10 million rows, it will take around 180M just to store them. You need to associate a key, right ? You're just reinventing the wheel - or more precisely the index, replacing tree by sequential memory search. Forget about it.

   You should try to tackle the problem at the root. For one thing, are you sure you really need to SELECT before the UPDATE ? You want the rowid. But to get it I presume you are using an old-fashioned index search, aren't you ? Where's the benefit ? Why don't you try to directly update with the same (presumably) selective criterion, by-passing the SELECT step ? - the SELECT is implicit to the UPDATE. You can check the number of rows processed if you want to see whether the row actually was there - and insert if nothing was updated. This could boost performance by more than the factor you need. Have a closer look at indexes too. Try to identify, and create if need be, a small (in number of blocks) index which will help you locate data fast. Internal Oracle algorithms are not THAT bad, you know. The trick is just to use them cleverly.

-- 
HTH,

  Stephane Faroult
  email: sfaroult_at_oriolecorp.com 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------> 


> 

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