Re: Dreaming About Redesigning SQL

"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:-) Received on Fri Oct 10 2003 - 01:40:18 CEST