Re: HELP avoiding full table scans with NVL and DECODE

A copy of this was sent to "Kel Brigman" <kelley.brigman_at_ac.com> (if that email address didn't require changing) On 20 Feb 1999 00:34:23 GMT, you wrote:

>I need to tune numerous queries that use NVL with DECODE to allow users to
>leave fields null when querying. This seems to force a full table scan! The
>table has grown beyond 7,000,000 rows, and performance now terrible!
>
>Works like this:
>
>SELECT col1, col2, col3...
>FROM table
>WHERE NVL(variable1, 'T') = DECODE(variable1, NULL, 'T', column1)
>AND NVL(variable2, 'T') = DECODE(variable2, NULL, 'T', column2)
>...
>

well, applying that function to the column is going to invalidate the use of indexes. You don't say what environment you are using to execute this query but lets supposes its in a pl/sql routine. You could code something like:

SQL> create or replace procedure demo_proc( var1 in varchar2, var2 in varchar2 )   2 as

  3          type refCur is ref cursor;
  4          c1 refCur;
  5          l_ename varchar2(40);
  6          l_job   varchar2(40);
  7          l_why   varchar2(40);
  8  begin
  9          if ( var1 is not null and var2 is not null ) then
 10                open c1 for select ename, job, 'both not null' why  from emp
 11                             where var1 = ename and var2 = job;
 12          elsif ( var1 is null and var2 is null ) then
 13                open c1 for select ename, job, 'both null' why  from emp;
 14          elsif ( var1 is null ) then
 15                open c1 for select ename, job, 'var2 not null' why
 16                              from emp where var2 = job;
 17          elsif ( var2 is null ) then
 18                open c1 for select ename, job, 'var1 not null' why
 19                              from emp where var1 = ename;
 20          end if;
 21  
 21          loop
 22                  fetch c1 into l_ename, l_job, l_why;
 23                  exit when c1%notfound;
 24                  dbms_output.put( l_ename  || ' ' );
 25                  dbms_output.put( l_job  || ' ' );
 26                  dbms_output.put_line( l_why || ' ' );
 27          end loop;

Procedure created.

SQL> exec demo_proc( 'MILLER', 'CLERK' ); MILLER CLERK both not null

PL/SQL procedure successfully completed.

SQL> exec demo_proc( null, 'CLERK' );
SMITH CLERK var2 not null
ADAMS CLERK var2 not null
JAMES CLERK var2 not null
MILLER CLERK var2 not null

PL/SQL procedure successfully completed.

SQL> exec demo_proc( null, null );
SMITH CLERK both null
ALLEN SALESMAN both null
...
FORD ANALYST both null
MILLER CLERK both null

PL/SQL procedure successfully completed.

SQL> exec demo_proc( 'KING', null );
KING PRESIDENT var1 not null

PL/SQL procedure successfully completed.

so, given specific inputs, the query is opened for the 'right' set of predicates.

You can achieve similar results with dbms_sql as well. It would look like this:

SQL> create or replace procedure demo_proc( var1 in varchar2, var2 in varchar2 )   2 as

  3      l_ename varchar2(40);
  4      l_job   varchar2(40);
  5  
  5      l_where_clause varchar2(1024);
  6      l_and           varchar2(5);
  7      l_cursor       integer default dbms_sql.open_cursor;
  8      l_status       integer;
  9  
  9      procedure build_where( v in varchar2, vname in varchar2 )
 10      is
 11      begin
 12        if ( v is not null ) then
 13           l_where_clause := l_where_clause || l_and || vname || '=:'||vname;
 14           l_and := ' and ';
 15        end if;
 16      end;
 17      procedure bind_var( v in varchar2, vname in varchar2 )
 18      is
 19      begin
 20        if ( v is not null ) then
 21           dbms_sql.bind_variable( l_cursor, vname, v );
 22        end if;
 23      end;
 24  begin
 25      build_where( var1, 'ename' );
 26      build_where( var2, 'job' );
 27      /* .... repeat above N times for each bind var ... */
 28  
 28      if ( l_where_clause is NOT NULL )
 29      then
 30          l_where_clause := ' where ' || l_where_clause;
 31      end if;
 32  
 32      dbms_sql.parse( l_cursor,
 33                     'select ename, job from emp ' || l_where_clause,
 34                      dbms_sql.native );
 35  
 35      dbms_sql.define_column( l_cursor, 1, l_ename, 40 );
 36      dbms_sql.define_column( l_cursor, 2, l_job, 40 );
 37  
 37      bind_var( var1, 'ename' );
 38      bind_var( var2, 'job' );
 39      /* .... repeat above N times for each bind var ... */
 40  
 40      l_status := dbms_sql.execute(l_cursor);
 41      loop
 42          exit when ( dbms_sql.fetch_rows(l_cursor) <= 0 );
 43          dbms_sql.column_value( l_cursor, 1, l_ename );
 44          dbms_sql.column_value( l_cursor, 2, l_job );
 45  
 45          dbms_output.put( l_ename  || ' ' );
 46          dbms_output.put_line( l_job  || ' ' );
 47      end loop;
 48      dbms_sql.close_cursor( l_cursor );

Procedure created.

SQL> exec demo_proc( 'MILLER', 'CLERK' ); MILLER CLERK PL/SQL procedure successfully completed.

SQL> exec demo_proc( null, 'CLERK' );
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK PL/SQL procedure successfully completed.

SQL> exec demo_proc( null, null );
SMITH CLERK
ALLEN SALESMAN
...
FORD ANALYST
MILLER CLERK SQL> exec demo_proc( 'KING', null );
KING PRESIDENT PL/SQL procedure successfully completed.

In your case, dbms_sql is probably a better choice since you have so many combinations of cursors apparently (given your example below)...

this will allow the optimizer to come up with the best plan given the inputs. It will require more shared sql area since there is more then one query but you are always building the same sort of query so if two people both supply different values for the same set of input variables -- they will reuse eachothers parsed queries.

>if variable 1 is NOT NULL, but variable 2 IS NULL, then you get
>WHERE variable1 = column1
>AND 'T' = 'T' (not 'T' = column2)
>
>the second column name is out of the query, and it can't hit column2 index.
>Any ideas on avoiding a full table scan??!!
>thanks in advance!
>
>SELECT OTX.OTX_OB_TXN_ID,
> OTX.OTX_STATUS_CD,
> OTX.OTX_AP_ID,
> OTX.OTX_BATCH_TMSTMP,
> OTX.OTX_TC_DA,
> OTX.OTX_SI_DA,
> OTX.OTX_NEW_BTN_NO,
> OTX.OTX_NEW_WTN_NO,
> OTX.OTX_DAT,
> OTX.OTX_NEW_CUSTMR_TYP_INDR,
> OTX.OTX_J_INDR,
> OTX.OTX_CIC_4_DA
>FROM OUTXN10T OTX
>WHERE NVL(in_otx_internal_file_nm, 'T') = DECODE(in_otx_internal_file_nm,
>NULL, 'T',
> OTX.OTX_INTERNAL_FILE_NM)
>AND NVL(in_otx_status_cd, 'T') = DECODE(in_otx_status_cd, NULL, 'T',
>OTX.OTX_STATUS_CD)
>AND NVL(in_otx_ap_id, 'T') = DECODE(in_otx_ap_id, NULL, 'T',
>OTX.OTX_AP_ID)
>AND NVL(in_otx_tc_da, 'T') = DECODE(in_otx_tc_da, NULL, 'T',
>OTX.OTX_TC_DA)
>AND NVL(in_otx_si_da, 'T') = DECODE(in_otx_si_da, NULL, 'T',
>OTX.OTX_SI_DA)
>AND NVL(in_otx_new_wtn_no, 'T') = DECODE(in_otx_new_wtn_no, NULL, 'T',
>OTX.OTX_NEW_WTN_NO)
>AND NVL(in_otx_new_custmr_typ_indr, 'T') =
>DECODE(in_otx_new_custmr_typ_indr, NULL, 'T',
> OTX.OTX_NEW_CUSTMR_TYP_INDR)
>AND ((DECODE(in_start_date, NULL, 'T', 'F') = 'T')
>OR (OTX.OTX_BATCH_TMSTMP BETWEEN in_start_date AND in_end_date))
>AND NVL(in_otx_new_btn_no, 'T') = DECODE(in_otx_new_btn_no, NULL, 'T',
>OTX.OTX_NEW_BTN_NO)
>AND NVL(in_otx_cic_4_da, 'T') = DECODE(in_otx_cic_4_da, NULL, 'T',
>OTX.OTX_CIC_4_DA);
  Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation