Managing Query Execution in Database Engine Part – 4

Heuristic Method for Query Processing

Heuristic process frequently includes creation of alterations for the query tree by means of stirring the operators up as well as down the tree as a result the altered tree is the same to the tree before the alterations. Earlier to start the heuristic method in details, it is needed to discuss the subsequent guidelines controlling the management of relational algebraic expressions:

6. Altering constraints as well as Projections. For example –

There is no trouble in calculating constraint with a projection as an individual is performing the constraint prior of the projection operation. On the other hand when an individual desire to alter the projection as well as the restriction operations both, that is probably possible, only when the constraint castoff no columns or attributes other than those which are in the projection operation.

7. Transforming constraints with Cartesian product. In a number of circumstances, it is likely possible to apply commutative law to constraints as well as to the product. For example,

In the above mentioned process an individual can expect that the predicate P has columns or attributes from relation or table R solely as well as the predicate q has columns or attributes from relation or table S solely.

8. Altering constraint by means of a Union operator.

9. Altering constraint by means of a Set Difference operator.

10. Altering Projection by means of a Cartesian Product or else by means of a Join operator, an individual can assume that the projection is comprised of the join predicate columns or attributes.

11. Altering Projection by means of a Union operator.

At this point of time an individual can make use of the above mentioned guidelines to renovate the query tree to diminish the query cost. As the cost is presumed to be narrowly connected to the size of the relations or tables on which the process is being carried out, one of the main goals of the alterations that was discussed till now is to decrease the size of in – between relations or tables.

The straightforward alterations take account of the subsequent:

a) Moving constraints down the tree as much as possible. The knowledge is to carry out constraints as fast as possible. If the query is comprised of joins as well as constraints, then moving the limitations down the tree is expected to lead in the direction of considerable savings as the relations or tables which are combined afterward of the constraints are to be expected to be smaller an in a number of cases much smaller than it was before the constraints. This was plainly presented by means of the illustrations that were castoff earlier in this article to display that a number of query plans can be very much costly than others. The query plans that cost the minimum were those in which the constraint was carried out foremost. There are obviously situations where a constraint does not decrease the relation or tables expressively, for an instance, a constraint of picking out only current account details from a big sized table or relation of customers or clients.

b) Projections are implemented as fast as possible. In real life databases, a relation or table can have one hundred (100) or more attributes or columns as well as for that reason the size of every single tuple or row is comparatively very big. A number of relations or tables can even have attributes or columns which are storing images files turning every single row or tuple in those tables or relations very bulky. In such circumstances, if a projection is implemented early as well as it leads to removal of many such columns or attributes so that the subsequent relation or table has rows or tuples of much reduced size, the amount of information that requires to be read in from the disk for the processes which follows can be condensed significantly resulting in cost control. It must be noted that the columns or attributes which an individual want to save from the relations or tables are those which are either required for the outcome or those which are to be castoff in one (1) of the processes which is to be carried out on the table or relation.

c) Optimum Sequencing of the Joins. It was seen previously that the join operator is associative as well as for that reason when a query is comprised of more than one (1) join, it is essential to find an effective sequencing for executing the joins. Sequencing is to be expected to be effective when an individual carries out those joins first which were expected to lead to small outcomes rather than implementing those joins which will probably lead to huge consequences.

d) Cascading constraints as well as Projections. From time to time it is appropriate to perform more than one (1) operation together. The minute constraints as well as projections have the identical operand; the actions can be executed together in that way saving the cost of scanning the relations or tables for more than once.

e) Projections of projections are fused into one (1) projection. Obviously, when more than one (1) projection is to be executed on the identical operand relation or table, the projections must be fused as well as this could lead to a considerable savings as no in-between outcomes is required to be written on the disk as well as read from the disk.

f) Joining certain constraints as well as Cartesian product to create a Join. A query can include a Cartesian product trailed by a constraint somewhat stating a join. The optimizer must identify this in addition to implement a join which is typically much inexpensive to carry out.

g) Categorization is delayed as much as possible. Categorization is usually a long process in addition to by means of deferring categorization; an individual might need to sort a relation or table which is very smaller than it would have been if the categorization was performed previously.

h) A group of actions is reorganized by means of commutative as well as distribution if an efficient method is more effectual.

In the upcoming part we will discuss the Elementary Algorithms for Executing Query Processes particularly the Selection.