Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!fu-berlin.de!uni-berlin.de!12.145.34.101!not-for-mail
From: Costin Cozianu <c_cozianu@hotmail.com>
Newsgroups: comp.databases,comp.databases.theory,comp.databases.object
Subject: Re: Dreaming About Redesigning SQL
Date: Thu, 09 Oct 2003 16:53:50 -0700
Lines: 50
Message-ID: <bm4sa8$bi4iq$1@ID-152540.news.uni-berlin.de>
References: <ba87a3cf.0310031052.77315052@posting.google.com> <blkq9n$d9puv$4@ID-125932.news.uni-berlin.de> <ba87a3cf.0310031759.42dce77c@posting.google.com> <3F7F8E1A.474@ix.netcom.com> <ba87a3cf.0310062207.1d09cff6@posting.google.com> <fPEgb.63802$IA4.3061832@phobos.telenet-ops.be> <3F833025.76A6@ix.netcom.com> <57da7b56.0310080006.47b550a9@posting.google.com> <bdf69bdf.0310080845.4d71a1f0@posting.google.com> <57da7b56.0310081407.71d1bc8e@posting.google.com> <Ze3hb.28$MU.214@news.oracle.com> <57da7b56.0310082211.79dca238@posting.google.com> <Ntmhb.25$ml5.142@news.oracle.com>
NNTP-Posting-Host: 12.145.34.101
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news.uni-berlin.de 1065743497 12128858 12.145.34.101 (16 [152540])
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624
X-Accept-Language: en-us, en
In-Reply-To: <Ntmhb.25$ml5.142@news.oracle.com>
Xref: newssvr20.news.prodigy.com comp.databases:48151 comp.databases.theory:21684 comp.databases.object:16503

Mikito Harakiri wrote:

> "Paul G. Brown" <paul_geoffrey_brown@yahoo.com> wrote in message
> news:57da7b56.0310082211.79dca238@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