Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!rip!sjc70.webusenet.com!sjc72.webusenet.com!news.webusenet.com!news.randori.com!newsfeeder1.randori.com!news.sanjose1.Level3.net!Level3.net!news.oracle.com!not-for-mail
From: "Mikito Harakiri" <mikharakiri@iahu.com>
Newsgroups: comp.databases.theory
References: <ba87a3cf.0310031052.77315052@posting.google.com> <3567017.1068124091@dbforums.com> <1b0b566c.0311070332.54a041e@posting.google.com> <64ea97cf.0311090213.38942cc@posting.google.com> <bdf69bdf.0311091120.14065bc5@posting.google.com> <fsGdnRimJ8RxWDOiRVn-gQ@golden.net> <bdf69bdf.0311092059.7fa5b13f@posting.google.com> <sZqdnURpMcikvzKi4p2dnA@golden.net> <bQQrb.17$24.25@news.oracle.com> <3FB0A52C.2050105@atbusiness.com>
Subject: Re: Is relational theory irrelevant? (was Re: Dreaming About Redesigning SQL)
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <5O9sb.21$Up4.73@news.oracle.com>
Date: Tue, 11 Nov 2003 10:12:48 -0800
NNTP-Posting-Host: 130.35.179.109
X-Trace: news.oracle.com 1068574977 130.35.179.109 (Tue, 11 Nov 2003 10:22:57 PST)
NNTP-Posting-Date: Tue, 11 Nov 2003 10:22:57 PST
Xref: newssvr20.news.prodigy.com comp.databases.theory:22643


"Lauri Pietarinen" <lauri.pietarinen@atbusiness.com> wrote in message
news:3FB0A52C.2050105@atbusiness.com...
> Mikito Harakiri wrote:
> >select EX.EMP#
> >from EMP EX
> >where (select count(1)
> >        from EMP EY
> >        where EY.HEIGHT < EX.HEIGHT) < 5
> >
> >
> >
> Wow! That is BEAUTIFUL!  And I bet it will be easy to teach an
> optimiser how to handle it best ;-)

I doubt your exclamation is unmistaken here, because all I did was copying
and pasting from the first Date's "Quota Queries" article. Date also quotes
equivalent self-join query, which is admittedly less intuitive. Query
unnesting should be able to transform one form into another (the question
might be if any product on the market can do that). Then, it is still a
selfjoin, so that it have to be transformed into single table scan with
stopcount.

> BTW, what if you had to order on more than one column (as is often
> the case)?

select EX.EMP#
from EMP EX
where (select count(1)
        from EMP EY
        where EY.HEIGHT < EX.HEIGHT or EY.HEIGHT = EX.HEIGHT  and EY.SAL <
EX.SAL  ) < 5

or something more physical like this

select EX.EMP#
from EMP EX
where (select count(1)
        from EMP EY
        where padding(EY.HEIGHT)||padding(EY.SAL)  <
padding(EX.HEIGHT)||padding(EX.SAL) ) < 5