Optimization SQL

Question

I'm pretty new to SQL writing and trying to learn how to optimize my query. I saw the suggestion online are: explain plan and indexing.(I'm using Teradata)

I have some questions:

1. Teradata has "Optimizer" after parsing engine, does it mean that the Optimizer pick most efficient plan?

2. I know that explain plan gives us detailed step of how AMP process, and give confidence and estimated time, how can we use it? As a reference guide?

3. Can we run explain plan on the view?

Sorry if my questions are silly to you, I was trying to learn and hope if someone could help. (: Thank you so much in advance.

Answer 1

An attempt at answer. There are some bad assumptions in here, but I've pointed those out where appropriate:

1. Here you say "Sql Server" which is a product by Microsoft. No biggie, but it will lead to confusion, especially here on SO where we answer for multiple RDBMS's. As for Indexing and the Optimizer: Indexing is something you do to a table (or multiple tables in an AJI). It's not something you do when you submit a SELECT statement, so this question is sort of coming from a bad assumption.
   
   Usually we 1) Make a table, 2) Determine and implement the appropriate indexes based on assumed usage, 3) Use it by submitting SQL. The optimizer will look at your SQL and based on indexes, joins, skew, statistics, and other things it will attempt to determine the best way to bring your data together.
   
   So, in short the Optimizer does not Index anything. Indexing is something YOU do on a table before submitting SQL against it.

2. Explain plans tell you exactly how the data will be retrieved from the database, how it will be joined, whether an intermediate result set will be redistributed across amps, and other important information. The less you know about how Teradata works, the less helpful the Explain plan is, but it's a good place to start learning!
   
   Generally you write a query and check the explain. Based on what you find, you may want to change your SQL around or take more drastic steps like creating new indexes. I highly suggest checking out "Explain the Explain" which is a book and class by Allison Torres. She is scary knowledgeable and she breaks down Teradata's explain into very digestible pieces.

3. You can run an explain on a view. Just stick keyword EXPLAIN before the view. It will, of course, use the underlying tables for the explain, since a view is just a definition, using SQL, of a result set.
   
   If there is an index, the optimizer may or may not use it depending on whether it has determined that the index gives the fastest path to the data you are requesting. Again, an Index is defined once for a table (or set of tables in an AJI) and is used by any query that may benefit from it as decided by the optimizer.