APEX Classic Report Performance enhancement

The Apex classic report is a lot of wonderful features but a simple enhancement can have reports running faster.   The problem comes in that classic report has a wild card search on selected columns.  This can slow down the report if the user wants everything

A simple example
   I have a classic report with 4 columns COL1 through COL4

The default sql generated is

select * from (

select rowid,

        col1,

       col2,

       col3,

       col4

  from table_x

) where

 instr(upper("COL1"),upper(nvl(:P25_REPORT_SEARCH,"COL1"))) > 0  or

instr(upper("COL2"),upper(nvl(:P25_REPORT_SEARCH,"COL2"))) > 0  or

instr(upper("COL3"),upper(nvl(:P25_REPORT_SEARCH,"COL3"))) > 0  or

instr(upper("COL4"),upper(nvl(:P25_REPORT_SEARCH,"COL4"))) > 0

)

The problem is this syntax requires Oracle to do string compares on every row for every column even if the user doesn't key anything in.  The SOLUTION: simply add one line

or length(trim(nvl(:p25_report_search,''))) < 1

Yep paste that line at the end of the SQL and your reports will run faster.  So the final SQL would look like

select * from (

select rowid,

        col1,

       col2,

       col3,

       col4

  from table_x

) where

 instr(upper("COL1"),upper(nvl(:P25_REPORT_SEARCH,"COL1"))) > 0  or

instr(upper("COL2"),upper(nvl(:P25_REPORT_SEARCH,"COL2"))) > 0  or

instr(upper("COL3"),upper(nvl(:P25_REPORT_SEARCH,"COL3"))) > 0  or

instr(upper("COL4"),upper(nvl(:P25_REPORT_SEARCH,"COL4"))) > 0

or length(trim(nvl(:p25_report_search,''))) < 1

)

So my 1st blog post... Go easy on me.

Let me know what you think :)

Hope to see everyone at my 2nd ODTUG KScope16 in Chicago!

Jerry