Why read only?

Over at AMIS technology blog, Jurgen Kemmelings writes about read only tables, a new feature in Oracle 11g.

I’m trying to find good use cases for read only tables. Maybe they are like constants in C++. I used constants for two reasons: I wanted the compiler to find errors if I attempt to modify something I shouldn’t by mistake, and I wanted to take advantage of optimizations that are possible if the compiler knows in advance that something will not change.

Problem is, I’m not sure how much this applies to Oracle’s read only tables.
In some cases, the compiler will probably catch me if I attempt to update a read only table, but if I used dynamic queries, it probably won’t (Oh, I wish I had 11g here so I could test it!). Well, some protection is better than nothing. What about optimizations? Will Oracle’s optimizer use the fact that the table can’t change? It makes the table a better candidate for caching, assuming it is not too large.

In general, there is not much information about this feature out there. I hope that in the near future white papers and articles will be written, and the usefulness of this feature will become clearer.

Advertisements

3 Comments on “Why read only?”

  1. This need was always around since I started Oracle and lots of discussions were made –
    http://oraqa.com/2006/01/09/how-to-set-a-table-in-read-only-mode
    http://www.dizwell.com/prod/node/60
    http://forums.oracle.com/forums/search.jspa?threadID=&q=read+only+table&objID=c84&dateRange=all&userID=&numResults=15

    I think primary need was to force the security of the data, to be sure that it will never be changed even by a DBA.

  2. prodlife says:

    Hi Tonguc,

    Looks like the issue is not exactly security, because the DBA will be able to make the table read-write again with another alter table command. Perhaps the point is to prevent accidental changes. In this case, perhaps new features we can want are:
    1. Insert-only tables – you can add new data, but you can’t touch what is already there. You can probably implement it with triggers.
    2. Read-only rows – many tables contain few rows that should never ever be updated or deleted.

    And here’s something I need to check once I get my hands on 11g – can you drop a read-only table?

  3. 11g is now on the market, you try and update this thread I hope 🙂

    http://tonguc.wordpress.com/2007/08/10/11g-documentation/

    Best regards.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s