xDM

Understanding pagination

If you've already had a look at the queries generated by xDM to select and then display records in your applications, you may be familiar with the wrapper select statements that reduce the scope of the current selection to the first ""page"" of records. Since Oracle SQL does not have native support for LIMIT and OFFSET, this is implemented with a set of filters using the rownum pseudo-column.

A simplified version would be

select * from (
  select * from gd_customer
  order by customer_id
 ) where rownum < 20

Paging always relies on a deterministic sort order (the order by part of the inner select statement) to make sure that two subsequent calls display the same records in the same order, hence allowing accurate pagination if you scroll down.

This syntax allows oracle to understand that you're only interested in displaying the first records and to use a FIRST_ROWS strategy, avoiding a full table scan if you have a proper index on customer_id. Since records are already sorted in the index, getting the first 20 records boils down to scanning the first 20 rows in the index and do a quick peek to the table itself.

What about master records then ?

The default sort order when displaying master records is an order by PublisherID ,SourceID.

If displaying a master-record-based collection is very slow, it's likely that oracle is failing to use the built-in index that covers those two attributes: pkmd_customer in the current example.

If the oracle optimizer keeps doing full scans on your MD tables and does not use the indexes based on string attributes, it's likely that your default regional setting (NLS settings) prevent that.

You can check your current NLS Settings using this sql statement

SELECT DB.PARAMETER, DB.VALUE ""Database"", I.VALUE ""Instance"", S.VALUE ""Session""
 FROM  NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S
 WHERE DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+)
 ORDER BY 1;
 

To get oracle to use your string-based indexes (including the master records table primary key) and avoid the overhead of a sort on top of a full table scan, you may have to force NLS_SORT to BINARY at the session level.

ALTER SESSION SET NLS_SORT = 'BINARY';

In a typical (tomcat) semarchy.xml file, this translates as follows, using the initSQL property for your data location's connection properties (single quotes require escaping with xml entities).

initSQL=""ALTER SESSION SET NLS_SORT = "BINARY"""

As a side note ...

We generally encourage disabling user-defined sort in business views (and collections) when you're dealing with millions of records. Clicking on a column header and trying to sort records on an un-indexed attributes will result in a looong full table scan to display the first page of records, and then the next pages if you scroll down.

In most cases, if the user is willing to do a search, opening a business view on a selection of custom search forms that cover the main search use cases is a good practice. These custom search forms can be supported by custom indexes to provide a fast response and a good user experience.

"