[rt-devel] DBIx::SearchBuilder ApplyLimits for Oracle
Brook Schofield
B.Schofield at mailbox.gu.edu.au
Wed Mar 12 22:07:26 EST 2003
I take it all back...
while the between is easier to read (by me) Pavel's query is more efficient on the Oracle database. From some rudimentary testing performed by our DBAs (below). In summary...
%diff -u brook.plan pavel.plan
--- brook.plan Thu Mar 13 12:42:05 2003
+++ pavel.plan Thu Mar 13 12:42:55 2003
@@ -1,13 +1,13 @@
Statistics
----------------------------------------------------------
0 recursive calls
- 2 db block gets
+ 0 db block gets
170 consistent gets
- 196 physical reads
+ 2 physical reads
0 redo size
- 366 bytes sent via SQL*Net to client
+ 361 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
+ 1 sorts (memory)
- 1 sorts (disk)
+ 0 sorts (disk)
- 11 rows processed
+ 10 rows processed
Pavel's query performs less reads and on disk sorts due to the limiting of the maximum rows returned in the second subselect. While the database reports the cost as the same - there is more data transferred to compute the between set. These tests were performed an a database with 100,000 rows and requesting the 4000 - 6000 set of records. The BETWEEN based query (which I now disowning) has constant cost based on the size of the table, while the early limit query (which I want to adopt) is more efficient - especially when you are looking at the start of a set of records.
Brook 0
Pavel 1
I'll keep trying for RT patch glory! Maybe my wording and slightly modified Pavel diff will get the nod! ;-) Pavel... will you give that to me?
-Brook
Explain plans for test queries.
TABLE: SYSTEM.Y
TABLESPACE: USERS
COLUMN NAME U COLUMN SPECIFICATION
------------------------------ - -----------------------------
C 1 NUMBER
V VARCHAR2(1)
Table contains 100,000 rows - table analyzed (compute).
Brook's query:
STATE_ID
----------------------------------------
21937
Id Par Pos Ins Plan
---- ---- ---- ---- -----------------------------------------------------------------------------
0 228 SELECT STATEMENT (choose) Cost (228,100000,2800000)
1 0 1 1 VIEW SYSTEM Cost (228,100000,2800000)
2 1 1 COUNT
3 2 1 2 VIEW SYSTEM Cost (228,100000,1500000)
4 3 1 SORT (order by) Cost (228,100000,500000)
5 4 1 3 TABLE ACCESS (analyzed) SYSTEM Y (full) Cost (18,100000,500000)
Pavel's query:
STATE_ID
----------------------------------------
21937
Id Par Pos Ins Plan
---- ---- ---- ---- -----------------------------------------------------------------------------
0 228 SELECT STATEMENT (choose) Cost (228,6000,168000)
1 0 1 1 VIEW SYSTEM Cost (228,6000,168000)
2 1 1 COUNT (stopkey)
3 2 1 2 VIEW SYSTEM Cost (228,100000,1500000)
4 3 1 SORT (order by stopkey) Cost (228,100000,500000)
5 4 1 3 TABLE ACCESS (analyzed) SYSTEM Y (full) Cost (18,100000,500000)
> diff -cr DBIx-SearchBuilder-0.80.orig/ DBIx-SearchBuilder-0.80
Common subdirectories: DBIx-SearchBuilder-0.80.orig//SearchBuilder and DBIx-SearchBuilder-0.80/SearchBuilder
diff -cr DBIx-SearchBuilder-0.80.orig//SearchBuilder.pm DBIx-SearchBuilder-0.80/SearchBuilder.pm
*** DBIx-SearchBuilder-0.80.orig//SearchBuilder.pm Sat Mar 8 18:42:25 2003
--- DBIx-SearchBuilder-0.80/SearchBuilder.pm Wed Mar 12 16:23:10 2003
***************
*** 204,212 ****
if ( $self->_isLimited > 0 );
- $self->_ApplyLimits(\$QueryString) unless ($all);
-
-
print STDERR "DBIx::SearchBuilder->DoSearch Query: $QueryString\n"
if ( $self->DEBUG );
--- 204,209 ----
Common subdirectories: DBIx-SearchBuilder-0.80.orig//SearchBuilder/Handle and DBIx-SearchBuilder-0.80/SearchBuilder/Handle
Common subdirectories: DBIx-SearchBuilder-0.80.orig//SearchBuilder/Record and DBIx-SearchBuilder-0.80/SearchBuilder/Record
diff -cr DBIx-SearchBuilder-0.80.orig//SearchBuilder/Handle/Oracle.pm DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm
*** DBIx-SearchBuilder-0.80.orig//SearchBuilder/Handle/Oracle.pm Sat Mar 8 18:42:25 2003
--- DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm Thu Mar 13 12:56:18 2003
***************
*** 122,126 ****
--- 122,176 ----
return( $self->{'id'}); #Add Succeded. return the id
}
+ # }}}
+ # {{{ sub ApplyLimits
+
+ =head2 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
+
+ takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE starting with FIRST_ROW;
+
+
+ =cut
+
+ sub ApplyLimits {
+ my $self = shift;
+ my $statementref = shift;
+ my $per_page = shift;
+ my $first = shift;
+
+ # Transform an SQL query from:
+ #
+ # SELECT DISTINCT main.*
+ # FROM Tickets main
+ # WHERE ((main.EffectiveId = main.id))
+ # AND ((main.Type = 'ticket'))
+ # AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
+ # AND ( (main.Queue = '1') ) )
+ # ORDER BY main.id ASC
+ #
+ # to:
+ #
+ # SELECT * FROM (
+ # SELECT oraquery.*,rownum rn FROM (
+ # SELECT DISTINCT main.*
+ # FROM Tickets main
+ # WHERE ((main.EffectiveId = main.id))
+ # AND ((main.Type = 'ticket'))
+ # AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
+ # AND ( (main.Queue = '1') ) )
+ # ORDER BY main.id ASC
+ # ) oraquery WHERE rownum <= 50
+ # ) WHERE rn => 1
+ #
+
+ if ($per_page) {
+ # Oracle orders from 1 not zero
+ $first++;
+ # Make current query a sub select
+ $$statementref = "SELECT * FROM ( SELECT oraquery.*,rownum rn FROM ( $$statementref ) oraquery WHERE rownum <= " . ($first + $per_page - 1) . " ) WHERE rn => " . $first;
+ }
+ }
+
+ # }}}
=========================================================================
= _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/ _/ Brook Schofield =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ B.Schofield at griffith.edu.au =
= _/_/ _/_/_/ _/ _/ _/ _/ _/_/ Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ Directory Services Integration =
= _/_/_/ _/ _/ _/_/_/ _/_/_/ _/ _/ Griffith University QLD 4111 =
=========================================================================
More information about the Rt-devel
mailing list