You Asked

Hello Tom,

First of all, thank for your time and help.
I have a procedure that receives more or less 26 parameters (most of them optional) and this dynamic query.
This query takes 1 min 36 with 99999 rows. Do you think a better way to do it using only sql statement not a dynamic one? I tried it but I found a problem with the order by clause because the parameter p_order_by could be 'col1 ASC, col2 DESC, ...'

v_select_stmt := 'SELECT * FROM ( ';
    v_select_stmt := v_select_stmt || 'SELECT rownum as rnum, y.* FROM( ';
    v_select_stmt := v_select_stmt || 'SELECT /*+ FIRST_ROWS(' || TO_NUMBER(p_maximumrows) || ') */ COUNT(1) OVER() as totalRows, ';
    v_select_stmt := v_select_stmt || 'x.col1, x.col2, x.col2, x.col4, x.col5, x.col6, x.col7, x.col8, x.col9, x.col10 ';
    v_select_stmt := v_select_stmt || 'FROM vView ';
    v_select_stmt := v_select_stmt || 'WHERE id = ' || p_lcs_event_id || ' ';
    v_select_stmt := v_select_stmt || 'AND (' || p_backup || ' = 1 OR x.event_id = 2) ';
    IF p_iresolved = 0 THEN
      v_select_stmt := v_select_stmt || 'AND (x.time_close IS NULL) ';
    END IF;
    
    IF p_region_id != 0 THEN
      v_select_stmt := v_select_stmt || ' AND x.rid =' || p_rid || ' ';
    END IF;
    IF p_region_name IS NOT NULL THEN
      v_select_stmt := v_select_stmt || ' AND LOWER(x.rname) LIKE ''%' || LOWER (p_rname) || '%' || '''';
    END IF;
    IF p_acctype IS NOT NULL THEN
      v_select_stmt := v_select_stmt || ' AND LOWER(x.acctype) LIKE ''%' || LOWER p_acctype || '%' || '''';
    END IF;
    IF p_mname IS NOT NULL THEN
      v_select_stmt := v_select_stmt || ' AND LOWER(x.mname) LIKE ''%' || LOWER p_mname || '%' || '''';
    END IF;
    IF p_county IS NOT NULL THEN
      v_select_stmt := v_select_stmt || ' AND LOWER(x.county) LIKE ''%' || LOWER (p_county) || '%' || '''';
    END IF;
    IF p_aname IS NOT NULL THEN
      v_select_stmt := v_select_stmt || ' AND LOWER(x.aname) LIKE ''%' || LOWER (p_aname)|| '%' || '''';
    END IF;
    IF p_cname IS NOT NULL THEN
      v_select_stmt := v_select_stmt || ' AND LOWER(x.cname) LIKE ''%' || LOWER (p_cname) || '%' || '''';
    END IF;
    IF p_accnumber IS NOT NULL AND p_accoperator IS NOT NULL
      THEN
      IF p_accoperator = 'Contains' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accnumber) LIKE ''%' || LOWER (p_accnumber) || '%' || '''';
      END IF;
      IF p_accoperator = 'DoesNotContain' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accnumber) NOT LIKE ''%' || LOWER (p_accnumber) || '%' || '''';
      END IF;
      IF p_accoperator = 'StartsWith' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accnumber) LIKE ' || '''' || LOWER (p_accnumber) || '%' || '''';
      END IF;
      IF p_accoperator = 'EndsWith' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accnumber) LIKE ''%' || LOWER (p_accnumber) || '''';
      END IF;
      IF p_accoperator = 'EqualTo' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accnumber) = ' || '''' || LOWER (p_accnumber) || '''';
      END IF;
    END IF;
    IF p_accname IS NOT NULL AND p_accnoperator IS NOT NULL THEN
      IF p_accnoperator = 'Contains' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accname) LIKE ''%' || LOWER (p_accname ) || '%' || '''';
      END IF;
      IF p_accnoperator = 'DoesNotContain' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accname) NOT LIKE ''%' || LOWER (p_accname )|| '%' || '''';
      END IF;
      IF p_accnoperator = 'StartsWith' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accname) LIKE ' || '''' || LOWER (p_accname ) || '%' || '''';
      END IF;
      IF p_accnoperator = 'EndsWith' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accname) LIKE ''%' || LOWER (p_accname ) || '''';
      END IF;
      IF p_accnoperator = 'EqualTo' THEN
        v_select_stmt := v_select_stmt || ' AND LOWER(x.accname) = ' || '''' || LOWER (p_accname ) || '''';
      END IF;
    END IF;
    
    IF p_order_by IS NULL THEN
      IF p_region_id != 0 THEN
          v_select_stmt := v_select_stmt || ' ORDER BY col2 ASC, col3 ASC, col4 ASC, col1 ASC NULLS FIRST ';
      ELSE
          v_select_stmt := v_select_stmt || ' ORDER BY col1  ASC, col3 ASC, col4 ASC NULLS FIRST ';                                                         
      END IF;
    ELSE
      v_select_stmt := v_select_stmt || ' ORDER BY ' || p_order_by || ' NULLS FIRST '; -- could be any column ASC/DESC separated with ,
    END IF;


    v_select_stmt := v_select_stmt || ') y ) ';
    v_select_stmt := v_select_stmt || ' WHERE rnum between ' || TO_NUMBER(p_startrowindex) || ' and ' || TO_NUMBER(p_startrowindex + p_maximumrows) || '';

Thanks in advance
Regards,
Diana

and Connor said...

The big thing here that is doing to hurt you is the "totalrows", because I can you're looking to use a pagination style query here.

We can do all sorts of good optimizations for those style of queries........but only if you don't go asking for the the total row count.

Here's some background on that

The other thing is that I can see a lot of your predicate are wild card based. It might be worth looking at perhaps using a Text based index here and doing a keyword search.

Plenty of examples of that here on AskTOM - just search for "CTXSYS"

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

bind Variables !

Rajeshwaran, Jeyabal, July 28, 2020 - 11:38 am UTC

IF p_region_name IS NOT NULL THEN
      v_select_stmt := v_select_stmt || ' AND LOWER(x.rname) LIKE ''%' || LOWER (p_rname) || '%' || '''';
    END IF;

you get many thing like this:

we are missing bind variable's - doing concatenation - crucial for performance and scalability and open to sql-injection attack.

Diana, July 28, 2020 - 3:12 pm UTC

Hello Tom,

Thank you for your quick answer.

I have a question regarding the total rows. What do you thing could be a better way to get the total rows that matches the select criteria?

Another question is related with the text indexes that you mentioned. This type of index allows to search string in a column using contains but you cannot search if the text is at the begging or at end with. Is there any other way to do this?

Thanks in advance
Regards,
Diana

July 29, 2020 - 3:01 am UTC

What do you thing could be a better way to get the total rows that matches the select criteria?

Easy .... don't :-)

Unless its a 100% read-only system, then any total is no longer a representative of the real data anyway. Same reason why an internet search says "results 1 to 10 of *about* 1000"

My approach to this with customers has been:

Option 1: "You can have total rows, but every query is potentially 10x slower. Are you prepared to sign off on that?"

Option 2: "We will cap the results at (say) 1000 rows. Are you prepared to sign off on that?"

In the latter case, you can still get them paginated results, but you cap the results at an upper limit which ensures you never have to count (say) 1 million rows (or more)

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

bind Variables !

More to Explore

Performance

Query optimization for adhoc search predicates

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

bind Variables !

More to Explore

Performance