5 Replies Latest reply: Sep 1, 2007 5:41 AM by Zlatko Sirotic RSS

    to REF or not to REF?

    385288
      Quick question:

      I have noticed examples in the documentation where an object is used as a datatype for an attribute without the REF clause, and examples where it is used with the REF clause:

      CREATE TYPE Object AS OBJECT
      ( Attribute Object_A)
      /

      and:

      CREATE TYPE Object AS OBJECT
      ( Attribute REF Object_A)
      /

      Can someone please explain in simple terms what the difference is?
        • 1. Re: to REF or not to REF?
          157315
          Cameron,

          REF is a reference (pointer) to another object type instance. The decision on whether to use REF or not should depend on the application object model you are mapping from. You find guidelines of mapping a UML object model to Oracle Object-Relational constructs in a free online course at http://otn.oracle.com/products/oracle9i/htdocs/9iobe/OBE9i-Public/obe-dev/html/objects/objects.htm.

          Regards,
          Geoff
          • 2. Re: to REF or not to REF?
            247823
            Hi,


            REF Object column stores the address of the value i.e., like a pointer in "C" language.

            REF takes as its argument a correlation variable (table alias) associated with a row of an object table or an object view. A REF value is returned for the object instance that is bound to the variable or row.
            DEREF returns the object reference of argument expr, where expr must return a REF to an object.

            Regards,
            Sailaja
            • 3. Re: to REF or not to REF?
              Zlatko Sirotic
              To REF or not to REF?
              I think that answer is: Not to REF!

              C.J.Date in "An introduction to Database Systems" (eighth edition, 2004, chapter 26)
              says that using REF's in relational databases is "The Second Great Blunder":
              - "The blunder consists of mixing pointers and relations"
              - "The Second Great Blunder undermines the conceptual integrity of the relational model in numerous ways"...


              And, REFs can't replace good old foreign keys:
              CREATE TYPE dept_type AS OBJECT (
                deptno VARCHAR2(10),
                dname VARCHAR2(20)
              ) NOT FINAL
              /
              CREATE TABLE dept_obj_tab OF dept_type
                (PRIMARY KEY (deptno))
                OBJECT IDENTIFIER IS PRIMARY KEY
              /
              INSERT INTO dept_obj_tab VALUES ('10', 'DEPT 10')
              /
              INSERT INTO dept_obj_tab VALUES (dept_type ('20', 'DEPT 20'))
              /
              CREATE TYPE emp_type AS OBJECT (
                empno VARCHAR(10),
                ename VARCHAR2(20),
                dept_oid REF dept_type
              ) NOT FINAL
              /
              CREATE TABLE emp_obj_tab OF emp_type
                (PRIMARY KEY (empno))
                OBJECT IDENTIFIER IS PRIMARY KEY
              /
              ALTER TABLE emp_obj_tab
                MODIFY dept_oid NOT NULL
              /
              ALTER TABLE emp_obj_tab
                ADD SCOPE FOR (dept_oid) IS dept_obj_tab
              /
              INSERT INTO emp_obj_tab VALUES
                ('101',
                 'EMP 101',
                 (SELECT REF (d)
                    FROM dept_obj_tab d
                   WHERE deptno = '10'
                 )
                )
              /
              INSERT INTO emp_obj_tab VALUES
                ('102',
                 'EMP 102',
                 (SELECT REF (d)
                    FROM dept_obj_tab d
                   WHERE deptno = '20'
                 )
                )
              /
              This (maybe) looks nice - we don't need join:
              SELECT empno,
                     ename,
                     e.dept_oid.dname
                FROM emp_obj_tab e
              /
              EMPNO      ENAME                DEPT_OID.DNAME
              ---------- -------------------- --------------------
              101        EMP 101              DEPT 10
              102        EMP 102              DEPT 20
              But this is not nice - we can delete DEPT that has EMPs (now we have "dangling REFs"):
              DELETE dept_obj_tab
              WHERE deptno = '20'
              /
              SELECT empno,
                     ename,
                     e.dept_oid.dname
                FROM emp_obj_tab e
              /

              EMPNO      ENAME                DEPT_OID.DNAME
              ---------- -------------------- --------------------
              101        EMP 101              DEPT 10
              102        EMP 102
              To avoid dangling REFs, we must have good old foreign key:
              -- first we must return deleted row 
              INSERT INTO dept_obj_tab
                VALUES (dept_type ('20', 'DEPT 20'))
              /
              -- create good old FK
              ALTER TABLE emp_obj_tab
                ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_oid) REFERENCES dept_obj_tab
              /
              -- now referential integrity works as usual
              DELETE dept_obj_tab
              WHERE deptno = '20'
              /
              DELETE dept_obj_tab
              *
              ERROR at line 1:
              ORA-02292: integrity constraint (SCOTT.EMP_DEPT_FK) violated - child record found
              So, don't use REF's, don't make "The Second Great Blunder".


              But I’ am not so sure for "The First Great Blunder"!
              "The First Great Blunder" is to equate object classes and relational variables
              (simply speaking - to have object tables, tables in which each row represents an object).

              C.J.Date says that one consequence of "The First Great Blunder" is to have subtables and supertables.
              Subtables and supertables exists in SQL:1999 standard:
              "SQL allows base table B to be defined as a "subtable" of base table A only if B and A are both "typed tables"
              and the structured type STB on which B is defined is a subtype of the structured type STA on which A is defined".

              But Oracle hasn't (explicit) subtables and supertables - and this is good thing.
              In Oracle we can't say:

              CREATE TABLE programmer_obj_tab OF programmer_type UNDER emp_obj_tab; -- SQL:1999 syntax

              but we can do this:
              CREATE TYPE programmer_type UNDER emp_type (
                experience NUMBER(2)
              ) NOT FINAL
              /
              Now table EMP_OBJ_TAB has a new (hidden) column EXPERIENCE, and EMP_OBJ_TAB can hold rows of subtype PROGRAMMER_TYPE
              - we don't need (object) subtable PROGRAMMER_OBJ_TAB.


              Regards,
              Zlatko Sirotic
              • 4. Re: to REF or not to REF?
                Billy~Verreynne
                > I think that answer is: Not to REF!

                Agree wholeheartedly. This is a "object model program thing" that translates very poorly into a persistent data structure (like those in a database) that needs to provide integrity and consistency. A result of an application (code) centric viewpoint and not a data centric viewpoint.

                In fact, I think this is similar to the issue of using PL/SQL "tables" (associative arrays) as a replacement for a proper SQL table - invariably the programmer of such poorly designed code runs into a very large and very hard brick wall when it comes to flexibility, performance and scalability.

                The fundamentals of the design of data structures are fundamentals. Period.

                Something I think the UML and o-o crowds ignore as they deem classes to be different because it is called a class and not an entity. A data stucture is a data stucture by any other name.
                • 5. Re: to REF or not to REF?
                  Zlatko Sirotic
                  In fact, I think this is similar to the issue of using PL/SQL "tables" (associative arrays) as a replacement for a proper SQL table
                  - invariably the programmer of such poorly designed code runs into a very large and very hard brick wall
                  when it comes to flexibility, performance and scalability.
                  But our OS can use virtual memory :)

                  "Oh, boy, virtual memory ! Now I'm gonna make myself a really *big* RAMdisk !"
                  (http://www.gdargaud.net/Humor/QuotesHardwareSoftware.html)


                  Regards,
                  Zlatko Sirotic