cost and rule based Optimizer

Database

cost and rule based Optimizer

Hi all,

what is the COST BASED OPTIMIZER and RULE BASED OPTIMIZER and on what criteria optimizer is deciding which one to choose... can we set it in configuration setting...or user can define it in sql/coding

please explain if any body can help...

waiting for reply...

2 REPLIES

Re: cost and rule based Optimizer

Teradata has a cost-based optimizer. It does not have a rule-based optimizer, so you don't have an option to specify that.

The main difference is that a cost-based optimizer first tries to calculate the cost of each of the various paths that it can take to solve the SQL. The cost is usually in terms of CPU time and/or number of IO's. It then decides which path is the least costly option.

A rule-based optimizer operates on a set of "rules" that dictate that it will take a certain path under certain conditions in the SQL. It may even have "hints" that you can provide to the optimizer to help it decide how to solve the query.

Teradata's cost-based optimizer does not allow for hints. One of the key components used in order for Teradata to assign the correct cost is to gather statistics on the tables that the optimizer can use to help figure out how much each potential path will cost. If you do not have these statistics, it could result in the optimizer choosing a less than optimal path.

N/A

Re: cost and rule based Optimizer

You can get help for what statistics to collect by turnong on the helpstats before running doing an Explain on a query.

type:
diagnostic helpstats on for session;
then run an Explain on the query. At the end of the explain will be the recoemmeded stats to collect.