Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: A searchable datastructure for represeting attributes?

Re: A searchable datastructure for represeting attributes?

From: Mikito Harakiri <>
Date: 1 Mar 2002 15:34:48 -0800
Message-ID: <>

IMHO, there is nothing wrong with Metamodel. One table, many views!

Therefore, a relation



 Joe | 200
John | null



Id | Domain | Value
 1 |  Rel   |  Emp
 1 |  Name  |  Joe
 1 |  Sal   |  200
 2 |  Rel   |  Emp
 2 |  Name  |  John

Note that there is no 'null' problem anymore. Relations are nothing more than just views upon 'Things'. Those views could be the only artifacts available to application developer -- that way you avoid nasty typing problems that Celko describe. If we have relations as views, then what query complexity are you talking about? And finally, if we index this relation the cost of extra selfjoins would be negligible.

Now, in traditional relational model we can create composite indexes. It doesn't seem like those indexes have any counterparts in Metamodel. Probably, the only way to deal with the problem is materializing views and building composite indexes upon those views. This causes severe implications on scalability (if one is aware that materialized views should be properly locked;-).

Actually, it can be both ways: one can create 'Things' as a view over relational schema. However it sounds ridiculous to redefine this view each time application developer adds one more relation, or column.

I think about metamodel every time I query catalog. It is notoriously difficult to search for simple things there (hey, where is the google of the catalog?), the major reason for this is that I'm unable remembering the names of 500 tables and views...

If metamodel is not a legitimate as a relational model, then why can't it be just a physical storage model? Whenever I see those stupid cost formulas counting blocks and figuring out how many records in the block are, etc, I think about metamodel as well.

no_spam_for_me_at_thankyou.very_much wrote in message news:<3C7D064F.C304F9D4_at_thankyou.very_much>...
> THE QUESTION: Is there any good datastructure for representing
> attributes which has the advantages of approach_1 and the search and
> sort convenience of approach_2 below? Seems like a problem many people
> would
> have run into.
> 1)
> objectid key value
> 78 phone 555-1212
> 78 addr Main Street
> 79 phone 222-2222
> 79 addr Green Street
> (keys could be normalized of course but I skip that for this example)
> 2)
> objectid phone addr
> 78 555-1212 Main Street
> 79 222-2222 Green Street
> The first approach has several advantages
> a) It is flexible. That is you do not need to change the schema to add
> new attribute types.
> b) It is normalized in a sense that if an attribute does not have a
> value you can represent it by its absense in the table istead of
> duplicating NULL in every feild that does
> not have a value. In my case this is actually a critical thing
> because there are a lot of attributes whose values are not set.
> Overall the first approach serves my case much better than second
> approach. However it has important disadvantages in that the search by a
> combination of attributes as
> well is sort by attributes is very cumbersome and slow. For example if I
> wanted to find all objects where phone number is 555-1212 AND address
> equals "Main Street" I would need to
> do a self join like
> select t1.objectid from mytable t1, mytable t2 where
> t1.objectid=t2.objectid and'555-1212' and t2.addr='Main
> Street'. There are many problems here
> 1) very slow when table is large
> 2) you need to do a self join for every attribute in the AND-ed
> expression.
> 3) If you want to sort by an attribute other than the search key you
> need to self join yet again.
> By contrast with approach 2 all these problems go away:
> select id from mytable where phone='555-1212' and addr='Main Street'
> order by some_field
> Thank you so much for your response
> Dre
Received on Fri Mar 01 2002 - 17:34:48 CST

Original text of this message