[Dbix-class] Subquery overhead with slice()

[Nickolay Platonov]

No, this woudnt work, coz ROWNUM is the number of the actual row in the
resultset, so ROWNUM is starting from the 1, even for 9th row

On Wed, Aug 6, 2008 at 3:54 PM, Nickolay Platonov <nickolay8 at gmail.com>wrot=
e:

> You mean having r >=3D 9 and r<16?
>
>
> On Wed, Aug 6, 2008 at 3:51 PM, Oleg Pronin <syber.rus at gmail.com> wrote:
>
>> And without subquery at all if you make use of 'having r >=3D 9'
>>
>> 2008/8/6 Nickolay Platonov <nickolay8 at gmail.com>
>>
>>> Hi all
>>>
>>> Not sure I'm posting to appropriate maillist, may be someone will advise
>>> where to forward this.
>>>
>>> I'd like to draw attention on 1 useless subquery, which is produces by
>>> slice() for DBIx::Class::Storage::DBI::Oracle::Generic
>>>
>>> for example $rs->slice(8,15) will produce following query, containing 2
>>> subqueries:
>>>
>>> SELECT * FROM
>>> (
>>>     SELECT A.*, ROWNUM r FROM
>>>     (
>>>         SELECT me.id AS col1, me.name AS col2, me.description AS col3,
>>> current_photo.path AS col4 FROM place me LEFT JOIN photo current_photo =
ON (
>>> current_photo.id =3D me.current_photo_id )
>>>     ) A
>>>     WHERE ROWNUM < 16
>>> ) B
>>> WHERE r >=3D 9
>>>
>>>
>>> meanwhile exactly the same result can be achieved with only 1 subquery:
>>>
>>> SELECT * FROM
>>> (
>>>         SELECT
>>>           me.id AS col1, me.name AS col2, me.description AS col3,
>>> current_photo.path AS col4, ROWNUM AS r
>>>         FROM
>>>           place me
>>>         LEFT JOIN photo current_photo
>>>           ON ( current_photo.id =3D me.current_photo_id )
>>>                 WHERE
>>>           ROWNUM < 16
>>> ) B
>>> WHERE r >=3D 9
>>>
>>>
>>> _______________________________________________
>>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>>> IRC: irc.perl.org#dbix-class
>>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>>> Searchable Archive:
>>> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>>>
>>
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive:
>> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080806/f53=
c67ec/attachment-0001.htm