Hints Usage

You can use hints with the Data Event Poller activity when performing a polling operation to fetch data from a table. Hints help improve the performance of your queries. Note that hints are available only for the Oracle and MSSQL databases.

Syntax

You can add hints on the Advanced tab of the Data Event Poller activity.

Syntax for Oracle database

/*+INDEX(<tablename>,<indexname>)*/;/*+INDEX(<tablename>,<indexname>)*/

Syntax for MSSQL database

/*+WITHNOLOCK(<tablename>)*/

where,

tablename is the name of the table you want to poll.

indexname is the index created on that table.

You can provide hints on multiple tables by using a semicolon to separate each table. You can use any tables and in any order - publishing table, child tables, or reference table.

Examples

The following examples show the usage of hints:

  • To force an index scan when polling an Oracle database, type the hint as follows:

    /*+INDEX(P1,P1_INDX)*/

    where,

    p1 is the publisher table.

    P1_INDX is the index created on the publishing table.

    The plug-in processes this select query on the publishing table as follows:

    SELECT /*+INDEX(P1,P1_INDX)*/ * FROM P1 WHERE ID = ?
  • To force an index scan when the plug-in fetches records from a child table, type the hint as follows:

    /*+INDEX(C1,C1_INDEX)*/

    where,

    C1 is the child table

    C1_INDEX is the index created on the child table.

    The plug-in processes this select query on the child table as follows:

    SELECT /*+INDEX(C1,C1_INDEX)*/ * FROM C1 WHERE ID = ?
  • To use the WITHNOLOCK hint when polling an MSSQL database, type the hint as follows:

    /*WITHNOLOCK(P1)*/

    where,

    P1 is the publishing table.

    The plug-in processes this select query on the publishing table as follows:

    SELECT * FROM P1 WITH(NOLOCK) WHERE id = ?