Example 6-17 Creating an Object View Hierarchy

-- Requires Ex. 6-16
CREATE TABLE AllPersons
( typeid NUMBER(1), 
  ssn NUMBER, 
  name VARCHAR2(30), 
  address VARCHAR2(100),
  deptid NUMBER,
  major VARCHAR2(30),
  empid NUMBER, 
  mgr VARCHAR2(30));

CREATE VIEW Person_v OF person_typ
  WITH OBJECT OID(ssn) AS
  SELECT ssn, name, address 
  FROM AllPersons 
  WHERE typeid = 1;

CREATE VIEW Student_v OF student_typ UNDER Person_v
  AS 
  SELECT ssn, name, address, deptid, major
  FROM AllPersons
  WHERE typeid = 2;

CREATE VIEW Employee_v OF employee_typ UNDER Person_v
  AS
  SELECT ssn, name, address, empid, mgr
  FROM AllPersons
  WHERE typeid = 3;

The flat model has the advantage of simplicity and poses no obstacles to supporting indexes and constraints. Its drawbacks are:

• A single table cannot contain more than 1000 columns, so the flat model imposes a 1000-column limit on the total number of columns that the object view hierarchy can contain.

• Each row of the table will have NULLs for all the attributes not belonging to its type. Such non-trailing NULLs can adversely affect performance.

Example 6-18 -- Creating Table Horizontal Model

-- Requires Ex. 6-16 and Ex. 6-17
CREATE TABLE only_persons
( ssn NUMBER,
  name VARCHAR2(30),
  address VARCHAR2(100));

CREATE TABLE only_students
( ssn NUMBER, 
  name VARCHAR2(30),
  address VARCHAR2(100), 
  deptid NUMBER,  
  major VARCHAR2(30));

CREATE TABLE only_employees
( ssn NUMBER, 
  name VARCHAR2(30),
  address VARCHAR2(100), 
  empid NUMBER, 
  mgr VARCHAR2(30));

CREATE OR REPLACE VIEW Person_v OF person_typ
  WITH OBJECT OID(ssn) AS
  SELECT * 
  FROM only_persons; 

CREATE OR REPLACE VIEW Student_v OF student_typ UNDER Person_v
  AS 
  SELECT *
  FROM only_students;

CREATE OR REPlACE VIEW Employee_v OF employee_typ UNDER Person_v
  AS
  SELECT * 
  FROM only_employees;

The horizontal model is very efficient at processing queries of the form:

Example 6-19 -- Querying views horizontal model

-- Requires Ex. 6-16 and Ex. 6-17
-- add the following data
insert into only_persons values(1234,'John','abc');
insert into only_students values(1111,'James','abc',100,'CS');
insert into only_employees values(2222,'jack','abc',400,'Juliet');

SELECT VALUE(p) FROM Person_v p
  WHERE VALUE(p) IS OF (ONLY student_typ);

OUTPUT:
VALUE(P)(SSN, NAME, ADDRESS)
--------------------------------------------------------------------------------
STUDENT_TYP(1111, 'James', 'abc', 100, 'CS')

Such queries only need to access a single physical table to get all the objects of the specific type. The drawbacks of this model are that queries such as SELECT * FROM view require performing a UNION over all the underlying tables and projecting the rows over just the columns in the specified view. Also, indexes on attributes (and unique constraints) must span multiple tables, and support for this does not currently exist.

Example 6-20 Creating table, views vertical model

CREATE TABLE all_personattrs 
( typeid NUMBER,
  ssn NUMBER,  
  name VARCHAR2(30),
  address VARCHAR2(100));

CREATE TABLE all_studentattrs
( ssn NUMBER, 
  deptid NUMBER, 
  major VARCHAR2(30));

CREATE TABLE all_employeeattrs
( ssn NUMBER,
  empid NUMBER, 
  mgr VARCHAR2(30));

CREATE OR REPLACE VIEW Person_v OF person_typ 
WITH OBJECT OID(ssn) AS
  SELECT ssn, name, address 
  FROM all_personattrs 
  WHERE typeid = 1;

CREATE OR REPLACE VIEW Student_v OF student_typ UNDER Person_v
  AS 
  SELECT x.ssn, x.name, x.address, y.deptid, y.major
  FROM all_personattrs x, all_studentattrs y
  WHERE x.typeid = 2 AND x.ssn = y.ssn;

CREATE OR REPLACE VIEW Employee_v OF employee_typ UNDER Person_v
  AS
  SELECT x.ssn, x.name, x.address, y.empid, y.mgr
  FROM all_personattrs x, all_employeeattrs y
  WHERE x.typeid = 3 AND x.ssn = y.ssn;

The vertical model can efficiently process queries of the kind SELECT * FROM root_view, and it is possible to index individual attributes and impose unique constraints on them. However, to re-create an instance of a type, a join over object identifiers (OIDs) must be performed for each level that the type is removed from the root in the hierarchy.