Hints

Dynamic Filter Hints

Dynamic filter hints use a different algorithm to execute a query. They should be used when a join condition significantly reduces results.

When a dynamic hint is used, several rules should be disabled:

• FILTER_INTO_JOIN

• GSJoinCommuteRule.INSTANCE

Disable the rules by adding the system property:

-DCoreRules.FILTER_INTO_JOIN=false

-DGSJoinCommuteRule.INSTANCE=false

When a step returns more than maxResults allowed, an exception will be thrown.

The from table clause should be correctly ordered so that each step brings a minimal amount of data.

No nested joins are supported for this hint, the expectation is that we have a join between two tables.

It is recommended to put in the join condition only the condition we want to be pushed to the joined tables condition, and move all the rest REpresentational State Transfer. Application Programming Interface An API, or application programming interface, is a set of rules that define how applications or devices can connect to and communicate with each other. A REST API is an API that conforms to the design principles of the REST, or representational state transfer architectural style. to the where clause.

Join conditions with an expression are not supported for dynamic filters.

For example, T1.f1=T2.f2 ok, expression(T1.f1)=T2.f2 is not valid.

When to Use Dynamic Filter Hints

Select t1.f1, t1,f2, t2.f3,t2.f4 ,t2.f5
From (Select f1, f2  from table1 where f2=?) AS  t1
Join (Select f1,f2,f3  from table2 where f3<?) t2
On t1.f1=t2.f1

Dynamic filter hints should be used when the t2 condition returns too many results, and the join condition filters the results significantly.

Without a dynamic filter in case t1.f1 and t2.f1 are not the table-related routing The mechanism that is in charge of routing the objects into and out of the corresponding partitions. The routing is based on a designated attribute inside the objects that are written to the Space, called the Routing Index. keys, we will bring results according to each table condition to the client and the join filter will be performed on the client side.

Using dynamic filters, the client will call the t1 query and use the results received in this step to add as a condition to the next step. This method makes more remote calls but is efficient in avoiding bringing too many objects to the client. When this approach is used it is important to order the tables according to minimal expected results per each taking into account the previous steps.

Specifying a Hint

Select /*+ DYNAMIC_FILTER(maxResponseSize='1000') */ t1.f1, t1,f2, t2.f3,t2.f4 ,t2.f5
From (Select f1, f2  from table1 where f2=?) AS  t1
Join (Select f1,f2,f3  from table2 where f3<?) t2 On t1.f1=t2.f1

maxResponseSize represents the number of rows received in each step, if number of rows is more than specified an exception will be thrown as it means we will add on next step IN condition including that amount of items which might be inefficient and indicates the query is not ordered correctly.

If maxResponseSize is not specified the default will be used (1000). The global default can be set by com.gs.jdbc.v3.max_response_dynamic_filter.

In case of union, specify a hint for each block.

skipDynamicFilter hint option - it's not always efficient to use dynamic filter per each table. When the size of the table or table condition cuts enough results, there is no need to add all additional conditions for those tables - instead, it is recommended to specify in the hint to skip:

Select /*+ DYNAMIC_FILTER(maxResponseSize='1000')  skipDynamicFilter(table3) */ t1.f1, t1,f2, t2.f3,t2.f4 ,t2.f5,t3.f6,t3.f7
From (Select f1, f2  from table1 where f2=?) AS  t1
Join (Select f1,f2,f3  from table2 where f3<?) AS t2 On t1.f1=t2.f1
Join (Select * from table3 where  f7=?) AS t3 on (t3.f6=t2.f1 or t3.f7<t1.f1)