Switch off pagination on builder Portlets of Requests category

Starting from version 9.30, builder portlets of the Requests category do not display the total number of records by default for better system performance. When there are entries on the next page, the page navigation buttons are enabled.

Considering the complexity of real data source SQLs used in customer's business, a switch is available for you to disable the new pagination feature at data source level. You can add the the /*NOPAGINATION*/ tag into the particular SQL statement of your concern. This allows you to disable the feature just in case there are some corner cases that the new pagination solution fails to cover. For example, if you have a data source SQL statement, like the following, not working properly as it was,

select distinct request_type_id from kcrt_requests

You can switch to non-pagination logic by adding the /*NOPAGINATION*/ tag into the SQL statement, as follows:

/*NOPAGINATION*/ select distinct request_type_id from kcrt_requests

Note:

  • The /*NOPAGINATION*/ tag is case-insensitive.

  • This tag does not have to be added to the very beginning of a SQL statement. Actually you can add it anywhere as long as the SQL syntax is not broken.

  • This tag disables pagination for this particular data source only.

However, we encourage you to take a look at such complex SQLs and follow the Best Practices on Builder Portlets Pagination below to re-factor them.

Best Practices on Builder Portlets Pagination

This section provides some best practices to help you eliminate the impact of the pagination limitation in your custom SQLs.

  • Avoid suppressing index

    • Do not use "<>" or "!=" when it is possible to use "="

    • Do not use "is null" or "is not null" when it is possible to use some specified value

    • Do not use "like" when the parameter value is certain

    • Create function-based index when a function is used in condition expressions

    • Pay attention to type mismatch. For example, "where varchar2column=1234" will suppress index on varchar2column, you should use "where varchar2column='1234'"

  • Optimize Nested Query as much as Possible

    • Nested query can appear in SELECT statements, FROM clauses, and WHERE clauses. Use as less nested queries as possible.

    • Never use ORDER BY clause in inline views

    • Use materialized views to replace inline views whenever possible

  • Do not use Row_number()

    This analytical function introduces sorting as well. It may cause conflict and performance issue when the original SQL statements are transformed.

  • Optimize access control

    • Do not use KCRT_PARTICIPANT_CHECK.is_participant_of_request(). Instead, join KCRT_PARTICIPANT_CHECK_V.

    • Do not involve KCRT_PARTICIPANT_CHECK_V, KNTA_ELIGIBILITY_CHECK_V, or KNTA_FIELD_SECURITY_V unless it is definitely necessary. Consider using some alternative filters, for example, create_by, create_date, status, or use a snippet from those views.

  • Do not use wildcard (*) in the outermost layer of SELECT statement

    PPM would automatically switch off pagination if it detects * in the outermost layer of the select statement.