Contrast cost estimation and heuristic rules with regard to query optimization.
The contrast between cost-based optimization and heuristic rules with regard to query optimization are :-
Cost-based Optimization
This process of selecting a lower-cost mechanism is known as cost-based optimization. This is based on the cost of the query. The query can use different paths based on indexes, constraints, sorting methods, etc. This method mainly uses the statistics like record size, number of records, number of records per block, number of blocks, table size, whether the whole table fits in a block, organization of tables, uniqueness of column values, size of columns, etc. Some of the features of cost-based optimization are as follows:
- It is based on the cost of the query that to be optimized.
- The query can use a lot of paths based on the value of indexes, available sorting methods, constraints, etc.
- The aim of query optimization is to choose the most efficient path of implementing the query at the possible lowest minimum cost in the form of an algorithm.
- The cost of executing the algorithm needs to be provided by the query Optimizer so that the most suitable query can be selected for an operation.
- The cost of an algorithm also depends upon the cardinality of the input.
Heuristic-based Optimization
Heuristic optimization transforms the query tree by using a set of rules (Heuristics) that typically (but not in all cases) improve execution performance. Some common the common heuristic rules are:
- Perform selection early (reduces the number of tuples) Perform projection early (reduces the number of attributes)
- Perform most restrictive selection and join operations (i.e., with smallest result size) before other similar operations Initially query tree from SQL statement is generated. A query tree is transformed into a more efficient query tree, via a series of tree modifications, each of which hopefully reduces the execution time. A single query tree is involved at last.
Comments
Post a Comment