Oracle Database Change Notification and ROWID's

Question

Oracle's database change notification feature sends rowids (physical row addresses) on row inserts, updates and deletes. As indicated in the oracle's documentation this feature can be used by the application to built a middle tier cache. But this seems to contradict when we have a detailed look on how row ids work.

ROWID's (physical row addresses) can change when various database operations are performed as indicated by this stackoverflow thread. In addition to this, as tom mentions in this thread clustered tables can have same rowids.

Based on the above research, it doesn't seem to be safe to use the rowid sent during the database change notification as the key in the application cache right? This also raises a question on - Should database change notification feature be used to built an application server cache? or is a recommendation made to restart all the application server clusters (to reload/refresh the cache) when the tables of the cached objects undergo any operations which result in rowid's to change? Would that be a good assumption to be made for production environments?

Marozas

Why don't you rely on business entities/domain attributes? – Thorathoracic

I didn't completely understand your recommendation. With database change notification, oracle only sends rowids for the inserted/updated rows. It cannot be configured to provide any other column information in that table. – Marozas

yes, and why don't you create caching layer over your Data Access Layer, relying on business data, not storage-related. Do you think you can cache the data better than oracle does? – Thorathoracic

I think you are getting the question wrong. I am planning to built up a replicated cache for my business entities on the application server. It's the oracle database notification feature which forces one to use storage related information (i.e. rowids) as the cache key. – Marozas

I did get your question and asked you why don't you build your cache on some higher level, which seems to be more efficient? – Thorathoracic

I am confused by the documentation. It seems the ROWIDS are the auto-generated aka identity ids, not the physical row address: ` // Ask the server to send the ROWIDs as part of the DCN events (small performance // cost): prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true"); ... ROWID="+autoGeneratedKey.getString(1)` – Luxurious

Ok. Are you recommending something which ORM frameworks provide? like hibernate's second level cache. The reason I do not plan use hibernate's second level cache is to get more fine control on the cache implementation. For e.g. I can control and make some operations hit the database directly instead of the cache. – Marozas

Tim - ROWID is a unique row identifier which is constructed based on the information of where the row is persisted. Have a look at this link to know more about the row id format - download.oracle.com/docs/cd/E11882_01/server.112/e17118/… – Marozas

Answer 1

It seems to me to none of operations that can potentially change the ROWID is an operation that would be carried out in a productive environment while the application is running. Furthermore, I've seen a lot of productive software that uses the ROWID accross transaction (usually just for a few seconds or minutes). That software would probably fail before your cache if the ROWID changed. So creating a database cache based on change notification seems reasonable to me. Just provide a small disclaimer regarding the ROWID.

The only somewhat problematic operation is an update causing a movement to another partition. But that's something that rarely happens because it defeats the purpose of the partitioning, at least if it occurred regularly. The designer of a particular database schema will be able to tell you whether such an operation can occur and is relevant for caching. If none of the tables has ENABLE ROW MOVEMENT set, you don't even need to ask the designer.

As to duplicate ROWIDs: ROWIDs aren't unique globally, they are unique within a table. And you are given both the ROWID and the table name in the change notification. So the tuple of ROWID and table name is a perfect unique key for building a reliable cache.

Faradmeter

Sounds right. While researching more on this topic I came through index organized tables (IOT). They have logical rowids which can change since the rows are stored in a sorted manner. So when using DCN we would have to always make sure that the database table is not an index organized table, right? – Marozas