SearchBuilder and sorting in Oracle

Hi,

I’ve been struggling with CustomFields in RT not being shown in the desired order, and I have now pinpointed where the problem apparently is.

As I see it, it is Oracle’s query optimizer that prevents the DistinctQuery-SQL to return the rows in correct order. I have tweaked the generated SQL in DistinctQuery (Oracle.pm) and it now seems to give the correct result (at least my CustomFields are now shown in the right order :)). You may argue, that there is an overhead in my solution (two order bys), but I do not have enough in-depth knowledge of how the whole SearchBuilder works.

Anyway - you may adopt the solution or simply use it as inspiration :slight_smile:

diff DBIx-SearchBuilder-1.54/SearchBuilder/Handle/Oracle.pm Oracle.pm
275c275,283
< $$statementref = “SELECT main.* FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)”;

    $$statementref = qq [

SELECT main.* FROM
( SELECT rt_internal_table.id, rownum rt_internal_sort_order FROM
( SELECT main.id FROM $$statementref $group $order ) rt_internal_table
) distinctquery, $table main
WHERE (main.id = distinctquery.id) order by distinctquery.rt_internal_sort_order
];

Configuration used: Perl v5.8.8, Oracle 10.2.0.1.0, RT3.8.1 and SearchBuilder 1.54

Best regards

Steen Olesen
Schilling A/S
Baldersbækvej 24-26
DK-2635 Ishøj
Tel: +45 70 27 99 00
Fax: +45 70 27 99 10
Mailto:so@schilling.dkmailto:so@schilling.dk
http://www.schilling.dkhttp://www.schilling.dk/

Steen, I’ve bounced this into the RT bugs queue for evaluation. Thanks!On Thu 12.Feb’09 at 11:37:02 +0100, Steen Olesen wrote:

Hi,

I’ve been struggling with CustomFields in RT not being shown in the
desired order, and I have now pinpointed where the problem apparently is.

As I see it, it is Oracle’s query optimizer that prevents the
DistinctQuery-SQL to return the rows in correct order. I have tweaked the
generated SQL in DistinctQuery (Oracle.pm) and it now seems to give the
correct result (at least my CustomFields are now shown in the right order
J). You may argue, that there is an overhead in my solution (two order
bys), but I do not have enough in-depth knowledge of how the whole
SearchBuilder works.

Anyway - you may adopt the solution or simply use it as inspiration J

diff DBIx-SearchBuilder-1.54/SearchBuilder/Handle/Oracle.pm Oracle.pm

275c275,283

< $$statementref = “SELECT main.* FROM ( SELECT main.id FROM
$$statementref $group $order ) distinctquery, $table main WHERE (main.id =
distinctquery.id)”;


$$statementref = qq [

SELECT main.* FROM

( SELECT rt_internal_table.id, rownum rt_internal_sort_order FROM

( SELECT main.id FROM $$statementref $group $order ) rt_internal_table

) distinctquery, $table main

WHERE (main.id = distinctquery.id) order by
distinctquery.rt_internal_sort_order

];

Configuration used: Perl v5.8.8, Oracle 10.2.0.1.0, RT3.8.1 and
SearchBuilder 1.54

Best regards

Steen Olesen
Schilling A/S
Baldersbækvej 24-26
DK-2635 Ishøj
Tel: +45 70 27 99 00
Fax: +45 70 27 99 10
[1]Mailto:so@schilling.dk
[2]http://www.schilling.dk

References

Visible links

  1. mailto:so@schilling.dk
  2. http://www.schilling.dk/

List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Steen, I’ve bounced this into the RT bugs queue for evaluation. Thanks!

There was some discussion about this last year:

http://www.gossamer-threads.com/lists/rt/devel/75666

Steve

Stephen Turner
Senior Programmer/Analyst - SAIS
MIT IS&T

Hi Steve,

I did look for solutions (and discussions on the subject) before trying to solve this.

It is quite obvious, that it is the Oracle query optimizer that is playing tricks.
You can run the standard DistinctQuery-SQL with:

Alter session set optimizer_mode=choose;

  • Get one result
    Alter session set optimizer_mode=all_rows;
  • Get another result
    Alter session set optimizer_mode=rule; // I know - this is not supported :slight_smile:
  • Get a third result.

/SteenFra: rt-devel-bounces@lists.bestpractical.com [rt-devel-bounces@lists.bestpractical.com] På vegne af Stephen Turner [sturner@MIT.EDU]
Sendt: 12. februar 2009 18:58
Til: Jesse Vincent; Steen Olesen
Cc: rt-devel@lists.bestpractical.com
Emne: Re: [Rt-devel] SearchBuilder and sorting in Oracle

Steen, I’ve bounced this into the RT bugs queue for evaluation. Thanks!

There was some discussion about this last year:

http://www.gossamer-threads.com/lists/rt/devel/75666

Steve

Stephen Turner
Senior Programmer/Analyst - SAIS
MIT IS&T
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Hi Steve,

I did look for solutions (and discussions on the subject) before trying
to solve this.

/Steen

Hi Steen,

Sure - I just wanted to make sure that BP was aware of the previous
discussion -

Thanks,
Steve

Stephen Turner
Senior Programmer/Analyst - SAIS
MIT IS&T