QueryGrid 2.03 and later Teradata connectors support the Teradata Database Remote Table Optimization (RTO) feature introduced in Teradata Database 16.10. RTO provides a way to use the remote data source, rather than the Teradata Database, to process queries involving remote tables.
Make sure that the RTO feature is enabled in the Teradata Database.
RTO can do the following:
- Limit the data transfer from the remote data source to the Teradata Database
- Help the Teradata optimizer to generate better plans when a remote query is involved
- Reduce query execution time for the Teradata Database
- Lower CPU and IO resource use for the Teradata Database
RTO has the following limitations:
- Multiple remote tables that are joined as a cluster are limited to being inner-joined and must have equality binding predicates connecting them.
- Aggregations performed on multiple remote tables are not pushed to the remote, even if all the multiple tables are clustered.
The Teradata connector and Teradata Database optimizer use a push profile to identify an operation that is run on the remote. Operations include pushdown, aggregate, and join operations.
When you run the Teradata connector installation script, RTO is available. The script performs the following actions:
- Inserts default push profile JSON code into the Teradata Database table
- Creates the push profile table, associated functions, and macros under TD_SERVER_DB in the Teradata Database
The following connector types have default push profiles:
- Teradata Database
When you configure a Teradata connector for use with RTO, you must select one of the following push profile properties for the Teradata initiating connector:
- Default Push Profile. The default is recommended for queries with a specific target connector or link. Query performance depends on how the remote system runs the pushed operation and is based on the network interfaces and bandwidth. If the remote system performance is slow and causes undesirable impacts, the default push profile can be replaced with a custom push profile.
- Custom Push Profile. Custom push profiles can be created for site-specific queries that need to be configured based on data transfer thresholds and transfer speeds. Custom push profiles override default push profiles.
Using a push profile does not require changes to the foreign server.