DBIx::SearchBuilder ApplyLimits for Oracle

Jesse,
attached is a patch against DBIx::SearchBuilder 0.80 which performs the following:

a) removes ApplyLimits from the _DoCount query because I can’t understand why:

            SELECT count(DISTINCT main.id) 
              FROM Tickets main   
             WHERE ((main.EffectiveId = main.id)) 
                    AND ((main.Type = 'ticket')) 
                    AND ( (  ( (main.Status = 'new')OR(main.Status = 'open') ) 
                            AND ( (main.Queue = '1') 
                        ) )
             LIMIT 50

    the LIMIT 50 is useful in a count(*) query as its only purpose is to limit the number of rows returned, which will always be 1. It doesn't affect the result set that count() operates on. Or does it?

b) changes an oracle query into a sub select so that ApplyLimits can be easily applied

I haven’t had an opportunity to test in my Oracle environment as I haven’t got Oracle/Perl talking yet… but thought that it may be more useful out in the open. What else needs to be done to have Oracle “supported” as an RT database.

-Brook

= /// /// /// /// _/ _/ Brook Schofield =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / B.Schofield@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

diff -rc 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 -rc 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 Wed Mar 12 16:39:48 2003
*** 122,126 ****
— 122,175 ----
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 oraquery.* FROM (
    
  • #         SELECT DISTINCT main.*,rownum rn 
    
  • #           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 oraquery.rn BETWEEN 1 AND 50
    
  • #
    
  • if ($per_page) {
    
  •     # Oracle orders from 1 not zero
    
  •     $first++; 
    
  •     $$statementref =~ s/main\.\*/main.*,rownum rn/;
    
  •     # Make current query a sub select
    
  •     $$statementref = "SELECT oraquery.* FROM ($$statementref) oraquery WHERE oraquery.rn BETWEEN $first AND ";
    
  •     $$statementref .= $per_page + $first - 1;
    
  • }
    
  • }

  • }}}

Brook, Pavel,

In the past 24 hours, you’ve both contributed patches to
DBIx::SearchBuilder to help better support Oracle. I’m including both
patches here. I’d love a bit of discussion on the relative merits of the
two patches.

Thanks,
Jesse

Brook’s patch:

diff -rc 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 -rc 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 Wed Mar 12 16:39:48 2003


*** 122,126 ****
— 122,175 ----
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 oraquery.* FROM (
    
  • #         SELECT DISTINCT main.*,rownum rn 
    
  • #           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 oraquery.rn BETWEEN 1 AND 50
    
  • #
    
  • if ($per_page) {
    
  •     # Oracle orders from 1 not zero
    
  •     $first++; 
    
  •     $$statementref =~ s/main\.\*/main.*,rownum rn/;
    
  •     # Make current query a sub select
    
  •     $$statementref = "SELECT oraquery.* FROM ($$statementref) oraquery WHERE oraquery.rn BETWEEN $first AND ";
    
  •     $$statementref .= $per_page + $first - 1;
    
  • }
    
  • }

  • }}}

Pavel’s patch

diff -ru DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm DBIx-SearchBuilder-0.80-pb/SearchBuilder/Handle/Oracle.pm
— DBIx-SearchBuilder-0.80/SearchBuilder/Handle/Oracle.pm 2003-03-08 09:42:26.000000000 +0100
+++ DBIx-SearchBuilder-0.80-pb/SearchBuilder/Handle/Oracle.pm 2003-03-11 15:55:22.000000000 +0100
@@ -122,5 +122,36 @@
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;
  • my $limit_clause_pre = ‘’;
  • my $limit_clause_pos = ‘’;
  • if ( $per_page) {
  •    if ( $first >  0 ) {
    
  •            $first++;
    
  •    }       
    
  •    $limit_clause_pre = " SELECT * FROM ( SELECT A.*, ROWNUM LIMRNUM FROM ( ";
    
  •    $limit_clause_pos = " ) A WHERE ROWNUM <= " . ($first + $per_page) . " ) WHERE LIMRNUM > " . $first ;
    
  • }
  • $$statementref = $limit_clause_pre . $$statementref . $limit_clause_pos;
    +}
    +# }}}

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

At 01:48 PM 12/03/2003 -0500, Jesse Vincent wrote:

Brook, Pavel,

In the past 24 hours, you’ve both contributed patches to
DBIx::SearchBuilder to help better support Oracle. I’m including both
patches here. I’d love a bit of discussion on the relative merits of the
two patches.

a) the substitution that I perform in my ApplyLimits is potentially
bad as this could effect the query. I have seen many queries from DBIx
search builder and they all appear to be in the format of SELECT DISTINCT
main.* so that addition of ‘rownum rn’ to this shouldn’t cause a problem.
But if the query is any different then that could induce problems. I have
since taken Pavel’s two sub selects approach to insulate the original query
from damage/change etc.

b) if the _DoCount function uses ‘group by’ then that could be a
potential reason for applying limits. _DoCount doesn’t do this yet - but
the new “dual” subselect patch will allow for _DoCount to have a limit
applied to it if there are any ‘group by’ counts performed.

c) I think that my ‘BETWEEN’ syntax is easier to read for longer term
maintenance :wink:

d) there could be an efficiency issue with the dual subselects. I’ll
get a DBAs to do an explain plan to see what issue there is, if any. But
for more robust code it is the better option.

-Brook

diff -cr DBIx-SearchBuilder-0.80.orig DBIx-SearchBuilder-0.80 | more
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
11:17:15 2003
*** 122,126 ****
— 122,177 ----
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 rn BETWEEN 1 AND 50
    
  • #
    
  • 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 rn BETWEEN $first AND ";

  •     $$statementref .= $per_page + $first - 1;
    
  • }
    
  • }
  • }}}

= /// /// /// /// _/ _/ Brook Schofield =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / B.Schofield@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

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! :wink: 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@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

  • 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;
  • }
    
  • }
  • }}}

That should read:

WHERE rn >= " . $first;
^^
rather than

WHERE rn => " . $first;
^^

-Brook

= /// /// /// /// _/ _/ Brook Schofield =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / B.Schofield@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

Brook: Thanks very much for all the detail. I probably won’t touch this
until next week, after I get RT 3.0.0 taken care of. That doesn’t mean
I’m not incredibly excited to finally be getting closer to proper oracle
support.

Jesse

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

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.
:slight_smile:
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 :slight_smile: 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,

PavelFrom: Brook Schofield [mailto:B.Schofield@mailbox.gu.edu.au]
Sent: Thursday, March 13, 2003 4:07 AM
To: Jesse Vincent; BEHAL,PAVEL (HP-Czechia,ex1)
Cc: rt-devel@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! :wink: 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@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

At 03:13 PM 13/03/2003 +0100, BEHAL,PAVEL (HP-Czechia,ex1) wrote:

There are some more issues to solve with Oracle.

What are the remaining issues with Oracle?

I’m going to finish running up an RT dev environment on my Mac tonight.
Hopefully I’ll have a good weekend of RT/Oracle hacking ahead of me. My
institution runs on Oracle so RT isn’t an option for a wider audience
without full Oracle support.

What other Oracle tips/problems can people suggest?

  1. 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?

This is what RT currently does under MySQL so I am guessing that it should
be the same behaviour under Oracle. So for a search result of 10 the pages
displayed will be: 1 - 10, 10 - 19, 19 - 28. Which is also the numeric
count at the bottom of the Search/Listing.html page.

  1. 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.

count() will only return ONLY ONE ROW - ever. Unless that count() query
also has a group by clause. Since SearchBuilder doesn’t use group by in its
_DoCount then the removal of ApplyLimits for every database is a good thing.

-Brook

= /// /// /// /// _/ _/ Brook Schofield =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / B.Schofield@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =

Hello Brook,

I have not installed the rt3 on Oracle, so my experience has come from rt2.
But because the queries are generated from DBIx:SearchBuilder, the problems
are version independent. Let’s look:

  • The rt2 schema for Oracle contains CLOB fields, and you can not make a
    SELECT DISTINCT * query on these tables. This leads to an Oracle error and
    it is an issue with all versions of Oracle DB. One ugly hack was to replace
    the CLOB with VARCHAR2(4000), but this has limited functionality very much.
    Or you should enumerate the fields and ignore the LOB’s, when you are
    constructing the query. My solution, I am thinking about, is to dynamically
    enumerate the fields in table and replace the * with the correct ones,
    before the query executes.
    Small hint, how to enumerate the fileds. It is from DBD:Oracle included
    examples, $dbh is handle from DBI->connect :
    — cut ----
    $sth = $dbh->prepare( “SELECT * FROM affectedtable WHERE 1 = 2”);
    @name = @{$sth->{NAME}};
    @length = @{$sth->{PRECISION}};
    @type = @{$sth->{TYPE}};

foreach $i ( 0 … $#name )
{
print $name[$i] . " " . $length[$i]. " " . $type[$i] . “\n”;
}
— cut —

  • The second problem is with ANSI syntax of LEFT JOIN. It is supported on
    MySQL and PostgreSQL, but not on older versions of Oracle. I hope, that
    Oracle 9.x.x will be fine. But I have no tested it yet.
  • There may be other problems, like requireig SID in connection string.
    Which should be configurable.

Points 5 and 6: Thank you for clarification Brook. Now I understand, you ar
right.

PavelFrom: Brook Schofield [mailto:B.Schofield@mailbox.gu.edu.au]
Sent: Friday, March 14, 2003 1:25 AM
To: BEHAL,PAVEL (HP-Czechia,ex1); Jesse Vincent
Cc: rt-devel@fsck.com
Subject: RE: [rt-devel] DBIx::SearchBuilder ApplyLimits for Oracle

At 03:13 PM 13/03/2003 +0100, BEHAL,PAVEL (HP-Czechia,ex1) wrote:

There are some more issues to solve with Oracle.

What are the remaining issues with Oracle?

I’m going to finish running up an RT dev environment on my Mac tonight.
Hopefully I’ll have a good weekend of RT/Oracle hacking ahead of me. My
institution runs on Oracle so RT isn’t an option for a wider audience
without full Oracle support.

What other Oracle tips/problems can people suggest?

  1. 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?

This is what RT currently does under MySQL so I am guessing that it should
be the same behaviour under Oracle. So for a search result of 10 the pages
displayed will be: 1 - 10, 10 - 19, 19 - 28. Which is also the numeric
count at the bottom of the Search/Listing.html page.

  1. 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.

count() will only return ONLY ONE ROW - ever. Unless that count() query
also has a group by clause. Since SearchBuilder doesn’t use group by in its
_DoCount then the removal of ApplyLimits for every database is a good thing.

-Brook

= /// /// /// /// _/ _/ Brook Schofield =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / B.Schofield@griffith.edu.au =
= // //
/ _/ _/ _/ _/ // Ph: +61 7 387 53779 - WCN 0.28 =
= _/ _/ _/ _/ _/ _/ _/ _/ _/ / Directory Services Integration =
= //
/ / / /// /// _/ _/ Griffith University QLD 4111 =