A Teradata query needs to pass through many stages before the successful execution. One needs to understand the step-by-step procedure of the execution of the Teradata query. In this tutorial, you are going to learn the life cycle of a Teradata query in details.
Below are the phases of a Teradata query:
1) Submission of query
The starting point of a Teradata query starts when user submits a query. A call level interface (CLI) prepares parcel from that SQL query. Parcels can be two types –
- Request parcel – contains SQL statement.
- Data parcel – contains any parameter values that may be supplied.
2) Database Connection
Then parcels are sent to Micro Teradata Director Program (MTDP) which establish session to the Teradata database. MTDP prepares a request message containing the parcel for transmission to the Teradata database via network. A Micro Operating System Interface (MOSI) is used to accept this request message to the operating system and network protocol like TCP/IP.
3) Parcel Extraction
The PE receives the request message via gateway and PE will extract request parcel and data parcel. The PE is composed of several modules: syntaxer, resolver, security, optimizer, generator and dispatcher.
Passing through security:-
- Syntaxer: Check the SQL query for proper syntax.
- Resolver: Look up for existence of objects in data dictionary.
- Security: Determine if the user or database has proper access rights.
- Optimizer: It creates most optimal plan for a query.
- Generation: Create steps for the winning plan.
- Dispatcher: Dispatches the steps to AMP via BYNET
4) Query Execution
Each AMP owns a portion of data and manages its own sets of lock and has its own sets of data dictionary. Each one works independently of the other AMPs. Execution steps are received in parallel by all AMPs participating in the step, and an AMP worker task (AWT) on each AMP is assigned to execute the step.
AWT may sort, aggregate and redistribute the data as needed to produce answer set.
5) Production of Result
Each participating AMP will returns it portion of result to the Bynet which merge all the result set and prepare the final result and pass it to dispatcher.
6) Answer to the Source
Dispatcher now packages the result into response parcel and dispatch it to the Gateway to deliver over the network connection as response message. The response parcel is then extracted from response message and the answer set is returned to the user.