From: Peter Rabbitson <ribasushi@cpan.org>
Date: Sun, 12 Sep 2010 12:14:43 +0000 (+0200)
Subject: Optimize RowNum limit dialect as per suggestion in RT#61277
X-Git-Tag: v0.08124~80
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FDBIx-Class.git;a=commitdiff_plain;h=d9672fb94b70dac86a6d4fed7ad46a48c5ee4527

Optimize RowNum limit dialect as per suggestion in RT#61277
---

diff --git a/Changes b/Changes
index 00603f2..fc93f08 100644
--- a/Changes
+++ b/Changes
@@ -1,6 +1,9 @@
 Revision history for DBIx::Class
 
     * New Features / Changes
+        - Use DBIx::Class::Storage::Debug::PrettyPrint when the
+          environment variable DBIC_TRACE_PROFILE is set, see
+          DBIx::Class::Storage for more information
         - Implemented add_unique_constraints() which delegates to
           add_unique_constraint() as appropriate
         - add_unique_constraint() now poparly throws if called with
@@ -9,9 +12,7 @@ Revision history for DBIx::Class
           most of the heavy lifting for a while anyway
         - FilterColumn now passes data through when transformations
           are not specified rather than throwing an exception.
-        - Use DBIx::Class::Storage::Debug::PrettyPrint when the
-          environment variable DBIC_TRACE_PROFILE is set, see
-          DBIx::Class::Storage for more information
+        - Optimized RowNum based Oracle limit-dialect (RT#61277)
 
     * Fixes
         - Fixed rels ending with me breaking subqueried limit realiasing
diff --git a/lib/DBIx/Class/SQLMaker/LimitDialects.pm b/lib/DBIx/Class/SQLMaker/LimitDialects.pm
index e189e2c..723001d 100644
--- a/lib/DBIx/Class/SQLMaker/LimitDialects.pm
+++ b/lib/DBIx/Class/SQLMaker/LimitDialects.pm
@@ -266,8 +266,8 @@ sub _FirstSkip {
  SELECT * FROM (
   SELECT *, ROWNUM rownum__index FROM (
    SELECT ...
-  )
- ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)
+  ) WHERE ROWNUM <= ($limit+$offset)
+ ) WHERE rownum__index >= ($offset+1)
 
 Supported by B<Oracle>.
 
@@ -285,15 +285,27 @@ sub _RowNum {
   my $idx_name = $self->_quote ('rownum__index');
   my $order_group_having = $self->_parse_rs_attrs($rs_attrs);
 
-  $sql = sprintf (<<EOS, $offset + 1, $offset + $rows, );
+  if ($offset) {
+
+    $sql = sprintf (<<EOS, $offset + $rows, $offset + 1 );
 
 SELECT $outsel FROM (
   SELECT $outsel, ROWNUM $idx_name FROM (
     SELECT $insel ${sql}${order_group_having}
-  ) $qalias
-) $qalias WHERE $idx_name BETWEEN %u AND %u
+  ) $qalias WHERE ROWNUM <= %u
+) $qalias WHERE $idx_name >= %u
 
 EOS
+  }
+  else {
+    $sql = sprintf (<<EOS, $rows );
+
+  SELECT $outsel FROM (
+    SELECT $insel ${sql}${order_group_having}
+  ) $qalias WHERE ROWNUM <= %u
+
+EOS
+  }
 
   $sql =~ s/\s*\n\s*/ /g;   # easier to read in the debugger
   return $sql;
diff --git a/t/73oracle.t b/t/73oracle.t
index b8b0270..2203e26 100644
--- a/t/73oracle.t
+++ b/t/73oracle.t
@@ -517,22 +517,20 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
 
       is_same_sql_bind (
         $rs->as_query,
-        '( 
+        '(
             SELECT artistid, name, rank, charfield, parentid FROM (
-                  SELECT artistid, name, rank, charfield, parentid, ROWNUM rownum__index FROM (
-                      SELECT 
-                          me.artistid,
-                          me.name,
-                          me.rank,
-                          me.charfield,
-                          me.parentid 
-                      FROM artist me 
-                      START WITH name = ? 
-                      CONNECT BY parentid = PRIOR artistid
-                      ORDER BY name ASC 
-                  ) me 
+              SELECT
+                  me.artistid,
+                  me.name,
+                  me.rank,
+                  me.charfield,
+                  me.parentid
+                FROM artist me
+              START WITH name = ?
+              CONNECT BY parentid = PRIOR artistid
+              ORDER BY name ASC 
             ) me
-            WHERE rownum__index BETWEEN 1 AND 2
+            WHERE ROWNUM <= 2
         )',
         [ [ name => 'root' ] ],
       );
@@ -551,19 +549,18 @@ if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
       # 
       is_same_sql_bind (
         $rs->count_rs->as_query,
-        '( 
-            SELECT COUNT( * ) FROM (
-                SELECT artistid FROM (
-                    SELECT artistid, ROWNUM rownum__index FROM (
-                        SELECT 
-                            me.artistid
-                        FROM artist me 
-                        START WITH name = ? 
-                        CONNECT BY parentid = PRIOR artistid
-                    ) me
-                ) me 
-                WHERE rownum__index BETWEEN 1 AND 2
-            ) me
+        '(
+          SELECT COUNT( * ) FROM (
+            SELECT artistid
+              FROM (
+                SELECT
+                  me.artistid
+                FROM artist me 
+                START WITH name = ? 
+                CONNECT BY parentid = PRIOR artistid
+              ) me
+            WHERE ROWNUM <= 2
+          ) me
         )',
         [ [ name => 'root' ] ],
       );
diff --git a/t/sqlmaker/limit_dialects/rownum.t b/t/sqlmaker/limit_dialects/rownum.t
index 34619fc..909367b 100644
--- a/t/sqlmaker/limit_dialects/rownum.t
+++ b/t/sqlmaker/limit_dialects/rownum.t
@@ -18,22 +18,24 @@ is_same_sql_bind (
       { 'bar.id' => 'bar.id' },
       { bleh => \ 'TO_CHAR (foo.womble, "blah")' },
     ]})->as_query,
-  '(SELECT id, bar__id, bleh
+  '(
+    SELECT id, bar__id, bleh
       FROM (
         SELECT id, bar__id, bleh, ROWNUM rownum__index
           FROM (
             SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
               FROM cd me
           ) me
+        WHERE ROWNUM <= 4
       ) me
-    WHERE rownum__index BETWEEN 4 AND 4
+    WHERE rownum__index >= 4
   )',
   [],
   'Rownum subsel aliasing works correctly'
 );
 
 is_same_sql_bind (
-  $rs->search ({}, { rows => 1, offset => 3,columns => [
+  $rs->search ({}, { rows => 2, offset => 3,columns => [
       { id => 'foo.id' },
       { 'ends_with_me.id' => 'ends_with_me.id' },
     ]})->as_query,
@@ -44,8 +46,9 @@ is_same_sql_bind (
             SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
               FROM cd me
           ) me
+        WHERE ROWNUM <= 5
       ) me
-    WHERE rownum__index BETWEEN 4 AND 4
+    WHERE rownum__index >= 4
   )',
   [],
   'Rownum subsel aliasing works correctly'