On Wed, Aug 06, 2008 at 03:35:08PM +0400, Nickolay Platonov wrote:
> 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 = me.current_photo_id )
> ) A
> WHERE ROWNUM < 16
> ) B
> WHERE r >= 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 = me.current_photo_id )
> WHERE
> ROWNUM < 16
> ) B
> WHERE r >= 9
I don't think that would work if the innermost query has a GROUP BY
and HAVING clause though.
I'd love to see a patch that detects if it can optimise this and does
so though.
--
Matt S Trout Need help with your Catalyst or DBIx::Class project?
Technical Director http://www.shadowcat.co.uk/catalyst/
Shadowcat Systems Ltd. Want a managed development or deployment platform?
http://chainsawblues.vox.com/ http://www.shadowcat.co.uk/servers/