Skip to Main Content
  • Questions
  • Relational VS Object Oriented Database Design

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: March 03, 2001 - 10:46 pm UTC

Last updated: May 25, 2012 - 9:19 am UTC

Version: Oracle 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom:

There are two different mind set between relational vs. Object Oriented Database Design.

Can you provide your experiences, what are the criteria (pro and con) to use relational vs. OO database design in Oracle? Objects in Oracle have better performance than relational table, or vice versa.

Thanks for your time and efforts.

Peter Cheng


and Tom said...

First off, in Oracle you would be considering pure relational versus Object RELATIONAL, not OO. We have object relational extensions to the relational database. We do not have an object oriented database.

The answer to this comes down to the data you are working with. Let's say you are building a banking application. You are building the application for the teller to service a person when they walk in the door. The Object perfect for that application is a "person" object identified by name or account number. That person object has all of the attributes of a person in a bank, a name, an address, a list of accounts with all of the account attributes, a list of recent transactions, and so on. A big glob of data that represents all of the information about a person. That is the perfect view for the teller. HOWEVER, at the end of the day, there is someone else who has a totally different view of the data -- they need to count how many transactions happened that day. How much money is in the bank. They have a transaction oriented, not person oriented, perspective of the data. In this case, a RELATIONAL model with possibly application specific Object relational views built on top of it is perfect. You need an account table, that is joined to a transaction table and a person table. In that fashion, the guy who needs to count the transactions is satisfied and so is the guy who needs the person oriented view.

That is the power of the relational model, it is able to easily give many views of the same data. Think of it this way with the scott/tiger EMP, DEPT tables. It might seem to make sense to create an EMP object type, a nested table of EMP, a DEPT object with an attribute of EMP nested table and then a table of DEPT types. Now the whole scott/tiger emp/dept example fits into 1 table with 4 rows. Sounds great. The problem is people will ask questions like:

o how many emps do I have?
o KING is standing here at the front door -- is he an employee or not?
o how much salary did I pay accountants last year?

and so on. None of these questions are easilly answered with a DEPT oriented view of the data.

Just some food for thought. I've not really used the Object relational concepts as a STORAGE mechanism, I have used them in my programming with PLSQL lots. I tend to stick with real tables and use the objects in my code frequently.

Rating

  (73 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Any recommendations on books of database design?

A reader, August 21, 2001 - 9:22 pm UTC


Any recommendations on books of database design?

F, August 21, 2001 - 9:22 pm UTC


Mahendra Jadav

Mahendra Jadav, December 30, 2001 - 12:48 am UTC

This implies, do not to use ORDBMS as storage mechanism. Use OR views based on plain table. Correct?

Tom Kyte
December 30, 2001 - 10:12 am UTC

That is my personal choice, yes.

Beautiful

Srimal, December 31, 2001 - 10:03 am UTC

Hello Mr Tom,

Your response is consise and to the point and amazing!



Object vs Relational - Help needed

Jsri, January 19, 2004 - 3:49 pm UTC

Hi Tom,

I am not sure whether I can use this context to discuss my case. I've requirement to pull data from a database( Object model), and dump the data into Data warehouse.

I've been searching in the Net for best methodology I can adopt to design an interface where I can Map the Objects to a Relational model STAGE schema, to where I can pull data first and from there to a Data warehouse schema
(Target).

Pl give your guidence about what direction I should take, and what important things I should consider.

I would appreciate, if you could give me references to any Online articles that might be useful for me.

Thanks,

Tom Kyte
January 19, 2004 - 4:01 pm UTC

what database are you pulling from.

Object vs Relational - Help needed

Jsri, January 19, 2004 - 8:17 pm UTC

Tom,

Here under giving you details:

Source Database: Oracle8i(8.1.7)
Application: Tririga (Object model DB)

Stage Database: Oracle9i (9.2.0.2)
Target Database: Oracle9i (9.2.0.2)

We're having no idea about how to analyze Source Database and Map to a relational model schema, and then load to target Warehouse schema. Client would like to see Real-time data in DWH, transaction volume is very low.

We're thinking about Oracle streams(Or, Informatica Power center) for Change Data capture, but not sure how initially map Objects to RDBMS tables. If you could give us any direction, we can quickly go from there.

Thanks in advance.


Tom Kyte
January 20, 2004 - 7:13 am UTC

well, you'd have to let us know HOW you might be able to capture changes in this tririga database (else streams is pretty much useless)

Now, most object dbs i've seen are a couple of meg/gigs -- we could just load the entire thing in a couple of seconds -- how big is this and what are the possibilities for tririga to tell us "whats changed"

mapping the objects -> relational tables is something you need to design, based on your requirements, not something we can do here.

Object-Relational View

Shibu, May 09, 2004 - 1:11 am UTC

I want to understand what steps in PL/SQL do you undertake to provide object-views of the data possibly with an example.
Any reading materials on how to use object-relational views of data.

Tom Kyte
May 10, 2004 - 7:34 am UTC

if you have access to my first book "Expert one on one Oracle", i cover that in a chapter.

Also see
</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96594/toc.htm <code>

My Two Cents

Michael, May 10, 2004 - 10:44 am UTC

Until recently, I was working on a project where the PM had made the decision to generate Oracle DDL directly from the Rational Rose Object Modeling SW. The original Data Architect was given the boot for railing against this decision. He felt that the best approach was to develop a good relational design and build the object views on top, as Tom has suggested. At that time, I was coming in as the DBA for the project and was told by a coworker to NOT bring it up, even though I agreed with the fellow who lost his position, or I would also swing.

I knew this would lead to no good end. In short order, relational vs object conflicts started to pop up. Of course, the answer was to write more code or do other workarounds. I was able to convince the PM to get a single copy of the CA product called Data Model Validator (aka Erwin Examiner), which does sanity checks on relational database designs. So I reverse engineered a ddl script into it and some real eye-openers popped into the reports. There were over a dozen redundant-path relationship errors, and 7 infinite loop errors. With redundant paths, the same PK attribute migrates more than once to a child/grandchild/etc table via two different paths. Commonly, the PK of the parent goes to two child tables as a PK, and both of those children eventually pass that PK to a 3rd or 4th level table....one as the PK and one as a non key field. With infinite loops, the parent passes the PK to a child and possibly a grandchild, who then passes it back to the original parent in a non key position.

You can only imagine, if you think about it, what kind of data anomalies or constraint problems can occur with such a design. I predicted that if this design was ever rolled out, they would have a small squad of programmers handling incessant requests to FIX the data problems popping up all over the place. Of course, this would all be done with triggers or other programming glue and rubber-bands. (For example, with some of these faulty relationships, instead of FIXING them, they selectively created SOME FKs as "deferrable initially deferred" constraints. How could it be otherwise?) Eventually, they would probably have had to develop a data problem reporting system to keep track of it all. I have seen that happen before.

So what Tom says about building the object views on top of a good relational design is right. In my opinion, develop a robust data model and logical design, then physicalize it with a DBA who knows what he or she is doing. Add anything else the object model requires on top of it.

BTW, the client cancelled the project I am referring to, so now I don't have to worry about the fudge hitting the blower when that bad relational database design rolled out this summer. Now I am in a large DWH environment, with an entirely different set of worries.

Note that I do not cast aspersions on object modeling, which in this case was quite good. The problem is that Oracle is a RELATIONAL database that SUPPORTS objects, not the other way around. So good object design will not always equal good relational design.







locks

AD, May 07, 2005 - 1:47 pm UTC

Tom,
The following question is purely for academic interest.
Why locking is much more complex in an OO database environment than in a relational database?


Regards


Tom Kyte
May 07, 2005 - 2:12 pm UTC

It typically isn't, and that is the problem, they typically don't have rows -- but big globs of data. So, you update what is a "master" record in an RDBMS and you have locked all of the children -- in a RDBMS, you would not....


So maybe the "complexity" is coming from "how do I write something that has some degree of concurrent access". It is more complex for you because the locking model is relatively simplistic.

Re: My Two Cents

Paul Horth, May 09, 2005 - 7:26 am UTC

Michael,

How how I wish I didn't have to continually fight against OO people who think you can design relational DBs in an OO manner. Luckily I haven't lost my job (yet) and usually win the argument!

OO and relational are orthogonal to each other and should be designed with the correct techniques in each area. When you need to tie the two together, then it's usually easier to get a third-party tool to do it (TopLink for example).

OR performance

Nilanjan Ray, May 12, 2005 - 6:17 am UTC

Hi Tom,

Will using Object Relational views improve performance over the same classic relational view? Would be very helpful if you can demonstrate with an example.

Regards
Nilanjan Ray

Tom Kyte
May 12, 2005 - 8:02 am UTC

they are completely different beasts.

tell me, what do you need in your client application????? do you need an object or just rows and columns. a 'relational view' is a subtype of a 'object relational view', but you would use A or B depending on what your client application requires.

??? how would you decide (and why)???

max, August 02, 2005 - 11:46 am UTC

hi tom,

i've a question regarding design, how to model, which features to choose ...

we've the requirement to store some data as well as some metadata about it, and we're not quite sure whether to use OR-features to achieve that goal ...

for example, consider one needs to store several instances of shapes according to their metadata -- *without* having those different data structures represented by different tables (at least one for each kind of shape).

yes, this sounds like one of those "generic" we-don't-know-what-we-want approaches. but please note: shapes are just used for explaination purposes here. in our real life use case the number the data structures should support storage of "templates" for several different unknown types *and* instances/objects of these types.

we want to able to "describe" the structures of shapes (their properties) in general, and then to store instances of those shapes with values for their properties.

one option would be to establish some kind of meta model where one could then associate different kinds of shapes with their properties/attributes at a meta data level (e.g. circles have a diameter r, rectangles have sides a and b). an instance of those shapes would then assign values to that certain shapes properties (e.g.: for instance X of type "circle" the value of property "diameter" is 42). with such a model the introduction of a new kind of shape would just require to input the meta data about it. but it would be difficult to *constrain* data of instances of shapes according to the shape's meta data (e.g.: one should *not* be able to store a diameter for a rectangle).

user defined types seem to be useful for that. one could create a type hierarchy with a not instantiable supertype containing the general properties/methods and subtypes for the different kinds of shapes. a table based on that supertype could then store any kind of shape. a new kind of shape could be introduced by just creating a new subtype of the supertype. the type constructors would enforce consistency between data and meta data without any additional coding ...

i've read your articles/books and know your "don't use OR-features for *storage*" ...
on the other hand ORACLE AQ relies on object types as well ...

which PROs and CONs do you see for both approaches described above?

Tom Kyte
August 02, 2005 - 1:59 pm UTC

I did not have to read past the second paragraph.

I've never used object tables (object types, nested tables, varrays) to persistently store data. I use them constantly in PLSQL to program with. I use object types that make sense (like XMLTYPE) as a single column -- but I do not use object tables, nested tables or the like to STORE the data.

Object Views of relational data - sure, absolutely.
Object Tables, I'm not a fan.

I don't have sufficient depth into your exact problem to say whether I'd change my mind or not (shapes is not a compelling example)

It seems to me at some point, the application would have to know what it is dealing with -- else you are not going to be able to do very much with it in the application. Sort of like an XML browser -- sure it can show you any bit of XML, but it cannot do very much with it.

(i shouldn't have mentioned OR that early ;o)

max, August 02, 2005 - 5:16 pm UTC

creating a "normal" table with one column of type "MyShape" is what i was thinking about (and have tried in a small test case). "MyShape" is not instantiable and supertype of "MyCircle", "MyRectangle", ...

is there anything to worry about, as long as there are no nested tables involved? ADT payloads with AQ result in a columns of such user defined types too ...

are you aware of any problems with varrays (within user defined types) as long as they are stored inline?


Tom Kyte
August 02, 2005 - 7:18 pm UTC

I have never seen a truly weak entity in my entire career (varray and nested tables are weak entities). Never.

i've never seen a child table I didn't want to at some time query by itself.

More issues with Object Types

Mike Friedman, August 02, 2005 - 10:15 pm UTC

Have you ever created a column with an object type and then tried to change the type?

It's a nightmare - you can't change the type in most ways while anything else depends on it.

Now think about upgrades, enhancements, and bug fixes.

You can drop and recreate an object view, no problem, but what do you do with a million row table that has columns of object types that you need to change?

This technology isn't yet ready for production use in the DB for storing data.


Tom Kyte
August 03, 2005 - 10:06 am UTC

well, in 9i you can "evolve" many types - significantly different from 8i

as for type evolution

max, August 03, 2005 - 2:18 am UTC

hi mike,

could you please let me now which *bad* experience you had with evolution of types used for storage? my *small* test case -- dropping some attributes and migrating existing table data -- succeeded (after all) ...

and if i'd like to add attributes/methods i could create a new SUBtype, right?



as for type evolution II

max, August 07, 2005 - 4:26 pm UTC

meanwhile i 've found out that one can't change the size of a varray embedded within an object type with pre 10g ...

so *** please *** tell me ...

max, August 10, 2005 - 4:02 pm UTC

... what 's wrong and/or dangerous with such an approach from *your* point of view:

drop type MyCircleType ;
drop type MyRectangleType ;
drop type MyShapeType ;

create or replace type MyShapeType as object(
Color varchar2( 10 )
) not final not instantiable ;
/

create or replace type MyCircleType under MyShapeType(
Radius number
) not final ;
/

create or replace type MyRectangleType under MyShapeType(
SideA number,
SideB number
) not final ;
/

create table MyShapeTab( MyShapeData MyShapeType ) ;

insert into MyShapeTab( MyShapeData ) values( MyCircleType( 'blue', 1 ) ) ;
insert into MyShapeTab( MyShapeData ) values( MyRectangleType( 'red', 10, 20 ) ) ;
commit ;

select case when T.MyShapeData is of ( MyCircleType )
then 'MyCircleType'
when T.MyShapeData is of ( MyRectangleType )
then 'MyRectangleType'
end SubType,
treat( T.MyShapeData as MyShapeType ) MyShapeData
from MyShapeTab T
/

SUBTYPE MYSHAPEDATA(COLOR)
--------------- ------------------------------
MyCircleType MYCIRCLETYPE('blue', 1)
MyRectangleType MYRECTANGLETYPE('red', 10, 20)

select MyShapeData
from MyShapeTab T
where T.MyShapeData is of( only MyCircleType ) ;

MYSHAPEDATA(COLOR)
-----------------------
MYCIRCLETYPE('blue', 1)

from my (current) point of view the main benefit is that one can store all different kinds of shapes within *one* table instead of having one table per shape type -- which would then have to be integrated into the data model one by one ...

as one can see the object type just serves as some kind of "container" to store data that is *not* related to any other database entry at all ...

and one can easily extend that model by supplying new object types ...

so couldn't i change your mind here (and if no: why not)?

Tom Kyte
August 11, 2005 - 8:56 am UTC

tell me about the client application. What is this client application and what sense will it make of this data.

Great, client gets "shape", now what.

I'm just not a fan of using a relational database to store "stuff", you might just as well stuff it all into XML and use a single xmltype column. At least I could index that pretty easily and ask questions like "find me the rectangles whose length is 10" efficiently.

but i could have done this ...

max, August 11, 2005 - 3:37 pm UTC

... without using XML ...

create index XIEMyShapeTabIdx1
on MyShapeTab( MyShapeData.Color ) ;

create index XIEMyShapeTabIdx2
on MyShapeTab( treat( MyShapeData as MyCircleType ).Radius ) ;

analyze table MyShapeTab compute statistics for table for all indexes ;

select MyShapeData
from MyShapeTab T
where T.MyShapeData.Color = 'blue'

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=16)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYSHAPETAB' (Cost=1 Card=1 Bytes=16)
2 1 INDEX (RANGE SCAN) OF 'XIEMYSHAPETABIDX1' (NON-UNIQUE) (Cost=1 Card=1)

select MyShapeData
from MyShapeTab T
where MyShapeData is of( MyCircleType )
and treat( MyShapeData as MyCircleType ).Radius = 5

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=16)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYSHAPETAB' (Cost=1 Card=1 Bytes=16)
2 1 INDEX (RANGE SCAN) OF 'XIEMYSHAPETABIDX2' (NON-UNIQUE) (Cost=1 Card=1)

and i could have added any RI constraints as well ...
(in our case none of these requirements does apply at all)

as for the "now what" question: maybe that simplified "shapes" sample would make more sense if we replace "shape" with "product". all "products" have some properties in common with each other (such as "color"), and different kinds of "products" have certain specific attributes in addition (such as "radius" for "circles"). all "products" should be stored within one single "catalog". the main and most important purpose of having such a (more or less "generic") table to store "products" of all different kinds is to get that table "embedded" within the larger relational data model -- instead of modelling one supertype-table and several subtype-tables ...

does that make more sense to you now?
and which drawbacks do remain from your point of view?
could you become a "fan" now? ;o)

Tom Kyte
August 12, 2005 - 8:15 am UTC

you have my opinion already. I can create a single index on the xml to index anything. every time you add a new object, ......


I still don't get the data processing application that would use this.



You have my opinion (no).

but it 's not just "opinion"?

max, August 12, 2005 - 9:23 am UTC

i'm trying to see and understand some (more) reasons why ...

Tom Kyte
August 12, 2005 - 9:35 am UTC

I don't like objects to persist data. Most tools cannot in any way shape or form deal with them, there are hidden "magic" behind them. The syntax is obscure. You will not be ad-hocing this data from most things.

And applications that need this level of "generic", well I have an opinion about them as well.

once again: *BUT* ...

max, August 16, 2005 - 1:22 am UTC

... isn't it even *more* easy to support those tools by creating xml data from object type content as well as (re-)creating objects from such "serialized" xml data? apart from some (very simple) user defined constructors there 's no user code needed at all since built-in xmltype provides *all* methods for that ...

(as for "obsure" syntax: yes, i don't like some details of oracle's OR feature implementation either :o)

Tom Kyte
August 16, 2005 - 10:52 am UTC

sorry, you have my opinion in the matter.

Objects add lots of magical stuff, things "just happen", most tools cannot "see them", your application might be the only thing in the world to be able to use it.




no objects

Jim, August 16, 2005 - 12:45 pm UTC

The xml storage is a BSO (bright shiny object) and a hammer in search of a nail. Once you leave the Java centric developer world there aren't too many applications that can deal with data in that format. Much easier to just issue queries. Using the XML stuff adds a ton of over head and junk. Compare retrieving a large xml result set over a slow wire vs the same result set in a sqlplus session. Magnitude of difference.

Max, expand beyond the Java centric world. Java is very new and likly to be usurped by something else. (inevitable)

Another two penny worth

Gary, August 17, 2005 - 2:21 am UTC

Most Oracle databases don't use OR for storage.
Most Oracle developers won't be immediately/quickly familar with how to query/extract/tune/debug etc.

As such, you'll be handicapping your developers by going this route.



how about hiding OR storage behind views such as ...

A reader, August 17, 2005 - 3:04 pm UTC

create view MyCircleView as
select V.MyShapeData.Color Color, V.MyShapeData.Radius Radius
from (
select treat( T.MyShapeData as MyCircleType ) MyShapeData
from MyShapeTab T
where T.MyShapeData is of ( MyCircleType )
) V ;
/

create view MyRectangleView as
select V.MyShapeData.Color Color, V.MyShapeData.SideA SideA, V.MyShapeData.SideB SideB
from (
select treat( T.MyShapeData as MyRectangleType ) MyShapeData
from MyShapeTab T
where T.MyShapeData is of ( MyRectangleType )
) V ;
/

Tom Kyte
August 17, 2005 - 5:12 pm UTC

you have my opinion above........................



max, August 28, 2005 - 2:23 pm UTC

in order to get some figures about resource consumption and performance impacts of OR-storage i've compared two insert statements: one against a "normal" table containing three columns, the other against a table containing a similar object type column (three attributes).

i 've used your runstats measurement tool.
both inserts had been run once before measuring.
destination tables had been truncated before each test run.

two test cases had been run, reversing the order of insert statements to be measured.

1st test run:
-------------
Run1: insert into MyTabDO( MyObj ) select MyType( MyNum, MyTxt, MyDate ) from MyTabSO ;
Run2: insert into MyTabDN( MyNum, MyTxt, MyDate ) select MyNum, MyTxt, MyDate from MyTabSN ;

2nd test run:
-------------
Run1: insert into MyTabDN( MyNum, MyTxt, MyDate ) select MyNum, MyTxt, MyDate from MyTabSN ;
Run2: insert into MyTabDO( MyObj ) select MyType( MyNum, MyTxt, MyDate ) from MyTabSO ;

here are the results:
---------------------

Run1 ran in 46 hsecs
Run2 ran in 84 hsecs
Run1 ran in 54,76% of the time

Name Run1 Run2 Diff
STAT...prefetched blocks 38 366 328
LATCH..cache buffers lru chain 559 911 352
STAT...free buffer requested 559 911 352
STAT...physical reads 44 404 360
STAT...redo size 3,812,792 3,591,144 -221,648

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
25,439 25,889 450 98%

Run1 ran in 56 hsecs
Run2 ran in 150 hsecs
Run1 ran in 37,33% of the time

Name Run1 Run2 Diff
LATCH..checkpoint queue latch 0 201 201
LATCH..cache buffers chains 17,665 18,404 739
STAT...redo size 3,599,964 3,819,384 219,420

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
25,302 26,552 1,250 95%

what could have led to these results (with no "winner" at all)?

Tom Kyte
August 28, 2005 - 4:07 pm UTC

tkprof it too - bet you the second runs all hit log waits on a checkpoint not complete or something.

that is what it looks like to me. the first one filled up the buffer cache with dirty blocks and the second one had to wait to flush them :)

I woulc not expect huge differences for soething so simple

max, August 29, 2005 - 4:00 am UTC

two tkprofs (two separate sessions, same preparation steps) produced the following output:

insert into MyTabDO( MyObj ) select MyType( MyNum, MyTxt, MyDate ) from MyTabSO

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.20 4 29 0 0
Execute 1 0.26 0.78 16 1319 4544 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.27 0.99 20 1348 4544 100000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64

Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS FULL MYTABSO

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.02 0.02
log buffer space 6 0.04 0.08
db file scattered read 2 0.03 0.03
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.01 0.01
********************************************************************************


insert into MyTabDN( MyNum, MyTxt, MyDate ) select MyNum, MyTxt, MyDate from MyTabSN

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.28 0.95 332 1346 4385 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.28 0.95 332 1346 4385 100000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64

Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS FULL MYTABSN

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 31 0.04 0.30
db file sequential read 4 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.02 0.02
********************************************************************************

would you consider the differences measured with "runstats" to be related to physical "disk" I/O during execution phase then?

i'd expect select statements to behave more or less the same since they just reverse the process of object creation during inserts by turning object type attributes into "normal" columns. would you agree?

do you expect updates to perform any different?

would you expect any difference in terms of resource consumption and/or performance at all with "plain" objects like that, containing nothing but attributes of built-in types (except xmltype)?

what are the bottlenecks/drawbacks of implementing supertype-subtype-relationships like shown above with one table with one object type column and several object types derived from that object type?

Tom Kyte
August 29, 2005 - 5:03 am UTC

looks like a file system buffer cache kicked in here, the IO times in this case are super fast for the first, but not the second.

but something is not adding up -- why the huge elapsed vs cpu times without waits, is this machine "loaded" already and competing for CPU?


I would question the need for objects of just plain attribute types, why?

max, August 29, 2005 - 2:52 pm UTC

as for work load: no, it 's just my local system running XP and nothing but 9iR2.

as for "plain" objects: the two major benefits i see are: there 's no need to have one table per subtype (maintaining mutual exclusive entries across all subtype tables and such) and one could extend such data model very easy by introducing a new object type.

(still looking for reasons to *NOT* use OR-storage like that :o)

Tom Kyte
August 30, 2005 - 12:51 am UTC

a simple insert of a simple object isn't going to show much.

go ahead, model your system, test *that* out - but also try to use 3rd party tools against it as well ;)

Data management is not about "making the life of the sole programmer easier" in my opinion. I find this stuff obscure, non-intuitive, harder to use - less flexible (as to choice of tools) and adds (to me) no real value. Purely my opinion, you are free to form your own.

Caveat Emptor.

as for "obscure" ...

max, August 30, 2005 - 3:17 pm UTC

if one issues

create table ... of <some_plain_sql_object_type>

then all_tab_columns shows one "normal" column per object type attribute (and selecting such a table doesn't even return objects but "normal" columns instead) whereas

create table ... ( myobjcol <some_plain_sql_object_type> )

results in only one column of <some_plain_sql_object_type> showing up in all_tab_columns ...

how is the data stored (internally) with such object type columns?


Tom Kyte
August 30, 2005 - 3:27 pm UTC

do you have Expert One On One Oracle -- I cover the details in depth there

max, August 31, 2005 - 12:24 pm UTC

yes, i do.

now that i've reread some parts of chapter 20 i 've found that (at page 871 ;o):
<quote>
even though there is a little bit of magic and some hidden columns here, using *scalar* object types (no nested tables) in this fashion is very straighforward. this is the sort of we can live with
</quote>

that really made my day ;o)

Will the future trends be towards object oriented databases

mohannad, November 17, 2005 - 5:55 am UTC

Dear tom
I have 3 questions
1. I want to ask if the future trends will be towards the object oriented databases specially if we consider that it is relatively new comparing to the relational database.

2. And might Oracle provide an object oriented database in future.

3. is it true that object oriented database when it come to real application it will fail because it is difficult to map the real requirement to objects rather than tables and because of the weakness in retrieving data in a direct way like the relational databases and that why most of the companies world wide use relational databases (oracle, mysql, db2....).
Thanks


Tom Kyte
November 17, 2005 - 8:18 am UTC

1) relatively new? really? the paper on system-r and the paper describing smalltalk were written in the same year. They have the same sort of lifetimes here. Both "technologies" are about the same age.

I hope the future trend isn't in that direction, OO is so "application oriented", not data oriented. Some data fits into that paradigm just fine, but most - does not.

2) object relational - sure, we've been doing that for years and years. OO - doubtful.

3) the problem is more that OO tends to be application focused, but data isn't for "an application" in general, it is for "many many many applications". Applications come, applications go - the data stays forever. We need many views of the same information and OO tends to give a rather myoptic view of that data.

mohannad, November 17, 2005 - 8:35 am UTC

but a lot of papers in the internet , say that object oriented database is more appropriate for the object oriented application ,and they make a comparisons between the two database systems and how you write simple and shorter code when using Object oriented database comparing with the relational database if you are accessing the database using object oriented application

Tom Kyte
November 17, 2005 - 8:47 am UTC

there are lots of things written on the internet. I agree.


Lots and lots and lots of things...


I take the view that applications come.
Applications go.
Applications come again and go again.

But - the data, the data - oh, it stays and stays and stays and is used by application after application after application.


And if you store your data perfectly for "the first application", it'll not be good for the future ones.

And I don't see tons of data that isn't used by many applications (need many views of the same data).

See, my goal is the data - knowing that applications are like Mayflies
</code> http://www.ent.iastate.edu/dept/courses/ent201/diversity/ephemeroptera/090lifespan.html <code>
but data is more like a tree (long lived...)




mohannad, November 17, 2005 - 8:55 am UTC

>>And if you store your data perfectly for "the first >>application", it'll not be
>>good for the future ones.

are refering to the oo database ?

Tom Kyte
November 17, 2005 - 9:09 am UTC

I'm talking about storing data for "an application" in general. I don't store data for "an application", I store data for data's sake.

mohannad, November 17, 2005 - 9:16 am UTC

i read the following sentence frin the internet
"If youÂ’re modeling a Boeing 747 with an ODBMS, the relationships between aircraft parts are directly managed [by the database]," With a relational database, he said, you have to decompose the aircraft into tables and then join the tables when you need to reconstruct the aircraft.




Tom Kyte
November 18, 2005 - 7:12 am UTC

so?

point is?

do you have just one view of that boeing 747? Might you need to ask a question of the database "how many of my 747's have this bad part installed?" Now what? Send a message to every single 747 to ask it "hey, you got this part?"

How many widgets do you need to have in stock for your fleet? Number of widgets in inventory is a function of the number of widgets in use and their age. Now what? send a message to each 747 again to ask it that?



"if your modeling a company with an ODBC, the relationships between employees and departments are directly managed [by the database]," With a relational database he said, you have to decompose the company into tables and then join them tables when you need to reconstruct the company"

That would be true as well - but you know what, when I forget my badge and show up at the front door and give them my employee ID, it is sure nice to have an EMP table to query directly and NOT to have to message each and every DEPT instance to ask "does emp 1234 work for you?"

Ambitious question :-)

A reader, November 17, 2005 - 1:02 pm UTC

Tom, some unusual question for you :-) We think we built something interesting using OO and XML Oracle features and would like to share these ideas with others. But it's quite possible that we just THINK we built "something interesting" :-) What can you advise? Is it possible to send some brief review to Oracle magazine? Just point us to the right direction. Thank you very much!

Tom Kyte
November 18, 2005 - 9:48 am UTC

Oracle Magazine accepts unsolicitated ideas for articles - sure.

Beoing 747 nonsense

Tony Andrews, November 18, 2005 - 6:11 am UTC

> i read the following sentence frin the internet
> "If youÂ’re modeling a Boeing 747 with an ODBMS, the relationships between
> aircraft parts are directly managed [by the database]," With a relational
> database, he said, you have to decompose the aircraft into tables and then join
> the tables when you need to reconstruct the aircraft.

Yes, it is amazing what utter rubbish you can read on the internet, isn't it? This rubbish was obviously written by someone who has NO CLUE what a relational DBMS really is. DBMS means "Database MANAGEMENT System". It is all about having the system MANAGING data. So how can he say YOU have to do it?

forget boeing

A reader, November 18, 2005 - 7:24 am UTC

it's all airbus in the future, or ?

Tom Kyte
November 18, 2005 - 3:00 pm UTC

canadair makes some nice jets too. I spend lots of time on those.

???

Bob B, November 18, 2005 - 4:17 pm UTC

Having a Dr. Seuss moment?

"there are lots of things written on the internet. I agree.


Lots and lots and lots of things...


I take the view that applications come.
Applications go.
Applications come again and go again.

But - the data, the data - oh, it stays and stays and stays and is used by
application after application after application."

Tom Kyte
November 19, 2005 - 9:44 am UTC

laughing out loud, I must have been ;)


I guess I've just said it so many times, I was feeling pedantic...

Badge example is hillarious

mikito, November 18, 2005 - 7:59 pm UTC

This "assemble disassemble" quote is so misleading. As if we have to assemble anything when we query aircraft parts. Like aggregating the parts in OODB would magically make the aircraft fly (in cyberspace?)

Don't understand this...

A reader, March 24, 2006 - 12:44 pm UTC

Dear Tom!
I was playing with examples from documentation and found that I don't understand how it works :-((

create or replace type person_t as object(
ssn NUMBER,
name VARCHAR2(30),
address VARCHAR2(100))
not final
/
create or replace type Student_t under person_t(
deptid NUMBER,
major VARCHAR2(30)
)
/
create or replace type Employee_t under person_t(
empid NUMBER,
mgr VARCHAR2(30)
)
/
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 VIEW Person_v OF Person_t
WITH OBJECT OID(ssn) AS
SELECT ssn, name, address
FROM all_personattrs
WHERE typeid = 1;

CREATE VIEW Student_v OF Student_t 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 VIEW Employee_v OF Employee_t 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;

Now, if we insert records into all_personattrs like this:
insert into all_personattrs(typeid,ssn,name,address) values(1,1,'test1','address1');
insert into all_personattrs(typeid,ssn,name,address) values(2,2,'test2','address2');
insert into all_personattrs(typeid,ssn,name,address) values(3,3,'test3','address3');
and query Person_V view - we are getting all three records back. But the view says "where typeid=1"!!! I understand there is some magick here due to this view is object view, and actually behaviour is correct - we should get all the records, because this is the root of object hierarchy, but how is it really implemented???
Sorry if something like this was already discussed - tried to find the answer on your great site but wasn't successful (or patient).
Thanks a lot for all your help!!!

Tom Kyte
March 24, 2006 - 3:54 pm UTC

I don't see that

ops$tkyte@ORA10GR2> select * from person_v;
 
       SSN NAME
---------- ------------------------------
ADDRESS
-------------------------------------------------------------------------------
         1 test1
address1
 

sorry - forgot

A reader, March 24, 2006 - 4:09 pm UTC

sorry - forgot to tell that all_employeeattrs and all_studentattrs also had records:
insert into all_employeeattrs(ssn,empid,mgr) values(2,2,'manager');
insert into all_studentattrs(ssn,deptid,major) values(3,3,'major');
But actually - I think I found explanation while asking ;-)) Whenever you query person_v Oracle actually queries UNION over all the tables that are used for type hierarchy. This is really cool!!!
By the way, found one strange thing - when extracting script with dbms_metadata.getddl call, for all inherited views "under person_t" clause is lost :-(( (9i)
Please tell me if I'm wrong... And thanks!!!

Tom Kyte
March 24, 2006 - 4:18 pm UTC

you need to supply a complete, but shorter (you have stuff that is quite simply not relevant) and the entire thing.

what is not relevant?

A reader, March 24, 2006 - 4:25 pm UTC

Sorry - but could you please tell me what is unrelevant in the example I sent? I'm sorry - I forgot to add two inserts to it and added some comments, but other than that - what is unrelevant? Without inherited types and views you will not get three records, but just one...

Tom Kyte
March 24, 2006 - 5:36 pm UTC

You have extra views that don't seem necessary? You never shared your query.

I prefer cut and pastes from sqlplus, like I always give.

full script

A reader, March 24, 2006 - 8:12 pm UTC

Tom, sure you are right - I had to provide the full script. But it's interesting to find that you were thinking that other views are irrelevant to the example, exactly like I was thinking in the very beginning. But these inherited views make really huge difference! Query to the root view returns different results dependent on if you have inherited views or not! This was very big surprise for me, but I'm very happy that it works exactly like this. Below is the full script:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as frost

SQL> drop table all_personattrs;

Table dropped

SQL> drop table all_studentattrs;

Table dropped

SQL> drop table all_employeeattrs;

Table dropped

SQL> drop type student_t;

Type dropped

SQL> drop type employee_t;

Type dropped

SQL> drop type person_t;

Type dropped

SQL> create or replace type person_t as object(
  2   ssn NUMBER,
  3    name VARCHAR2(30),
  4    address VARCHAR2(100))
  5  not final
  6  /

Type created

SQL> create or replace type Student_t under person_t(
  2   deptid NUMBER,
  3    major VARCHAR2(30)
  4  )
  5  /

Type created

SQL> create or replace type Employee_t under person_t(
  2     empid NUMBER,
  3     mgr VARCHAR2(30)
  4  )
  5  /

Type created

SQL> CREATE TABLE all_personattrs
  2  ( typeid NUMBER,
  3    ssn NUMBER,
  4    name VARCHAR2(30),
  5    address VARCHAR2(100));

Table created

SQL> CREATE TABLE all_studentattrs
  2  ( ssn NUMBER,
  3    deptid NUMBER,
  4    major VARCHAR2(30));

Table created

SQL> CREATE TABLE all_employeeattrs
  2  ( ssn NUMBER,
  3    empid NUMBER,
  4    mgr VARCHAR2(30));

Table created

SQL> CREATE or replace VIEW Person_v OF Person_t
  2  WITH OBJECT OID(ssn) AS
  3    SELECT ssn, name, address
  4    FROM all_personattrs
  5    WHERE typeid = 1;

View created

SQL> CREATE or replace VIEW Student_v OF Student_t UNDER Person_v
  2    AS
  3    SELECT x.ssn, x.name, x.address, y.deptid, y.major
  4    FROM all_personattrs x, all_studentattrs y
  5    WHERE x.typeid = 2 AND x.ssn = y.ssn;

View created

SQL> CREATE or replace VIEW Employee_v OF Employee_t UNDER Person_v
  2    AS
  3    SELECT x.ssn, x.name, x.address, y.empid, y.mgr
  4    FROM all_personattrs x, all_employeeattrs y
  5    WHERE x.typeid = 3 AND x.ssn = y.ssn;

View created

SQL> insert into all_personattrs(typeid,ssn,name,address)
  2  values(1,1,'test1','address1');

1 row inserted

SQL> insert into all_personattrs(typeid,ssn,name,address)
  2  values(2,2,'test2','address2');

1 row inserted

SQL> insert into all_personattrs(typeid,ssn,name,address)
  2  values(3,3,'test3','address3');

1 row inserted

SQL> insert into all_employeeattrs(ssn,empid,mgr) values(3,3,'manager');

1 row inserted

SQL> insert into all_studentattrs(ssn,deptid,major) values(2,2,'major');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from person_v;

       SSN NAME                           ADDRESS
---------- ------------------------------ --------------------------------------------------------------------------------
         1 test1                          address1
         2 test2                          address2
         3 test3                          address3

SQL> --now - let's drop student_v inherited view
SQL> drop view student_v;

View dropped

SQL> select * from person_v;

       SSN NAME                           ADDRESS
---------- ------------------------------ --------------------------------------------------------------------------------
         1 test1                          address1
         3 test3                          address3

SQL> --now - let's drop employee_v inherited view
SQL> drop view employee_v;

View dropped

SQL> select * from person_v;

       SSN NAME                           ADDRESS
---------- ------------------------------ --------------------------------------------------------------------------------
         1 test1                          address1

SQL>  

Tom Kyte
March 24, 2006 - 8:23 pm UTC

interesting, reason 432143214 to not like this object "really cool stuff".

ugh, what an ugly situation. I have never used, nor will I use, this construct.

Interesting example, but only demonstrates why it would be dangerous to dive into this "stuff happens by automagical side effect" stuff.



why????????????????

A reader, March 24, 2006 - 8:38 pm UTC

Tom, why??????? This is absolutely great!!!!!!! As I understand, if you have inherited object view, Oracle automatically (!!!) changes query to the root view into the union query to the root view and all inherited views. This is exactly how I'd like to have it! If I query root view - I want to have ALL objects - root and all inherited. If I query inherited view - I have only a subset of data. Lets say I inherited new type. I created new table that I use for storing attributes related to this new type. But if I already have queries for my root view - I don't have to change a bit of code in it! I automatically have these new objects of new type included in any query to the root view. I simply inherit functionality. I don't have to change my root view to manually add one more union section - I have it automatically. And all this functionality includes method override!!! I really like this feature and see unlimeted ways of using it.


Tom Kyte
March 25, 2006 - 7:36 am UTC

automagic side effects by accident (same reason I wish we didn't have triggers, they bring more grief than joy) - hate it.

Have fun with it. I pray never to "inherit" such a system myself.

specific is good.
generic - virtually always evil (not to mention 'not really super fast' as well).

Just my opinion. I like straightforward, clear, easy to understand over "stuff happens by magic incantation over here"

it's not generic!

A reader, March 25, 2006 - 7:45 pm UTC

Tom, I disagree that this is "generic" approach. Just opposite. It's specific per each object type. Attributes, specific for inherited object type, are stored in separate table - exactly according to the normalisation principles of relational database. And if you are talking about side effects - it's a question is it good or bad. I have heard from some people that foreign key constraints cause side efects. And their logic is: I have a working system. Then new table is added with foreign key to one of the existing tables. And now I cannot delete records in my old table! Is it side effect? For me - it's a side effect that makes me happy.

Tom Kyte
March 25, 2006 - 8:06 pm UTC

I don't like the OR stuff, that is all.

foreign key constraints do not cause side effects, they enforce a rather STRAIGHT FORWARD constraint.

That is not a side effect, that is a business rule.

go back and re-read your original question to see why I don't like this "auto-magical stuff just happens by accident" stuff.

Sure it's not side effect

A reader, March 25, 2006 - 8:18 pm UTC

Tom,
I love your site and admire your examples and advices. But when you say "I don't like OR - that's all" - it doesn't sound like you :-)) Some people say "I don't like foreign key - that's all". OR is not perfect for every project, relational approach is not perfect for every project - they are coming from absolutely different places. And when Oracle brings some advatages of OR to the best relational database on Earth - this is GREAT!!! And, by the way - regarding re-reading of my original question. It was confusing BEFORE I understood how it is working. Now - absolutely clear, no place for side effects :-))
Thank you very much, Tom! Anyway - by asking question here I found the answer, as usual! (Even if you don't like OR :-))

Tom Kyte
March 26, 2006 - 8:13 am UTC

It is the same reasoning for my utter and complete dislike of C++ - a thing I can program in but would never consider doing so.

Things happen by total side effect, by magic. Unless YOU wrote it, you cannot understand it.

I'm a more linear guy I guess. I (as I get older and others get younger even more so) prefer the utterly STRAIGHT FORWARD and NON-CONFUSING.

Same reason I don't like triggers - even though there is a time and place for them - I would prefer to not have them at all since they get so wickedly abused so often. It does make it easy to answer some rather nasty looking questions though

Question: "Hey, I do this - but Oracle does that - whats up with that is this a bug"
Answer: "What triggers do you have"
Response: "oh"

This is my opinion, you are free to use it - I just hope I never inherit it (all pun intended).



OO in and of itself?

Khalid, March 26, 2006 - 12:46 pm UTC

Tom
Do you also dislike C? you know we have a (void *) pointer that we can make it point to virtually (no pun intended) anything at run time. Personally I loved void *.

IMO this feature seems to be working a little like C++. The parent class has a virtual function, and at run time the right method from a derived class is called. We can extend that philosophy a little to call pl/sql functions. SELECT f(ssn) from person_v at run time. The possibilities are endless.

There is a tremendous amount of flexibility offered at a certain price, but at the end of the day, what are you trying to achieve, is OO (or OR) in and of itself, is it good enough for you?

We have been hearing ad-nauseum from this irritating OO community, about the touted benefits of OO, and its reusability, but here we see a case where it can become nightmare for the maintainer.










Tom Kyte
March 26, 2006 - 1:26 pm UTC

I love C :)

The only time I liked void * was when a friend of mine came up with a band named simply:

The static void *

I thought that was great. You can write cruddy obscure code in C, you can write good, clean, modular, understandable code in C.

Replace C with Oracle.




You want reusable code? See: stored procedures




Data Design /Data Model

Thiru, June 23, 2006 - 11:41 am UTC

Hello Tom,

A legacy system is under consideration to be ported to an Oracle db. So it involves designing and modeling the database. a. How would one go about designing a database from such systems. Can you please get into some details with an example.

b. Also do you advise/use of any data modeling tools?

c. Is database design and model are two different areas?

Thanks for the time Tom.



Tom Kyte
June 23, 2006 - 1:21 pm UTC

a) that is the subject of university courses, many books, experience.

Perhaps the easiest answer for you is "get an experienced person on your team that has 'been there, done that' (successfully) to mentor and teach you".

That is the safest answer as well.

b) sure... many tools are there, different tools, different jobs.

c) they are likely 'the same' in your context



strongly encourage you to "get a mentor" before you end up on a site like:

</code> http://thedailywtf.com/ <code>

:) seriously - that is not sarcasm, just advice I've given over and over.

A reader, June 23, 2006 - 4:05 pm UTC

Thanks Tom for the advice.

Data goes away too

Milivoj Milani, July 01, 2006 - 6:17 am UTC

"I take the view that applications come.
Applications go.
Applications come again and go again.

But - the data, the data - oh, it stays and stays and stays and is used by
application after application after application."

Absolutelly untrue. Database tables and schemes are in 99% of cases migrated to new modeled schemas for use by those new applications. That means just one thing. Data model IS and ALWAYS will be application dependant, otherwise we will have just one WORLD DATABASE.
Tables change - data changes and adapts. I have never seen another application built on top so called "perfect relational model".

Also, my opinion is - C++ is easier for developers then C. Especially on big projects. Have a look at Oracle eBusiness Suite - you will see what huge amounts of procedural code can create. Absolute nightmare.

Tom Kyte
July 01, 2006 - 8:07 am UTC

Only because they were modeled for - well - applications in the first place.

It is not true that 99% of the cases they are migrated. As the model changes, the schema might evolve - but the schemas that suffer from this most?

The ones that were not thought out in the first place and were build to satisfy the needs of a single application.

Pay a bit of attention to the data in the first place and you won't have your self fullfilling prophecy.


C++? The list of abysmal failures with that on large scale projects is large and long. I've written in both. I'll stick with C.

Re: Data goes away too

Rick Davis, July 03, 2006 - 8:10 am UTC

I couldn't agree more with Tom! Experienced Data Modelers never model 'process' or the 'application', they model the data!! Experienced Data Modelers become very aware of and knowledgeable about the process/application but only so they can extract the required entities and attributes from that process/application and the business rule relationships between them. In my experience, well modeled databases probably change existing structure about 10% of the time, sometimes because of errors or poor design, but usually because some 'process' sneaked into the design which never should have been there. Regards, Rick

Data does not "go away"

Billy, July 04, 2006 - 9:38 am UTC

Milivoj Milani from Republic of Croatia, Balkans wrote:

> Absolutelly untrue. Database tables and schemes are in 99%
> of cases migrated to new modeled schemas for use by those
> new applications. That means just one thing. Data model IS
> and ALWAYS will be application dependant, otherwise we
> will have just one WORLD DATABASE.

You're missing the point. Completely. No-one is advocating a single fixed relational database model with tables and tupples that cater for every data modeling need in the world. Relation design is formal design approach you use to translate business entities into an efficient and effective data model for an information system .

What comes first? The application or the data? (and btw, the chicken and egg thing has been resolved and I was right there too ;-) )

Answer. The data.

The first thing that one does in software engineering when getting into the design phase (after the business requirements have been spec'ed) is to model the business entities and determine the relationships between them. Only once the model is in place, can you look at the application design.

So you say that the "data changes/migrates". If it does, it is because the actual business itself changes. There are new business entities or modified ones.

**Business requirements drives the changes in the data model** (and not the application!)

Depending on the nature and focus of the business, a data model can be very static, or very dynamic. Most systems I have worked with tended to the former - largely static data models that have few changes over time. And when there are drastic changes, it always have been an exception to the norm. These are for example due to changes in legislation, or a merger with another company, or expanding into a new market.

What does NOT drive the data model in ANY way or form is the application. If it does, then some very fundamental software engineering principles are either grossly misunderstood, or stupidly ignored.

If you need to "fix" the data model with each new application release, then there is something inherantly wrong with your data modelling approach in the first place.



difference between a HAS-A relationship and an IS-A relationship and give an example of each

mal, July 21, 2006 - 3:55 pm UTC

difference between a HAS-A relationship and an IS-A relationship and give an example of each

Can you please help on above

Tom Kyte
July 23, 2006 - 7:56 am UTC

I think you are using terminology from a class or some modeling tool right - in that "has-a" is not an acronym, but the words "has a"

X has-a Y

A record in the table X may be the parent of a record in Y, DEPT has-a EMP set of records associated with it. Depending on who you ask - has-a might specify a 1 to 1 relationship of related objects.


A is-a B

A student is a type of person, A professor is a type of person. The person table would be the parent table of the subtype tables (or set of attributes) Student and Professor.

Structuring Business Logic

Peter Eden, January 02, 2007 - 1:20 am UTC

I have seen a number of articles comparing stored procedures with packages, and packages with triggers, and here OR database with relational database. But I think it is in this forum I should raise the question. What technology is best for structuring business logic: procedures, packages, triggers or methods?

I take note of the discussion here in favour of a well-designed flat relational schema that supports many applications. I agree with Tom that different OR designs are application dependent, so for the data design a flat schema provides maximum flexibility. However when it comes to design business logic, I believe methods allow for minimum redundancy of code by locating the business logic close to the data. Triggers of course are ultimately closer the data, but I agree that database triggers are hard to understand and maintain as the logic becomes more complex. So it would seem the optimum arrangement would be to put the business logic into methods that access the data through object views defined on a flat schema.

In short relational is good for data storage and querying, but OO is good for business logic. Hence we have OR.
I am curious to hear your opinion.

Tom Kyte
January 02, 2007 - 8:02 am UTC

packages.

I hate triggers (automagical side effects that happen mysteriously and are often forgotten about leading to interesting "bugs")

procedures are not good from a coding/modularity/dependency perspective.

packages are the only way to go.

About 10g

K P Ratnaker, January 03, 2007 - 8:14 am UTC

Hi tom,
i am working oracle 10g i am not getting more question on oracle 10g

Tom Kyte
January 05, 2007 - 8:09 am UTC

eh?

Methods for Business Logic?

Peter Eden, January 09, 2007 - 1:42 am UTC

>I hate triggers ..... procedures are not good from a coding/modularity/dependency perspective. packages are the only way to go.

Yes but what about Methods? We can separate spec and body, but we need types and object tables. So what do you think about methods? Oracle seems to have put a lot of effort into developing O-R SQL and PL/SQL, but is anybody using it?
Tom Kyte
January 11, 2007 - 9:17 am UTC

I use some of the Object stuff in my PLSQL code - never to persist (store data).

Packages are great for business logic.

Objects are good for new "data types", like XMLType for example.

References between transient objects

Zlatko Sirotic, January 12, 2007 - 3:38 am UTC

"I use some of the Object stuff in my PLSQL code - never to persist (store data)."

In my opinion it is a priority to add references between transient objects
(and then, perhaps, even a garbage collector).


Regards,
Zlatko Sirotic

data modeling for a large erp system

Rajesh Kochath, February 18, 2007 - 2:11 pm UTC

dear Tom
i am right now involved with a team and we are in the process of re-engineering the data model of a very large application(1400 tables,600 packages,2000+ application functions,3000+users). the database of this application has grown over the years(20+) and it is currently in oracle 10g with not so normalized state. Some of the team members want to remodel the database for the 24/7 uptime and ever changing business requirements. The idea they are floating is to group the attributes of any table into 3 categories. 1 - Flags(with values like yes/no or entry/confirm/close,etc) 2. Relationships to other tables.(like country is related to currency, state is related to zip codes,etc) and the 3- is the specific attributes of the specific table(like ISO code of the country, population in the country, etc). For each of these three categories there will be an attribute definition table and the actual data table. Highly appreciate your view/advise/comments on the pros & cons of this approach.
Tom Kyte
February 18, 2007 - 8:05 pm UTC

I didn't follow the idea entirely. (or if I did, it sounds like the "generic" model and I'm not a fan of that)

Generic Models

Bill C., March 14, 2007 - 6:11 pm UTC

Rajesh,

Tom and others have written about the dangers of generic data models. He was probably unable or too tired to spell it all out again. Listen to the experience of those who have preceded you: DON'T DO IT! If you are responsible for that data model, you will probably lose your job when it goes into production unless you can manage to get reassigned or quit before then, in which case you'll just lose your network of friends at that company.

In the last three years, I've personally heard of, run across, been asked to consult to shoot down, and inherited a handful of generic object models. In every case, they were unmitigated disasters. My currently employer almost lost their entire 50M dollar business over theirs, a generic model foisted on them by an overbearing Java architect who had the ears of the executives. Had they bothered testing with a dataset large enough to represent actual usage in the wild 5 years ago, this aberration of a "data model" would have been forcibly aborted before seeing the light of day.

Generic object models might actually perform when quantum computers are available. Until then, they only work for the most trivial of applications. But even when the hardware is infinitely scalable, the other issues (like lack of integrity constraints, mind-numbingly complex and unreadable SQL statements, scores of massive composite indexes and the related problems that brings, etc.) will persist.

If they continue to insist on a generic object model, it is in your best interests to quit. But before you do send a memo to the executives with a prediction as to what will happen. Advise them to stress test it long before release. Leave them your number. You will gain all sorts of respect and maybe a nice fat contract when their business goes down in flames 6 months after release and they need you to come give them a model that works.

Good luck, my friend. You're going to need it.

Use of object view

Stewart Bryson, March 15, 2007 - 1:03 pm UTC

I'm trying to figure out what use an object view is. I understand it conceptually... but can't make much sense out of it practically. And the documentation at http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjvew.htm#g444865 just shows creating one... not using one.

So suppose I have:

create or replace type testtype as object (tt_num number, tt_string varchar2(30));
/

create table testtab (tt_num number, tt_string varchar2(30));

CREATE OR REPLACE VIEW test_ov OF testtype
WITH object identifier (tt_num) AS
SELECT tt_num, tt_string
  FROM testtab;

insert into testtab values (1,'test string 1');


Now what? I would like to define a variable to hold the object and read an instance of the object into it. Then, I would like to use the methods (if they existed) on that object. Obviously, the below doesn't work, but I believe it makes the point of what I am looking for:

DECLARE
   o_test testtype;
BEGIN
   NULL;
  SELECT <instance_of_object>
     INTO o_test 
     FROM test_ov
    WHERE tt_num=1;
END;
/


Am I missing the real application of the object view?

If I was ever able to instantiate an object successfully, what happens when the attributes are changed? Would that automagically update the underlying table(s), and does that happen after a commit?

Thanks Tom.

Tom Kyte
March 15, 2007 - 1:10 pm UTC

what happens when you update? it depends - if the underlying view was updatable - it would update. if not and you did not code any instead of triggers to process the update - it would fail.

if you have access to Expert One on One Oracle - in my chapter on Object Relational stuff - i discuss Object Relational-Views in some detail.

basically:

quote:

Object Relational Views
This is a fairly powerful feature for those of you who want to work with the object relational features, but still must present a relational view of the data to many applications. This allows you to use the standard VIEW mechanism to synthesize objects from relational tables. You don't have to create tables of a TYPE, with all of the mysterious columns and such ¿ you can create a view of standard tables you have created (and probably already have). These views will behave just like an object table of that type would ¿ without much of the overhead of hidden keys, surrogate keys, and other nuances.

In this section, we will use the EMP and DEPT tables to present a department-oriented view of the data. This is similar to the example of the nested table we used in Chapter 6 on Tables, where by we had the EMP_TAB_TYPE as a nested table of EMP_TYPE, and the DEPT table had a column of this nested table type. Here, we will model the EMP_TYPE and the EMP_TAB_TYPE once again, but we will also create a DEPT_TYPE object type as well and a view of that type.

It is interesting to note that this approach of using object views allows us to have the best of both worlds (relational and object relational). For example, we might have an application that needs a department-oriented view of the data. Their view starts at the department and employees in the department are naturally modeled as a collection inside the department. Another application however needs a different perspective. For example, when you walk up to a security guard and identify yourself as an employee, they will need to have an employee-oriented view of the data. Department in this case is inferred by the employee, not the other way around where the view was that department infers employees. This is the power of the relational model ¿ many different views can efficiently be supported simultaneously. The object model does not support many different views of the same data as easily (if at all) or efficiently. By using many different object views of the relational data, we can satisfy everyone.

thanks

Stewart Bryson, March 15, 2007 - 3:01 pm UTC

I think I have it:

SQL> CREATE OR REPLACE TYPE testtype AS object (tt_num NUMBER, tt_string VARCHAR2(30));
  2  /

Type created.

Elapsed: 00:00:00.01
SQL> 
SQL> DROP TABLE testtab;

Table dropped.

Elapsed: 00:00:00.04
SQL> 
SQL> CREATE TABLE testtab (tt_num NUMBER, tt_string VARCHAR2(30));

Table created.

Elapsed: 00:00:00.04
SQL> 
SQL> CREATE OR REPLACE VIEW test_ov OF testtype
  2  WITH object identifier (tt_num) AS
  3  SELECT tt_num, tt_string
  4    FROM testtab;

View created.

Elapsed: 00:00:00.03
SQL> 
SQL> INSERT INTO test_ov VALUES (1,'test string 1');

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> DECLARE
  2     o_test testtype;
  3  BEGIN
  4     SELECT value(t)
  5       INTO o_test
  6       FROM test_ov t
  7      WHERE t.tt_num=1;
  8  
  9     o_test.tt_string := 'Test string 2';
 10  
 11     UPDATE test_ov t SET value(t) = o_test WHERE t.tt_num=1;
 12  END;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> 
SQL> SELECT * FROM test_ov;

    TT_NUM | TT_STRING
---------- | ------------------------------
         1 | Test string 2

1 row selected.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> SELECT * FROM test_ov;

    TT_NUM | TT_STRING
---------- | ------------------------------
         1 | Test string 2

1 row selected.

Elapsed: 00:00:00.00
SQL> 
SQL> UPDATE test_ov t SET t.tt_string='Test string 3' WHERE t.tt_num=1;

1 row updated.

Elapsed: 00:00:00.01
SQL> 
SQL> SELECT * FROM test_ov;

    TT_NUM | TT_STRING
---------- | ------------------------------
         1 | Test string 3

1 row selected.

Elapsed: 00:00:00.00
SQL> 


Is there an easier way?

Thanks.
Tom Kyte
March 16, 2007 - 2:55 pm UTC

easier way than what?

I'm not sure what your goal is

Object identifier

Stewart Bryson, March 16, 2007 - 3:35 pm UTC

Obviously the statements I wrote are not difficult... so "easy" was probably not the right word.

Once I select the object I want to work on out of the object table and update the attributes and use the methods, I thought it might be easy to "update" the object easily using something like the object identifier. That would be easier than de-referencing the object using the VALUE function to dictate which object to update according to one of the attributes.

I've done some research since my last posting, and it looks like the object identifier is completely off limits. But I can't see the importance of specifying which relational column maps to the object identifier if you have no intention of using it.

Thanks as always.

Nested object tables

Laxman Kondal, July 20, 2007 - 1:45 pm UTC

Hi Tom,

We have a project in 9i using Oracle text where 17 tables are object type and few columns in the table are again object type, and again some columns in that are object type - up to 4 levels down.

I need to copy them to different server and convert them into heap organized table. I did full export from production in Window server to Linux and found columns with nested and sub nested type have no values at all.

For nested type column I will create a child table and again child table for nested to nested column till last column of object type. That will have 4-5 tables from one table.

Colud you please help me how do I get the data out from production server from super nested table in Oracle 9i on Window server to Linux box.

Thanks and regards.
Tom Kyte
July 21, 2007 - 9:01 am UTC

you would have to create relational views using flattened nested tables, then you can query the views (over a dblink even) to move the data.


stored procedure interface

James Su, January 27, 2010 - 6:20 pm UTC

Hi Tom,
About the interface between a stored procedure and J2EE code, which of the following do you recommend:
1. flat interface, for example:
input: p_firstname in varchar2,p_lastname in varchar2, p_address in varchar2
output: sys_refcursor to return a result set

2. object interface, for example:
input: p_object_employee employee_type
output: table_of_employee (which is a nested table)

Thank you!

Tom Kyte
January 29, 2010 - 3:26 pm UTC

how about

3) both, since sometimes #1 is best and easiest and most sensible and sometimes #2 is best and easiest and most sensible.


If I just had three inputs, simple enough.

If I 'just' had 300 inputs, totally different story...

re: stored procedure interface

Stew Ashton, January 30, 2010 - 2:27 am UTC


The problem with using PL/SQL "object" interfaces is that JDBC doesn't have any standard way of converting them to Java objects. There is so much work that Oracle gives you tools like Jpublisher to generate code for the conversion.

This work would have to be redone for the other languages, object or not, that would access this data.

That is why my "default" choice would be scalars for input and resultset for output, since they are standard and directly supported by most/all drivers and languages.

Now why would you want to do it any other way? I think because objects can have "n" references to "1" thing : many EMP objects can reference the same DEPT object. In a standard resultset, you join EMP and DEPT and you get "n" copies of the same DEPT data, one copy per line.

You could return an "object" view with one line per DEPT and a nested table of EMP; great, but requires conversion.

Another approach would be to return "nested XML", which could return one DEPT followed by its EMPs, then you let Java worry about the XML. It looks like DBMS_XMLGEN will get you the XML, at least in 11.2 : http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10492/xdb13gen.htm##BBABFCBFF Hope this helps.
Tom Kyte
February 01, 2010 - 9:45 am UTC

... then you let Java worry about the XML ...

and how pray tell would that be easier/better than jpub that creates a java class that interfaces with the object?

XML - eXpensive Markup Language.

(the word language there always grates on me, language? really?)

Correction on stored procedure interface

Stew Ashton, January 30, 2010 - 2:38 am UTC


It looks like DBMS_XMLGEN can already generate nested XML in Oracle 10GR2 : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1613

XML

Sokrates, February 01, 2010 - 10:19 am UTC

"eXpensive Markup Language" - is that what it stands for ?

I didn't know that, I always thought

XML = "eXpensive Markup bLather"

Letting "Java worry about the XML"

Stew Ashton, February 01, 2010 - 4:07 pm UTC


Tom, after I said "... then you let Java worry about the XML ...", you asked:

"and how pray tell would that be easier/better than jpub that creates a java class that interfaces with the object?"

I admit I don't know enough about either alternative to answer you satisfactorily; still, there are two things I don't like about the Jpublisher method:
- you have to generate and include that Java code
- once you do, you have an interface for Java and only for Java.

I was thinking that a more or less generic XML output might be easier to convert to Java objects, and might also be more usable by other programming languages. These were mere hypotheses. Indeed, converting from Oracle object to XML to Java object would mean managing two conversions whereas Jpublisher does the job with one.

So, not much to say for myself on this topic.
Tom Kyte
February 01, 2010 - 4:18 pm UTC

... - you have to generate and include that Java code ...

and you don't have to write anything - perfection.


... - once you do, you have an interface for Java and only for Java. ...

true, but the other languages actually tend to make it easier than java, since with java we are stuck with jdbc.


and push comes to shove they can all:

begin
your_procedure( your_type( ?, ?, ?, ?, ?, ...., ?, ? ) );
end;


which makes it not less hard on them (true, you lose named notation, that is a downside) but a lot better on the plsql side :)

object database

A reader, February 01, 2010 - 5:01 pm UTC


Implementing inheritance - what would be your approach?

Vitaliy, April 22, 2010 - 5:33 pm UTC

Tom,
I am debating myself (developing multiple personalities, I guess) with a question on implementing inheritance in relational database. I agree with you on the topic, that I like my data to be stored in relational way, but possibly exposed to different applications as OR views.
Taking the Person, Employee and Student example into consideration, I see at least 3 ways to implement it in relational way:
1. Have a sparsely populated records in ONE table with all named attributes defined for Person and ALL Person sub-types.
2. Still have 1 table with only COMMON attributes of a Person being defined as named attributes and the rest being generic attributes like Attribute1..Attribute30. Then I would have another table for the metadata, where I would define meaning of the attributes1..30 for each sub-type.
3. Have a separate table for each sub-type with a FK into Person table

The question is - what type of a system would YOU like to inherit?


Tom Kyte
April 23, 2010 - 8:33 am UTC

... Taking the Person, Employee and Student example into consideration, I see at
least 3 ways to implement it in relational way:
....

I stopped there and said "I see only one logical way, a single table"....


The others don't make sense from a performance, ease of use or implementation perspective.

Do you really want to have to do a join that isn't necessary to get a student? Or a three table join to get a student who is an employee?

This is where the relational database gets hit, someone picks up a data modeling tool - they model their 'objects' and click 'generate'.

Then say "it is slow, joins are slow, databases are slow, let's use XML" or something similar.


Just a single table - add check constraints to make sure that if you have some student attributes, you have all you must have - and if you have some employee attributes - you have all you must have. If you want to spend time "optimizing" figure out which subtypes are the least frequently occurring and make those be the last columns in the physical table (since null attributes at the "end" of the table take zero bytes to store)...

Implementing inheritance - what would be your approach?

Vitaliy, April 23, 2010 - 11:59 am UTC

Tom,
Thank you for your followup. To be honest I was leaning towards approach #3 with separate tables for each sub-type, but your example with Student being an Employee put it in a very good perspective - I would rather deal with other developers screaming about "too many empty fields" than the same developers screaming about poor performance of convoluted queries.

Thank you,

Vitaliy
Tom Kyte
April 23, 2010 - 2:21 pm UTC

if the developers 'scream', create views for them.

Give them a person, student, employee and student_who_is_also_employee view. All are based on one table. All are efficient. All are really the same. But it'll make them scream less.

do not let them join the student view to the employee view. well, maybe you can in 11g whereby the optimizer gets wise to their tricks:

ops$tkyte%ORA11GR2> create table t ( x int primary key, person int, student int, employee int );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace view person as select x, person from t;

View created.

ops$tkyte%ORA11GR2> create or replace view student as select x, student from t;

View created.

ops$tkyte%ORA11GR2> create or replace view employee as select x, employee from t;

View created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from person, student, employee
  2  where person.x = student.x and student.x = employee.x;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    52 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    52 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Nice, three table join, turned into a single table access.....


That'll not happen in older releases.

object

A reader, April 24, 2010 - 1:02 pm UTC


A reader, July 30, 2010 - 8:33 am UTC

A table consists of an 'object' as column and that object consists of collection of objects as column
And that object consists of nested tables - I want to use CTAS on that TABLE to create a table with some data
- is it possible in this scenario to use CTAS ?


A table LR_PROV_ORDERDETSWIP consists of a column : ACCOUNTMASTER OBJ_LINERENTALACCMASTER
The Object OBJ_Linerental master again consists of a column mLTList COLL_LT
(COLL_LT is a collection of OBJCETS) and also 3 nested tables:mASList COLL_AS,mDDIList COLL_DDI,mMiscList COLL_MISC




TABLE DEFINITION :

CREATE TABLE LR_PROV_ORDERDETSWIP
(
CUSTORDERDETSID NUMBER(10),
ORDERID NUMBER(10) NOT NULL,
TRANSACTIONNO NUMBER(2) NOT NULL,
ERS_INPAYLOADTYPE NUMBER(1),
ERS_INPAYLOAD SYS.XMLTYPE,
ERS_OUTPAYLOADTYPE NUMBER(2),
ERS_OUTPAYLOAD SYS.XMLTYPE,
CURRENTBUSINESSSTATUS NUMBER(2) NOT NULL,
SENTDATETIME DATE,
RESPONSEDATETIME DATE,
SUSPECTCONDITION NUMBER(1),
ONTIMETRIGGER NUMBER(1),
CREATED DATE DEFAULT SYSDATE,
ACCOUNTMASTER OBJ_LINERENTALACCMASTER---> Object
)


OBJECT DEFINITION ( Observe the mLTList COLL_LT)
===============

CREATE OR REPLACE
TYPE "ERS_TST2"."OBJ_LINERENTALACCMASTER" AS OBJECT (
mLRACCID NUMBER(10),
mREFCLI VARCHAR2(24),
mPTTACCNO VARCHAR2(10),
mERSID NUMBER(10),
mORIGPTTACCNO VARCHAR2(10),
mERSORDERNO VARCHAR2(48),
mMessageDate DATE,
mSDTQuantity NUMBER(4),
mServiceAddress CUSTOMERADDRESS,----->nested table
mTargetAddress CUSTOMERADDRESS,----->nested table
mLTList COLL_LT, ----> colllection of Objects
mResellerAccNo NUMBER(6),
mCreated VARCHAR2(50),
mCreatedBy VARCHAR2(50),
mUpdated VARCHAR2(50),
mUpdatedBy VARCHAR2(50),
mLRAMField1 VARCHAR2(50),
mLRAMField2 VARCHAR2(50),
mLRAMField3 VARCHAR2(50),
mLRAMField4 VARCHAR2(50),
mLRAMDate1 DATE,
mLRAMDate2 DATE,
MEMBER PROCEDURE InsertIntoDB,
MEMBER PROCEDURE ProcedeCH,
MEMBER PROCEDURE ProcedeCDD,
MEMBER PROCEDURE ProcedeLTI,
MEMBER PROCEDURE ProcedeCN,
MEMBER FUNCTION CreateXMLNode return XMLType,
MEMBER FUNCTION ProduceXML(in_MSGTYPE IN VARCHAR2) return XMLType,
MEMBER PROCEDURE InsertIntoBillingTables )



CREATE OR REPLACE TYPE "COLL_LT"
IS TABLE OF OBJ_LINETYPES
/
the definition of OBJ_LINETYPES :
=================================
CREATE OR REPLACE
TYPE "ERS_TST2"."OBJ_LINETYPES" AS object(
mBItemId NUMBER(10),
mCurrentStatus VARCHAR2(2),
mActionFlag VARCHAR2(3),
mLeadCLI VARCHAR2(24),
mBChannel VARCHAR2(24),
mOldTeleNo VARCHAR2(24),
mLRAccId NUMBER(10),
mLTId NUMBER(10),
mSTDCode VARCHAR2(4),
mASOption VARCHAR2(1),
mQuantity NUMBER(4),
mCLIOrderType NUMBER(1),
mMSNQuantity NUMBER(3),
mDDIQuantity NUMBER(3),
mDDIType NUMBER(5),
mCreated VARCHAR2(50),
mCreatedBy VARCHAR2(50),
mUpdated VARCHAR2(50),
mUpdatedBy VARCHAR2(50),
mLTField1 VARCHAR2(50),
mLTField2 VARCHAR2(50),
mLTField3 VARCHAR2(50),
mLTField4 VARCHAR2(50),
mLTDate1 DATE,
mLTDate2 DATE,
mASList COLL_AS,
mDDIList COLL_DDI,
mMiscList COLL_MISC,

member PROCEDURE insertintodb(in_notamdate IN DATE, in_ersorderno IN VARCHAR2),
member PROCEDURE ceaseservice(in_notamdate IN DATE, in_ersorderno IN VARCHAR2),
member PROCEDURE changeddis(in_notamdate IN DATE, in_ersorderno IN VARCHAR2),
member PROCEDURE changeas(in_notamdate IN DATE, in_ersorderno IN VARCHAR2),
member PROCEDURE changecn(in_notamdate IN DATE, in_ersorderno IN VARCHAR2),
member FUNCTION createxmlnode(in_msgtype IN VARCHAR2) RETURN xmltype)


Tom Kyte
August 02, 2010 - 8:39 am UTC

yuck.


but yes, you can. I don't recommend it (nested tables) at all - ugly, not necessary.

But just use create table as select

ops$tkyte%ORA11GR2> create or replace type myObj as object ( x int, y int )
  2  /

Type created.

ops$tkyte%ORA11GR2> create or replace type myTab as table of myObj
  2  /

Type created.

ops$tkyte%ORA11GR2> create or replace type myObj2 as object( a int, b myTab )
  2  /

Type created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t ( xx myobj2, yy int )
  2  nested table xx.b store as t_b_tab;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t2
  2  nested table xx.b store as t2_b_tab
  3  as
  4  select * from t;

Table created.



you'll be propagating a bad idea really fast though.

A reader, May 25, 2012 - 5:17 am UTC

Hi Tom,

What do you meant by dynamic method dispatch in PL/SQL? Could you give me a simple example to understand this concept, please?

Regards,
Boris
Tom Kyte
May 25, 2012 - 9:19 am UTC

can you give us the link on this page (all reviews have a bookmark link) so I can have a bit of context.

you are the first one to type in dynamic method dispatch

A reader, May 26, 2012 - 11:33 am UTC

Hi Tom,

Thanks for your reply. Here is the link from the documentation for your refernce:-

http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjbas.htm#i468270

Regards,
Boris