[rt-devel] DBIx::SearchBuilder ApplyLimits for Oracle
BEHAL,PAVEL (HP-Czechia,ex1)
pavel.behal at hp.com
Thu Mar 13 09:13:46 EST 2003
Hi Brook, Hi Jesse,
I am living in another time zone, so you have to wait a little for my
answers. It is funny, that we have got two similar patches in the same time.
:-)
Let's look about it:
1) First I am not any DBA, so all my work with Oracle is based on some best
practices. This is the same with the limiting query using two sub-selects or
between. Brook, please look into this URL:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:127412348
064
This was my source, while preparing the patch. And there are mentioned all
the tests you have already done. I have chosen the "better" and proven way
with two sub-selects, also because the between clause may lead to some
misunderstanding.
2) Brook, the replacement regex, you provided within your first patch may be
bad. I think, the parts of DBIx:SearchBuilder API should not relay on proper
syntax of one query. I have noticed, that you have already corrected it. I'd
prefer the query untouched.
3) The alias of rownum column (you used rn, me LIMRNUM) should be hardly
predictable. We have to avoid any future name collision inside sub-query.
4) Brook, I think your latest modification of both patches (together with
latest => and >= replacement) is the best and can be safely accepted. Maybe
it just need only small beautification, regarding the column alias. And you
take the glory :-) There are some more issues to solve with Oracle.
5) I am unsure about one thing in Brook's patch. During paging, the latest
row from previous page is the first rown of next page. I think, this
intended and correct. Am I right?
6) Brook, you have suggested to remove the
"$self->_ApplyLimits(\$QueryString) unless ($all);" from _DoCount. Jesee,
is this safe also in MySQL and PostgreSQL? I have not tested it.
Regards,
Pavel
-----Original Message-----
From: Brook Schofield [mailto:B.Schofield at mailbox.gu.edu.au]
Sent: Thursday, March 13, 2003 4:07 AM
To: Jesse Vincent; BEHAL,PAVEL (HP-Czechia,ex1)
Cc: rt-devel at fsck.com
Subject: Re: [rt-devel] DBIx::SearchBuilder ApplyLimits for Oracle
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