20.6 Schema Evolution

One of the unsung heroes[ 3 ] of object views is their relative immunity from the problems of schema evolution that we discussed in Chapter 18 . As the example below illustrates, object views do not impose the same "evolution penalty" as object tables. This resilience of object views is due at least in part to Oracle's refusal to let you store virtual REFs in a table (as discussed earlier in Section 20.4.3, "Storage of Virtual REFs" ). Since virtual REFs are computed on the fly, you can drop and rebuild underlying schema objects without affecting the way that object views reference each other.

[3] This feature is "unsung" from the point of view that Oracle does not seem to mention it in their documentation.

Recall the earlier scenario about adding a table of artists. Let's pretend that we had to add the table of artists as a schema change, rather than rebuilding everything from scratch. Here's how we'll change the images table:

ALTER TABLE images
   ADD artist_id INTEGER;

The ability to execute this statement represents one significant advantage that object views provide over object tables. If images had been an object table defined directly on the images_t type, this statement would have failed with an ORA-22856 error, "cannot add columns to object tables."

Many DBAs do not like to use the ALTER TABLE statement in a production environment, but it's nice to know that it's possible. You could also recreate the table cleanly with the new column, drop the foreign key constraints to the old table, drop the old table, rename the new table, and rebuild the constraints. This, too, is impossible with object tables.

Proceeding with the rest of the schema change, here is the table of artists (just as we created it previously):

CREATE TABLE artists (
   id INTEGER,
   name VARCHAR2(60),
   CONSTRAINT artists_pk PRIMARY KEY (id)
);

To be "relationally correct," we'll add a foreign key to relate each image to an author:

ALTER TABLE images
   ADD CONSTRAINT image_created_by_artist
   FOREIGN KEY (artist_id)
   REFERENCES artists (id);

Now, as before, we can create the Artist_t type and its associated view:

CREATE TYPE Artist_t AS OBJECT (
   id INTEGER,
   name VARCHAR2(60)
);

CREATE VIEW artists_v
   OF Artist_t
   WITH OBJECT OID (id)
AS
   SELECT id, name
     FROM artists;

and now it's a simple matter to replace the Image_t type definition (as long as we have not implemented any object tables or column objects using this type):

CREATE 
OR REPLACE
 TYPE Image_t AS OBJECT (
   image_id INTEGER,
   file_name VARCHAR2(512),
   file_type VARCHAR2(12),
   bytes INTEGER,
  
 artist_ref REF Artist_t,

   keywords Keyword_tab_t,
   MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2,
      new_file_type IN VARCHAR2, new_bytes IN INTEGER)
      RETURN Image_t,
   MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t)
      RETURN Image_t,
   PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS, WNDS, RNPS, WNPS)
);