SHARD_ENABLE Clause

The SHARD_ENABLE keyword indicates that a query referencing the defined function can be pushed down into the shards of a sharded database (SDB).

When using the SHARD_ENABLE clause, the query optimizer takes the initiative to push the execution of the PL/SQL function to the shards.

The SHARD_ENABLE clause can appear in the following SQL statement:

Queries with PL/SQL functions created with the SHARD_ENABLE keyword will be pushed down, if possible, to the shards and executed as multishard queries. If possible refers to the fact that there may be other parts of the query that do not allow the pushdown. Therefore, the optimizer will make the pushdown decision.

Queries with PL/SQL functions created without the SHARD_ENABLE keyword will not be pushed down to the shards and executed as cross shard queries on the coordinator.

Topics

Syntax

shard_enable_clause ::=

Description of the illustration shard_enable_clause.eps

Semantics

shard_enable_clause

Usage Notes

It is up to you to decide whether a function execution can be pushed to the shards. However, there are some instances where you should decide not to use shard_enable:

Functions referencing any session context variables that may be different on the shards and coordinator.
Functions referencing any global variables that may be different on the shards and coordinator.
Functions referencing any data local to the coordinator.

In some cases you may decide it is safe to push a function, even if it references a package global variable or reads data from a table.

Even if a PL/SQL function is marked with SHARD_ENABLE clause, there are times when the evaluation needs to happen on the coordinator, meaning the function evaluation is not pushed to shards. Possible scenarios include:

When the function is in SELECT list and there is a join between sharded tables and the join is not on a sharding key (note that a join between sharded and duplicated key is okay),
When the function is in SELECT list and there is a join with a local (non-sharded) table,
If such a function is present in WHERE clause and it takes input parameters as column of multiple sharded tables and there is no join on sharding key.

Pushing eligible functions down to shards to execute as multi shard queries rather than cross shard queries can result in significant performance improvement by:

Distributing the computation by performing evaluation of PL/SQL functions on each shard.
Reducing the size of the data returned from shards when the predicate involves a PL/SQL function, resulting in smaller inputs for joins on the coordinator.