Re: Determining a PK for a view

From: Brian Peasland <peasland_at_usgs.gov>
Date: Tue, 5 Jun 2001 12:50:29 GMT
Message-ID: <3B1CD595.186FAEF6@usgs.gov>

> It makes sense to use db,owner,table_name,rowid tuples to uniquely
> identify rows in real tables, but not for views such as I described.
> What I need is a technique for handling these.

Richard,

I'm confused as to why one wants to obtain a rowid for each row returned from a view?!?!? In Oracle 8+, rowids are of the format OOOOOOFFFBBBBBBRRR where:

   OOOOOO = object #
      FFF = relative file #
   BBBBBB = block #
      RRR = row #

While a view is an object and will have an object #, it is not stored as a physical object in the database. Therefore, it will have no file#, block#, or row#.

Views are nothing more than a SQL macro. Instead of typing a very long SQL statement, one can store that statement in a view and then select from that view. There are other advantages to views (and some disadvantages) as well. Any time the underlying table(s) of the view experience DML, the result set of the view changes to match the stored query on the underlying table(s). Why would you propose to index this result set?

Just my 3.14159265 cents worth,
Brian

-- 

====================================
Brian Peasland
peasland_at_usgs.gov
Raytheon at USGS EROS Data Center
Mundt Federal Building
Sioux Falls, SD 57198

Received on Tue Jun 05 2001 - 07:50:29 CDT