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.
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.
- 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 = ?