Predicate Pushdown Criteria

When processing a given query that includes a predicate, the mechanism provided by the table API Hive integration classes will analyze the query's predicate and apply the following criteria to determine whether all, part, or none of the predicate can be pushed to the database for filtering on the backend.

• If the query's predicate includes components (column, operator, value) with comparison operators from the set {=, >, >=, <, <=}, as well as zero or more combinations of the AND conjunction, the OR conjunction, and/or one or more IN lists, then the predicate is eligible for predicate pushdown.
• Each combination of predicate components that form a valid (as defined by the table API) primary key, index key, or field range is eligible for predicate pushdown; using mechanisms that optimize for scale.
• If the query's predicate is found to contain multiple combinations that are eligible for predicate pushdown, then the combination resulting in optimal performance and scale will be pushed to the server. If more than one of those combinations is found to be optimal, then the first such combination will be pushed.
• Each component of the query's original predicate that is not included in the predicate to push will be added to the residual predicate for client side filtering.
• If all of the predicate components are found to be ineligible for predicate pushdown, then predicate pushdown will not be performed, and the system will fallback to the default mechanism, using the original predicate to perform only client side filtering.

It is important to understand the criteria listed above in conjunction with the data model and search patterns you expect to employ when you define the primary key, (along with any indexes), for a given Oracle NoSQL Database table that will be queried. Although the predicate pushdown mechanism will be employed automatically - without user intervention or special configuration - how you define your table and indexes can affect how well the more common queries you execute will perform and scale.

Predicate pushdown is employed automatically with no obvious indication (other than improved performance) that it is "on and working". As a result, if you wish to verify that the mechanism is indeed operating as described above, you can set the level of the following Oracle NoSQL Database loggers to the DEBUG level:

• oracle.kv.hadoop.hive.table.TableStorageHandlerBase
• oracle.kv.hadoop.hive.table.TableHiveInputFormat

After setting the level of the above loggers to DEBUG, you can run a query and then observe how the predicate pushdown mechanism processes the query's predicate by analyzing the contents of the logger output.