When a query is sent from a Teradata system to a remote data source, and a push profile is used, the following occurs:
- The operation is sent to the remote data source. Statistics are collected. The statistics and push profile settings determine whether the operation is performed on the remote data source.
- A temporary table is created on the remote data source and the following scenarios apply:
- If delayed fetch is enabled and chosen based on static statistics, dynamic statistics are collected for the temporary table. The statistics determine whether a remote join or local join is performed on the temporary table.
- If a remote join is chosen (with or without delayed fetch), the temporary table is exported for a local join.
- Statistics about the row count and byte count of the temporary table are collected and sent to the Teradata system optimizer for analysis.
- If the counts are at or below the delayed fetch threshold specified in the push profile, the query result (the temporary table) is sent to the Teradata system.
- If the byte count is greater than the delayed fetch threshold, the dynamic statistics that were collected are sent to the optimizer. The optimizer then decides whether a local join or remote join is performed.
- If a local join is chosen, the temporary table is brought back in a separate execution phase.
- If a remote join is chosen, the local table is exported, joined with the temporary table on the remote system, then brought back to the local system.