Indexing for dynamic query in Oracle

dynamic sql in oracle tutorial
oracle dynamic sql example
oracle dynamic sql select example
oracle dynamic sql select into variable
oracle dynamic sql cursor for loop
dynamic sql in oracle stored procedure example
oracle dynamic sql bind variables
oracle dynamic cursor for loop example

I have a procedure where depending on the parameters I build dynamic where condition. If any parameter is null we ignore to check this column in where condition. Can anyone suggest me what is the best way to indexing the table to get the best performance?

Another question is suppose I have a table with 12 column. I have two query one of this have three column in where condition and another query have eight column in where condition. In that case, should I create two different index for better performance?

V_sql VARCHAR2(10000):='SELECT
    SV_ACC_REG.ACC_REG_ID            AS ACC_REG_ID           ,
    SV_ACC_REG.PRODUCT_ID            AS PRODUCT_ID           ,
    GEN_PRODUCT.FULL_NAME            AS PRODUCT_NAME         ,
    SV_ACC_REG.STATUS                AS STATUS               ,
    SV_ACC_REG.OPENING_DATE          AS OPENING_DATE         ,
    SV_ACC_REG.CURRENT_BALANCE       AS CURRENT_BALANCE      ,
    SV_ACC_REG.CLOSING_DATE          AS CLOSING_DATE         ,
    SV_ACC_REG.REG_NO                AS REG_NO               ,
    SV_ACC_REG.IS_WITHDRAW_BY_SINGLE AS IS_WITHDRAW_BY_SINGLE,
    SV_ACC_REG.IS_SINGLE             AS IS_SINGLE            ,
    SV_ACC_REG.IS_EXTENDABLE         AS IS_EXTENDABLE        ,
    SV_ACC_REG.REMARKS               AS REMARKS              ,
    SV_ACC_REG.PR_NO                 AS PR_NO                ,
    SV_ACC_REG.CREATED_ON            AS CREATED_ON           ,
    SV_ACC_REG.CREATED_BY            AS CREATED_BY           ,
    SV_ACC_REG.UPDATED_ON            AS UPDATED_ON           ,
    SV_ACC_REG.UPDATED_BY            AS UPDATED_BY           ,
    SV_ACC_REG.IS_DELETED            AS IS_DELETED           ,
    SV_ACC_REG.DELETED_ON            AS DELETED_ON           ,
    SV_ACC_REG.DELETED_BY            AS DELETED_BY           ,
    SV_ACC_REG.CLIENT_TYPE           AS CLIENT_TYPE          ,
    SV_ACC_REG.IS_TRANSFER           AS IS_TRANSFER          ,
    SV_ACC_REG.WITHDRAW_TYPE         AS WITHDRAW_TYPE        ,
    SV_ACC_REG.DEATH_DATE            AS DEATH_DATE           ,
    SV_ACC_REG.IS_MIGRATE            AS IS_MIGRATE           ,
    SV_ACC_REG.MIGRATE_COMMENTS      AS MIGRATE_COMMENTS     ,
    SV_ACC_REG.CHEQUE_HONOR_DATE     AS CHEQUE_HONOR_DATE    ,
    SV_ACC_REG.SO_NO                 AS SO_NO                ,
    SV_ACC_REG.IS_MINOR              AS IS_MINOR             ,
    SV_ACC_REG.NAME                  AS NAME                 ,
    SV_ACC_REG.IS_OLD                AS IS_OLD               ,
    SV_ACC_REG.IS_NO_PROFIT_CALC     AS IS_NO_PROFIT_CALC    ,
    SV_ACC_REG.IS_SIX_M_PROFIT_CALC  AS IS_SIX_M_PROFIT_CALC ,
    SV_ACC_REG.IS_SEND_DPMG                                  ,
    SV_CUSTOMER_INFO.CUSTOMER_NAME AS CUSTOMER_NAME
    FROM SV_ACC_REG
    LEFT JOIN GEN_PRODUCT ON SV_ACC_REG.PRODUCT_ID=GEN_PRODUCT.PRODUCT_NO
    LEFT JOIN SV_CUSTOMER_INFO ON SV_ACC_REG.ACC_REG_ID = SV_CUSTOMER_INFO.ACC_REG_ID';
V_WHERE VARCHAR2(500):=' WHERE ';
BEGIN
BEGIN

  V_WHERE:=' WHERE ';
    IF p_ACC_REG_ID IS NOT NULL THEN
      V_WHERE := V_WHERE || ' SV_ACC_REG.ACC_REG_ID = '||p_ACC_REG_ID||' AND';
   END IF; 

    IF p_PRODUCT_ID IS NOT NULL THEN
      V_WHERE := V_WHERE || ' SV_ACC_REG.PRODUCT_ID = '||p_PRODUCT_ID||' AND';
    END IF; 

    IF p_STATUS IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.STATUS = '||p_STATUS||' AND';
    END IF; 

    IF p_IS_TRANSFER IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.IS_TRANSFER = '||p_IS_TRANSFER||' AND';
    END IF; 

    IF p_SO_NO IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.SO_NO = '||p_SO_NO||' AND';
    END IF; 

    IF p_IS_OLD IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.IS_OLD = '||p_IS_OLD||' AND';
    END IF; 

    IF p_IS_SEND_DPMG IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.IS_SEND_DPMG = '||p_IS_SEND_DPMG||' AND';
    END IF; 

    IF p_IS_SIX_M_PROFIT_CALC IS NOT NULL THEN
        V_WHERE := V_WHERE || ' IS_SIX_M_PROFIT_CALC= '||p_IS_SEND_DPMG||' AND';
    END IF;  

    IF  LENGTH(' WHERE ') =7 THEN
        V_sql :=V_sql ||'  ORDER BY SV_ACC_REG.ACC_REG_ID ASC';
    ELSE
         V_sql :=V_sql || SUBSTR(V_WHERE, 1, LENGTH(V_WHERE) - 3) ||'  ORDER BY SV_ACC_REG.ACC_REG_ID ASC';
    END IF; 
    --V_sql :=SUBSTR(V_sql, 1, LENGTH(V_sql) - 3);

   --OPEN cur_OUT FOR V_sql USING p_ACC_REG_ID, p_PRODUCT_ID,p_STATUS,p_IS_TRANSFER,p_SO_NO,p_IS_OLD,p_IS_SEND_DPMG,p_IS_SIX_M_PROFIT_CALC;
    OPEN cur_OUT FOR V_sql ;
END;
END;

Can anyone suggest me what is the best way to indexing the table to get the best performance?

Well, there are 10 optional parameters in this query, a number of possible combinations (null/not null) is 2^10 = 1024, so you can get 1 thousand variants of this query, each of them may require a different set of indexes. It is rather impossible to give a sensible hint here. What would I do in your situation:

  1. Deploy the application to production
  2. Allow users to use the application for a few days / a week
  3. Login to the database and run the below query (you must have the appropriate permissions granted by your DBA)
  4. Pick the most problematic queries, tune them, then repeat after a few days/weeks/months again and again.

This query will extract basic statistics about what queries are most commonly used and which consume the most resources. There are many statistics there like EXECUTIONS, total ELAPSED_TIME, BUFFER_GETS etc. etc. which will give you a general picture of the application's operation, user behaviors, etc. etc. and allow to choose the worst queries for further analysis. You can further query v$sql_plan in order to obtain execution plans used by the rdbms (use sql_id and plan_hash_value columns) in order to anayse them.

select 
        SQL_TEXT
        , SQL_FULLTEXT
        , SQL_ID
        , FETCHES
        , EXECUTIONS
        , FIRST_LOAD_TIME
        , PARSE_CALLS
        , DISK_READS
        , BUFFER_GETS
        , USER_IO_WAIT_TIME
        , ROWS_PROCESSED
        , OPTIMIZER_MODE
        , OPTIMIZER_COST
        , HASH_VALUE
        , PLAN_HASH_VALUE
        , CHILD_NUMBER
        , CPU_TIME
        , ELAPSED_TIME
        , IO_INTERCONNECT_BYTES
        , PHYSICAL_READ_REQUESTS
        , PHYSICAL_READ_BYTES
    from v$sql t
    where upper(sql_text) like upper('%FROM SV_ACC_REG%LEFT JOIN GEN_PRODUCT ON SV_ACC_REG.PRODUCT_ID=GEN_PRODUCT.PRODUCT_NO%')
    order by executions desc 

Coding Dynamic SQL Statements, Many types of applications need to use dynamic queries, including: IS TABLE OF VARCHAR2(40) INDEX BY binary_integer; dml_str VARCHAR2 (200);  *Starting in 11.2.0.2, Oracle Database can use function-based indexes to process queries without the function in the where clause. This happens in a special case where the function preserves the leading part of the indexed values.

Due to vulnerability of SQL-Injection I would propose solution like this

V_sql VARCHAR2(10000):='SELECT
    SV_ACC_REG.ACC_REG_ID            AS ACC_REG_ID           ,
    SV_ACC_REG.PRODUCT_ID            AS PRODUCT_ID           ,
    GEN_PRODUCT.FULL_NAME            AS PRODUCT_NAME         ,
    ...
    SV_ACC_REG.IS_OLD                AS IS_OLD               ,
    SV_ACC_REG.IS_NO_PROFIT_CALC     AS IS_NO_PROFIT_CALC    ,
    SV_ACC_REG.IS_SIX_M_PROFIT_CALC  AS IS_SIX_M_PROFIT_CALC ,
    SV_ACC_REG.IS_SEND_DPMG                                  ,
    SV_CUSTOMER_INFO.CUSTOMER_NAME AS CUSTOMER_NAME
    FROM SV_ACC_REG
    LEFT JOIN GEN_PRODUCT ON SV_ACC_REG.PRODUCT_ID=GEN_PRODUCT.PRODUCT_NO
    LEFT JOIN SV_CUSTOMER_INFO ON SV_ACC_REG.ACC_REG_ID = SV_CUSTOMER_INFO.ACC_REG_ID';

V_WHERE VARCHAR2(500);

cur INTEGER := DBMS_SQL.OPEN_CURSOR;
curRef SYS_REFCURSOR;
ret INTEGER;

BEGIN

    IF p_ACC_REG_ID IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND SV_ACC_REG.ACC_REG_ID = :p_ACC_REG_ID';
    END IF; 
    IF p_PRODUCT_ID IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND SV_ACC_REG.PRODUCT_ID = :p_PRODUCT_ID';
    END IF; 
    IF p_STATUS IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND SV_ACC_REG.STATUS = :p_STATUS';
    END IF; 
    IF p_IS_TRANSFER IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND SV_ACC_REG.IS_TRANSFER = :p_IS_TRANSFER';
    END IF; 
    IF p_SO_NO IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND SV_ACC_REG.SO_NO = :p_SO_NO';
    END IF; 
    IF p_IS_OLD IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND SV_ACC_REG.IS_OLD = :p_IS_OLD';
    END IF; 
    IF p_IS_SEND_DPMG IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND SV_ACC_REG.IS_SEND_DPMG = :p_IS_SEND_DPMG';
    END IF; 
    IF p_IS_SIX_M_PROFIT_CALC IS NOT NULL THEN
       V_WHERE := V_WHERE || ' AND IS_SIX_M_PROFIT_CALC= :p_IS_SIX_M_PROFIT_CALC';
    END IF;  

    V_WHERE := REGEXP_REPLACE(V_WHERE, '^ AND', 'WHERE');
    V_sql := V_sql || V_WHERE ||' ORDER BY SV_ACC_REG.ACC_REG_ID ASC';
    DBMS_SQL.PARSE(cur, V_sql, DBMS_SQL.NATIVE);


    IF p_ACC_REG_ID IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':p_ACC_REG_ID', p_ACC_REG_ID); 
    END IF; 
    IF p_PRODUCT_ID IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':p_PRODUCT_ID', p_PRODUCT_ID); 
    END IF; 
    IF p_STATUS IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':p_STATUS', p_STATUS); 
    END IF; 
    IF p_IS_TRANSFER IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':p_IS_TRANSFER', p_IS_TRANSFER); 
    END IF; 
    IF p_SO_NO IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':p_SO_NO', p_SO_NO); 
    END IF;     
    IF p_IS_OLD IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':p_IS_OLD', p_IS_OLD); 
    END IF; 
    IF p_IS_SEND_DPMG IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':IS_SEND_DPMG', IS_SEND_DPMG); 
    END IF; 
    IF p_IS_SIX_M_PROFIT_CALC IS NOT NULL THEN
       DBMS_SQL.BIND_VARIABLE(cur, ':p_IS_SIX_M_PROFIT_CALC', p_IS_SIX_M_PROFIT_CALC ); 
    END IF;  

    ret := DBMS_SQL.EXECUTE(cur);
    curRef := DBMS_SQL.TO_REFCURSOR(cur);

END;

Regarding performance I would recommend to create individual indexes on each column you may have in WHERE condition, i.e. one column per index. Oracle is able to combine indexes (see examples https://jonathanlewis.wordpress.com/2010/11/26/index-join-2/), however unless you force it by INDEX_JOIN hint this might be very seldom the case. Typically Oracle will take only the most selective Index. For example if the result of SV_ACC_REG.PRODUCT_ID = 12345 returns only a couple of rows then the other conditions/indexes do not matter any more in terms of performance.

For combinations which are used very frequently you may consider dedicated Composite Indexes.

Columns SV_ACC_REG.STATUS, SV_ACC_REG.IS_SEND_DPMG, SV_ACC_REG.IS_TRANSFER, SV_ACC_REG.IS_OLD, IS_SIX_M_PROFIT_CALC seem to have very low cardinality, I assume they just contain Yes and No values or similar. Consider to use Bitmap-Indexes for these columns. Bitmap-Indexes are actually intended to be combined with each other, that's how they work most efficient.

However, Bitmap-Indexes are not appropriate in OLTP applications, i.e. you should not use them when table data often changes (DELETE, INSERT, UPDATE). It becomes even worse if such changes are done by multiple sessions at the same time.

Feature Index Monitoring should help you to detect useless indexes.

Dynamic Indexes, use of parallel query. I think I made it quite clear in pointing out the circumstances where I would consider employing this option. You are certainly  Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query. I begin our look at Oracle indexing with a review of standard Oracle b-tree index methodologies.

This will take some work, but you might not need a terribly complex index structure.

For quite a while, Oracle has supported skip-scans on the index -- in addition to full index scans and range scans. This can be quite powerful, but it is unclear what exact indexes would be needed.

Here is a good blog post that explains what a skip scan is. You may want to see if it works on your data, by putting some low-cardinality columns first.

Performing SQL Operations with Native Dynamic SQL, With dynamic SQL, you can directly execute most types of SQL statement, The collection type can be any PL/SQL collection type: index-by table, nested table,  I am creating new table while running a procedure, I would like to know how can i create related indexes dynamically. table x (a number(20)); idx-1 idx-2 idx-3 table x-copy generated. following should take as table x indexes. idx-1 idx-2 idx-3 can anyone have this script.

By experimenting, I get better performance by this technique. please ignore SQL injection issue. I will fix it later. if anyone has any better idea please add this.

IF p_REG_NO IS NOT NULL THEN
  V_WHERE := V_WHERE || ' SV_ACC_REG.REG_NO ='''||p_REG_NO||''' AND';
END IF; 


IF p_PRODUCT_ID IS  NULL THEN
  V_WHERE := V_WHERE || ' SV_ACC_REG.PRODUCT_ID IN(1,2,3,4,5,6) AND';
ELSE
    V_WHERE := V_WHERE || ' SV_ACC_REG.PRODUCT_ID ='||p_PRODUCT_ID||' AND';
END IF; 

IF p_STATUS IS NULL THEN
    V_WHERE := V_WHERE || ' SV_ACC_REG.STATUS IN (0,1) AND';
ELSE    
    V_WHERE := V_WHERE || ' SV_ACC_REG.STATUS ='||p_STATUS||' AND';
END IF; 

IF p_IS_TRANSFER IS NULL THEN
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_TRANSFER IN(0,1) AND';
ELSE 
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_TRANSFER ='||p_IS_TRANSFER||' AND';    
END IF; 

IF p_SO_NO IS NULL THEN
    BEGIN
        --Select listagg(SO_NO,', ')  within group(order by SO_NO) INTO V_List from GEN_SO;
        V_WHERE := V_WHERE || ' SV_ACC_REG.SO_NO> 0 AND';
    END;
ELSE 
    V_WHERE := V_WHERE || ' SV_ACC_REG.SO_NO ='||p_SO_NO||' AND';   
END IF; 

IF p_IS_OLD IS NULL THEN
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_OLD IN (0,1) AND';
ELSE 
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_OLD ='||p_IS_OLD||' AND';
END IF; 

IF p_IS_SEND_DPMG IS NULL THEN
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_SEND_DPMG IN(0,1) AND';
ELSE
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_SEND_DPMG ='||p_IS_SEND_DPMG||' AND';
END IF; 

IF p_IS_SIX_M_PROFIT_CALC IS NULL THEN
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_SIX_M_PROFIT_CALC IN(0,1) ';
ELSE 
    V_WHERE := V_WHERE || ' SV_ACC_REG.IS_SIX_M_PROFIT_CALC='||p_IS_SIX_M_PROFIT_CALC||' ';
END IF;  

PL/SQL Dynamic SQL, Dynamic SQL is a programming methodology for generating and running SQL and data types of the input and output variables of the dynamic SQL statement. type that SQL does not support (such as associative array indexed by string). 11 Configuring the Search Index. This chapter describes how to configure the Oracle WebCenter Content Server search index. Content Server interfaces with a variety of indexing tools such as commercial search engines and databases. This chapter includes the following topics: Section 11.1, "Variances in Indexing Tools and Methods"

Oracle Dynamic SQL, However, some dynamic queries require complex coding, the use of special References to database objects such as columns, indexes, sequences, tables,  Expression indexing is available only in Oracle Database Enterprise Edition. An index can be defined on a column storing expressions to quickly find expressions that evaluate to true for a data item. This is most helpful when a large expression set is evaluated for a data item.

EXECUTE IMMEDIATE Statement, The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or define variables and bind arguments cannot be Booleans or index-by tables. If you use C/C++, you can call dynamic SQL with the Oracle Call Interface (OCI), or you can use the Pro*C/C++ precompiler to add dynamic SQL extensions to your C code. If you use COBOL, you can use the Pro*COBOL precompiler to add dynamic SQL extensions to your COBOL code. If you use Java,

Using Dynamic SQL for Multirow Queries, There's nothing dynamic about this query. INDEX BY PLS_INTEGER; But OPEN FOR is most commonly used with dynamically constructed queries. You can create indexes on columns to speed up queries. Indexes provide faster access to data for operations that return a small portion of a table's rows. In general, you should create an index on a column in any of the following situations: The column is queried frequently. A referential integrity constraint exists on the column.

Comments
  • Standard approach here would be to work out which combinations of columns will be present and then build concatenated indexes for each combination, and to avoid having 1 index per column. Or, my first stab at this would be to identify "header" columns (hopefully just a handful) one of which would be present in any given combination and index them, then fine tune these indexes by adding secondary columns to these indexes.
  • What is the cardinality of the columns you may have in WHERE clause? I assume columns IS_TRANSFER or STATUS just have very low number of values, most likely only 2.
  • How many rows in this table?
  • @APC Around 5.6 million in SV_ACC_REG table
  • Dosn't the number of possible combinations is 2^10?