Using Hints (Publication Service Only)

Hints help improve the performance of your queries. When Publication Service performs a poll operation to fetch data from a table, using hints greatly enhances the query.

Note: Hints are only supported in Oracle and SQL Server databases.

To use hints, you can add the following line to the adapter TRA properties file:

adb.table_name.poll.hint hint_value

where, table_name is the name of your table and hint_value is the hint.

Examples

The ways of using hints vary depending on different databases you use.

The following examples show the ways of using hints based on different databases:
  • To force an index scan when polling in an Oracle database, set the property as follows:

    adb.p1.poll.hint /*+INDEX(P1,P1_INDX)*/

    where p1 is the publisher table, and P1_INDX is the index created on the publishing table.

    The adapter processes this select query on the publishing table:

    SELECT /*+INDEX(P1,P1_INDX)*/ * FROM P1 WHERE ID = ?

  • To use the NOLOCK hint when polling in an SQL Server database, set the property as follows:

    adb.p1.poll.hint WITH(NOLOCK)

    where P1 is the publisher table.

    The adapter processes this select query on the publishing table, but will not issue shared locks or honor exclusive locks:

    SELECT * FROM P1 WITH(NOLOCK) WHERE ADB_L_DELIVERY_STATUS =’N’

  • To force an index scan when the adapter fetches records from a child table, set the property as follows:

    adb.C1.poll.hint /*+INDEX(C1,C1_INDEX)*/

    where C1 is the child table, and C1_INDEX is the index created on the child table.

    The adapter processes this select query on the child table:

    SELECT /*+INDEX(C1,C1_INDEX)*/ * FROM C1 WHERE ID = ?