Teradata connectors support the Teradata 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 system, to process queries involving remote tables.
Make sure to enable the RTO feature in the Teradata system by running the installation script and configuring the push profiles as described in this topic.
RTO can do the following:
- Limit the data transfer from the remote data source to the Teradata system
- Help the Teradata optimizer generate better plans when a remote query is involved
- Reduce query execution time for the Teradata system
- Lower CPU and IO resource use for the Teradata system
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 system, even if all the multiple tables are clustered.
The Teradata connector and Teradata system optimizer use a push profile to identify an operation running on the remote table. 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 system table
- Creates the push profile table, associated functions, and macros under TD_SERVER_DB in the Teradata system
The following connector types have default push profiles:
- Generic JDBC
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 profile 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 are 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.