From 53990486fa488d26ee95b6d452b0a76b08a446fa Mon Sep 17 00:00:00 2001 From: Eduardo Mourao Date: Fri, 15 Mar 2013 16:39:50 -0300 Subject: Revert issue #99 fixes There are two commits involving issue #99 which should be reverted. First of all, #99 is not an issue in Arel at all. Second, the fix provides pretty much destroys the purpose of pagination by cause full table scans. The original code (it seems I can't simply revert the commits) is 900 times slower than this one. ``` SELECT * FROM ( SELECT raw_sql_.*, rownum raw_rnum_ FROM (SELECT "LANCAMENTOS".* FROM "LANCAMENTOS" ) raw_sql_ ) WHERE raw_rnum_ between 1 and 30 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4636K| 2701M| 23442 (2)| 00:04:42 | |* 1 | VIEW | | 4636K| 2701M| 23442 (2)| 00:04:42 | | 2 | COUNT | | | | | | | 3 | TABLE ACCESS FULL| LANCAMENTOS | 4636K| 738M| 23442 (2)| 00:04:42 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RAW_RNUM_"<=30 AND "RAW_RNUM_">=1) Statistics ----------------------------------------------------------- 4 user calls 13 physical read total multi block requests 202588160 physical read total bytes 202588160 cell physical IO interconnect bytes 0 commit cleanout failures: block lost 0 IMU commits 0 IMU Flushes 0 IMU contention 0 IMU bind flushes 0 IMU mbu flush SELECT * FROM ( SELECT raw_sql_.*, rownum raw_rnum_ FROM (SELECT "LANCAMENTOS".* FROM "LANCAMENTOS" ) raw_sql_ WHERE rownum <= 30 ) WHERE raw_rnum_ >= 0 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 18330 | 2 (0)| 00:00:01 | |* 1 | VIEW | | 30 | 18330 | 2 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL| LANCAMENTOS | 30 | 5010 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RAW_RNUM_">=0) 2 - filter(ROWNUM<=30) Statistics ----------------------------------------------------------- 4 user calls 0 physical read total multi block requests 0 physical read total bytes 0 cell physical IO interconnect bytes 0 commit cleanout failures: block lost 0 IMU commits 0 IMU Flushes 0 IMU contention 0 IMU bind flushes 0 IMU mbu flush ``` --- lib/arel/visitors/oracle.rb | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'lib/arel/visitors/oracle.rb') diff --git a/lib/arel/visitors/oracle.rb b/lib/arel/visitors/oracle.rb index 6a58a3cff2..375f7dbfe9 100644 --- a/lib/arel/visitors/oracle.rb +++ b/lib/arel/visitors/oracle.rb @@ -25,8 +25,9 @@ module Arel SELECT * FROM ( SELECT raw_sql_.*, rownum raw_rnum_ FROM (#{sql}) raw_sql_ + WHERE rownum <= #{offset.expr.to_i + limit} ) - WHERE raw_rnum_ >= #{offset.expr.to_i + 1 } and rownum <= #{limit} + WHERE #{visit offset} eosql end -- cgit v1.2.3