Re: Dreaming About Redesigning SQL

From: Costin Cozianu <c_cozianu_at_hotmail.com>
Date: Thu, 09 Oct 2003 16:53:50 -0700
Message-ID: <bm4sa8$bi4iq$1@ID-152540.news.uni-berlin.de>

Mikito Harakiri wrote:

> "Paul G. Brown" <paul_geoffrey_brown_at_yahoo.com> wrote in message
> news:57da7b56.0310082211.79dca238_at_posting.google.com...
>

>>   Well, the point of departure here was a discussion about modeling

>
> something
>

>>   at the logical level. Implementation is quite up to you. The simplest
>>   thing to do would be to take a bog-standard RDBMS approach and treat
>>   REF(Dept) as "this says to take the primary key of Dept and embed those
>>   columns invisibly within the Emp table and enforce the necessary RI
>>   constraint rules". At which point  a decision about how to process this
>>   query becomes a bog-standard query problem.

>
>
> After half-day investigation I must give some credit to object folks. They
> reduced the query to
>
> select e.name from Emp E
> where E.Deptref.name = 'shoe'
>
> and pointed me to scoping syntax which allows creating index like this
>
> create index deptref_ix ON emp (Deptref);
>
> Then, the hinted query
>
> select --+leading(d) use_nl(e) index(e deptref_ix)
> E.Name FROM Emp E, Dept D
> WHERE E.Deptref = REF(d) AND D.Name = 'shoe';
>
> did follow the access path that I wanted to. Of course, it still remains to
> see the more challenging task solved with the hints removed.
>
> It is optimizer that is always a problem:-)
>
>

Well, I'd be least concerned about the optimizer. If you're using Oracle , I'd lookup the documentation for the cute name

   IS_DANGLING Cool stuff.

Cheers,
Costin Received on Thu Oct 09 2003 - 18:53:50 CDT