[Dbix-class] Subquery overhead with slice()

[Nickolay Platonov]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080806/b6f=
5ff3e/attachment.htm