The EXPLAIN command is a powerful tool provided with the Teradata database. It is designed to provide an English explanation of what steps the AMP must complete to satisfy the SQL request. The EXPLAIN is based on the PE's execution plan.
The Parsing Engine (PE) does the optimization of the submitted SQL, the creation of the AMP steps and the dispatch to any AMP involved in accessing the data. The EXPLAIN is an SQL modifier; it modifies the way the SQL operates.
When an SQL statement is submitted using the EXPLAIN, the PE still does the same optimization step as normal. However, instead of building the AMP steps, it builds the English explanation and sends it back to the client software, not to the AMP. This gives users the ability to see resource utilization, use of indices, and row and time estimates.
Therefore, it can predict a Cartesian product join in seconds, instead of hours later when the user gets suspicious that the request should have been finished. The EXPLAIN should be run every time changes to an object's structure occur, when a request is first put into production and other key times during the life of an application. Some companies require that the EXPLAIN always be run before execution of any new queries.
The syntax for using the EXPLAIN is simple: just type the EXPLAIN keyword preceding your valid SQL statement. For example:EXPLAIN<SQL-command> ;
The EXPLAIN can be used to translate the actions for all valid SQL. It cannot provide a translation when syntax errors are present. The SQL must be able to execute in order to be explained.
Once you attain more experience with Teradata and SQL, these terms lead you to a more detailed understanding of the work involved in any SQL request. However, at this stage, there are two primary pieces of information on which to concentrate.
The first is the estimated number of rows that will be returned. This number is an educated guess that the PE has made based on information available at the time of the EXPLAIN. This number may or may not be accurate. If there are current STATISTICS on the table, the numbers are more accurate. Otherwise, the PE calculates a guess by asking a random AMP for the number of rows it contains. Then, it multiples the answer by the number of AMPs to guess a "total row count." At the same time, it lets you know how accurate the number provided might be using the terms in the next chart
The second area to check in the output of the EXPLAIN is the estimated cost, expressed in time, to complete the SQL request. Although it is expressed in time, do not confuse it with either wall-clock or CPU time. It is strictly a cost factor calculated by the optimizer for comparison purposes only. It does not take the number of users, the current workload or other system related factors into account. After looking at the potential execution plans, the plan with the lowest cost value is selected for execution. Once these two values are checked, the question that should be asked is: Are these values reasonable?
For instance, if the table contains one million rows and the estimate is one million rows in 45 seconds, that is probably reasonable if there is not a WHERE clause. However, if the table contains a million rows and is being joined to a table with two thousand rows and the estimate is that two hundred trillion rows will be returned and it will take fifty days, this is not reasonable.
The following EXPLAIN is for a full table scan of the Student Table:EXPLAIN SELECT * FROM Student_table ;
The EXPLAIN estimates, 8 rows and .15 seconds. Since there are 10 rows in the table, the EXPLAIN is slightly off in its estimate. However, this is reasonable based on the contents of the table and the SELECT statement submitted.
The next EXPLAIN is for a join that has an error in it, can you find it?:EXPLAIN
The EXPLAIN estimates nearly 512 rows will be returned and it will take .39 seconds. Although the time estimate sounds acceptable, this is a very small table. Looking at the number of rows returned as 512 with only 14 rows in the largest of these tables. This is not reasonable based on the contents of the tables.
Upon further examination, the product join in step 6 is using (1=1) as the join condition where it should be a merge join. Therefore, this is a Cartesian product join. A careful analysis of the SELECT shows a single join condition in the WHERE clause. However, this is a three-table join and should have two join conditions. The WHERE clause needs to be fixed and by using the EXPLAIN we have saved valuable time.
If you can get to the point of using the EXPLAIN in this manner, you are way ahead of the game. No one will ever have to slap your hand for writing SQL that runs for days, uses up large amounts of system resources and accomplishes absolutely nothing. You say, "Doctor, it hurts when I do this." The Doctor says, "Don't do that." We are saying, "Don't put extensive SELECT requests into production without doing an EXPLAIN on it.
Remember, always examine the EXPLAIN for reasonable results. Then, save the EXPLAIN output as a benchmark against any future EXPLAIN output. Then, if the SQL starts executing slower or using more resources, you have a basis for comparison. You might also use the benchmark if you decide to add a secondary index. This prototyping allows you to see exactly what your SQL is doing.
Some users have quit using the EXPLAIN because they have gotten inaccurate results. From our experience, when the numbers are consistently different than the actual rows being returned and the cost estimate is completely wrong, it is normally an indicator that STATISTICS should be collected or updated on the involved tables.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.