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_at_ID-152540.news.uni-berlin.de>
>
> something
>
>
>
> 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:-)
>
>
Date: Thu, 09 Oct 2003 16:53:50 -0700
Message-ID: <bm4sa8$bi4iq$1_at_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 Fri Oct 10 2003 - 01:53:50 CEST