Object views and REFs... Please help !!!

From: Laszlo Papp <laszlo.papp_at_epigenomics.com>
Date: Wed, 27 Feb 2002 17:44:09 +0100
Message-ID: <3C7D0CD9.6FFB3755@epigenomics.com>

Hi,

        I have a relational structure, and I make some object views upon it. I use the MAKE_REF operator to turn foreign key values into REFSs for other object views.
Actually theoretically every OIDs are primary key based and using the MAKE_REF operator evvery REFs should be stored as a SCOPED REF by Oracle.

The question is whether it is really stored as a scoped ref.

And... these are just views ? Do these REFs take up extra space in the DB ?

I attach the short SQL script on which I tested these things... but the REFs seem to be too big...

Note that there are 2 kinds of REFs. One is a simple attribute and the other is a Nested table of REFs. But... as you can see in the attached SQLPlus dump both return the same (big) size of something for the REFs...

Thanx for your help.
I hope, someone has a reliable info on this.

Regards:

        Papp Laszlo

---

So the SQL:

DROP TABLE btab CASCADE CONSTRAINTS;
CREATE TABLE btab (

   id       INT CONSTRAINT pk_btab PRIMARY KEY,
   y        NUMBER

)
/

DROP TYPE int_nt FORCE;
CREATE TYPE int_nt AS TABLE OF INT;
/

DROP TABLE atab;
CREATE TABLE atab (

   id       INT CONSTRAINT pk_atab PRIMARY KEY,
   x        NUMBER,
   bref     INT CONSTRAINT fk_ab REFERENCES btab (id),
   nums     int_nt,
   refs     int_nt

)
NESTED TABLE nums STORE AS nums_st,
NESTED TABLE refs STORE AS refs_st
/

CREATE INDEX num_ind ON nums_st (NESTED_TABLE_ID); CREATE INDEX ref_ind ON refs_st (NESTED_TABLE_ID);

DROP TYPE btyp FORCE;
CREATE TYPE btyp AS OBJECT (

   id       INT,
   y        NUMBER

)
/

DROP TYPE ref_nt FORCE;
CREATE TYPE ref_nt AS TABLE OF REF btyp
/

DROP TYPE atyp FORCE;
CREATE TYPE atyp AS OBJECT (

   id       INT,
   x        NUMBER,
   b        REF btyp,
   nums     int_nt,
   refs     ref_nt

)
/

DROP VIEW bview;
CREATE VIEW bview OF btyp WITH OBJECT IDENTIFIER (id) AS

   SELECT id, y FROM btab
/

DROP VIEW aview;
CREATE VIEW aview OF atyp WITH OBJECT IDENTIFIER (id) AS

   SELECT a.id, a.x, MAKE_REF (bview, a.bref), a.nums,

      CAST (MULTISET (
         SELECT MAKE_REF (bview, r.COLUMN_VALUE) 
         FROM TABLE (a.refs) r
         ) AS ref_nt
      ) AS refs

   FROM atab a
/

INSERT INTO btab VALUES (1,25);
INSERT INTO btab VALUES (2,50);
INSERT INTO btab VALUES (3,75);
INSERT INTO atab VALUES (1,7,1,int_nt (1,2,3,4,5),int_nt (1,2));

---

And the SQLPlus dump of the 3 queries:

SQL> select r.* from aview a, table (a.refs) r;

COLUMN_VALUE

---

---

00004A030A00469AF66BB62997EC1AE030A8C0140273AA0000001426 010001000100290000000000

090626002A00078401FE0000000A02C1020000000000000000000000
000000000000000000

00004A030A00469AF66BB62997EC1AE030A8C0140273AA0000001426 010001000100290000000000

090626002A00078401FE0000000A02C1030000000000000000000000
000000000000000000

SQL> select r.column_value.y from aview a, table (a.refs ) r;  

COLUMN_VALUE.Y

---

            25
            50
 

SQL> select b from aview;  

B

---

00004A030A00469AF66BB62997EC1AE030A8C0140273AA0000001426010001000100290000000000

090626002A00078401FE0000000A02C1020000000000000000000000000000000000000000

-- 
Laszlo Papp
 
Epigenomics AG       www.epigenomics.com      Kleine Präsidentenstr. 1.
+49-30-24345-0                                      10178 Berlin

Received on Wed Feb 27 2002 - 10:44:09 CST