Example of a tree

Now we shall show, as search for an example of connection on fig. 1 is carried out. First of all, we find all reasonable ways of access to single attitudes{relations} with application only local predicates. Results for this example are shown on fig. 2. For the table emp is present three ways of access: an index on dno, an index on job and segment scanning. Interesting orders are dno and job. The index on dno provides trains in the order dno, and the index on job provides trains in the order job. For our purposes the way of access through segment scanning is considered disorder. For this example we assume, that the index on job is the cheapest way, therefore the segment way of access is cut. For the attitude{relation} dept is present two ways of access: an index on dno and segment scanning. We assume, that the index on dno is cheaper, therefore the segment way of access is cut. For the attitude{relation} job is present two ways of access: an index on job and segment scanning. We assume, that the segment way of access is cheaper, both ways therefore are saved. Just the described results are saved in a tree of search, as shown in fig. 3. In these figures of a designation c (emp.dno) or c (e.dno) designate cost of scanning emp through dno with application of all predicates which are applicable{applied}, that trains from the specified attitudes{relations} are already read. The designation ni represents capacities of various partial results.

Further there are decisions for pairs attitudes{relations} by connection of the second attitude{relation} to results for the single attitudes{relations} shown on fig. 3. For each single attitude{relation} we find ways of access for connection with each second attitude{relation} for which there is a predicate connecting it{him} with the first attitude{relation}. First we make a choice of ways of access for connections by the enclosed cycles. In this example we assume, that connection emp-job is the cheapest at access to job through an index job. It is quite probable, as allows to read out directly trains about conformity job (without need{requirement} for scanning the attitude{relation}). In practice cost of connection is estimated with isprol`zovaniem resulted before formulas, and the cheapest way gets out. For connection of the attitude{relation} emp with the attitude{relation} dept we assume, that the cheapest is the index dno. Naluchshij the way of access for each attitude{relation} of the second level is combined with each of plans about fig. 3 for formation of decisions with the enclosed cycles, shown on fig. 4.

Further we generate decisions about use of a method of scanning with merge. As we videm in the left part fig. 3, is present an opportunity of scanning of the attitude{relation} emp in the order dno, therefore there is an opportunity to use this scanning and scanning on dno attitudes{relations} dept to execute connection by scanning with merge without any sorting. Though it is possible to execute connection by merge without sorting, can be more cheaply use an index job on emp, sort on dno, and then execute merge. We shall notice, that we do not consider{examine} an opportunity of sorting of the table dept because the cheapest scanning of this table already provides orderliness on dno.

For merge job with emp we consider{examine} only an opportunity of use indeksv job on emp because it is the cheapest way of access for emp irrespectively orderliness. Using an index job on job we can execute merge without any sorting. However can appear is cheaper to sort job, using segment scanning for maintenance of the data for sorting and then to execute merge.

On fig. 3 we see, that by the access, chosen for the attitude{relation} dept, the index dno is. After access to dept through this index we can execute merge with emp, using an index dno on emp, again without any sorting. However can appear is cheaper all over again to sort emp, using an index job for maintenance of the data for sorting and then to make merge. Both these of a case are shown in a Fig. 5.

When calculate all of cost shown on fig. 5 and 6, they are compared to the cheapest equivalent decision (the same tables and the same order of result), and the cheapest decision is saved. After that cutting off there are decisions for all three attitudes{relations}. For each pair attitudes{relations} we find ways of access for connection with the staying third attitude{relation}. As well as earlier, we expand a tree, using methods of the enclosed cycles and scannings with merge for connection of the third attitude{relation}. The tree of search for three attitudes{relations} is shown on fig. 6. We shall notice, that in one case it is sorted both the compound attitude{relation} and the added table (job). We shall notice also, that in some cases sorting is not made at all. In these cases the compound result is materialized on one train for time, and intermediate compound attitudes{relations} are never saved. As well as earlier, in process of calculation stoimostej he are compared to the cheapest decision.

6. The enclosed searches

The search can appear as an operand of a predicate in the form " expression operator query ". Such search is called as the Enclosed Search, or the Subrequest. If operation is one of six operations of scalar comparison (=, - =,>,> =, <, <=) the subrequest should return a unique value. The following example with use of operation "=" has been resulted in section 2:

select name

from employee

where salary =

(select avg (salary)

from employee)

If operation is in or not in the subrequest can return set of values. For example:

select name

from employee

where department_number in

(select department_number

from department

where location ='denver ')

In both examples the subrequest is required to be calculated only once. OPTIMIZATOR organizes calculation of a subrequest before calculation of search of the top level. If the unique value comes back, it is substituted in search of the top level as though was a part of the initial operator of search; for example, if avg (sal) in the first example it was calculated in 15000 in execution time the predicate would assume air " salary = 15000 ". If the subrequest can return a set of values, they come back as the time list which internal form is more effective, than at the attitude{relation}, but supposes only consecutive access. If in the second example the subrequest returns the list (17,24) the predicate is calculated in a manner similar to as though the initial predicate looked like department_number in (17,24).

The subrequest also can contain a predicate with a subrequest, and (theoretically any depth of an enclosure is supposed. If such subrequests do not refer on stolbcy tables of blocks of searches of higher level, they can be calculated before calculation of search of the top level. In this case all over again most deeply enclosed searches as any subrequest should be calculated earlier are calculated, than his{its} parental search can be calculated.

In a subrequest the link to the value received from a possible{probable} train of the block of search of more top level (the example is see lower) can contain. Such search is called as a correlation subrequest. Basically, the correlation subrequest should be calculated each time before the parental predicate of correlation search can be checked up for acceptance or a deviation{rejection} of a possible{probable} line. As an example we shall consider the following search:

select name

from employee x

where salary> (select salary

from employee

where employee_number=x.manager)

Here names of the employees earning more the manager get out. x identifies the block of search and the attitude{relation} which gives a possible{probable} train for correlation. For each possible{probable} train of the block of search of the top level for calculation of a subrequest value of a column manager is used. The result of a subrequest then comes back in a predicate " salary> " for check of acceptance of a possible{probable} train.

If the correlation subrequest is not directly under the block of search to which refers, and is separated from this block by one or several intermediate blocks calculation of a correlation subrequest should be made before calculation of highest of intermediate blocks. For example:

level 1 select name

from employee x

where salary>

level 2 (select salary

from employee

where employee-number =

level 3 (select manager

from erployee

where employee-number = x.manager))

Here names of the employees earning more of the manager of the manager get out. As before, for each possible{probable} train of the block of search of a level 1 value of a column employee.manager is used for calculation of the block of search of a level 3. In this case, as the subrequest of a level 3 refers to value of a level 1, but does not refer to values of a level 2, he is calculated one time for each new possible{probable} train of a level 1, instead of for each possible{probable} train of a level 2.

If value, on which the correlation search refers (x.manager in the resulted example) is not unique in set of possible{probable} trains (for example, several employees can have a same manager), the procedure described above all the same will lead to to repeated calculation of a subrequest for each ocurrence of the duplicated value. However, if the attitude{relation} on which the link conducts, is ordered on stolbcu to which the subrequest repeated calculation can be conditional depending on check refers, whether the current value same, as at the previous possible{probable} train is. If they are identical, the previous calculated value can be used again. In some cases can make sense even specially to sort the external attitude{relation} on corresponding stolbcu, to avoid excessive calculation of a subrequest. To define{determine}, whether values of a column to which the correlation search refers are unique, OPTIMIZATOR can use helps such as ncard> icard, where ncard - capacity of the attitude{relation}, and icard - capacity of an index on it stolbce.

7. The conclusion

The choice of ways of access in system r for searches above single tables, connections and the enclosed searches is described. Job in comparison of chosen variants with "correct" is conducted, results will be described in future clause{article}. Preliminary results show, that though to cost, predskazyvaemye optimizatorom, often are inexact in absolute measurement, in overwhelming majority of cases really optimum plan gets out. In many cases of a parity{ratio} between stoimostjami for all considered plans parities{ratio} between really measured stoimostjami are appreciated in accuracy the same, as.

Besides cost of a choice of a way is not unreasonable. For connection of two attitudes{relations} cost of optimization is approximately equivalent to cost 5-20 vyborok from a database. This number becomes even more insignificant when the choice of a way of access is made in the environment similar system r where once compiled applied programs are carried out repeatedly. Cost of optimization pays off repeated performance.

The basic new contribution of the given job above a component of a choice of ways of access will consist in expanded use of statistics (for example, capacities of indexes), inclusion of loading of the CENTRAL PROCESSING UNIT in estimated formulas and development of a method of definition about connections. Many searches are limited to opportunities of the CENTRAL PROCESSING UNIT, in particular, connections by merge for which time attitudes{relations} are created and sorting is made. The concept of " factor of selectivity " allows optimizatoru to use as the greater number of predicates in arguments of search rss and ways of access is possible. At storing classes of equivalence of " interesting ordering " for connections and specifications order or group optimizator takes into account more information, than other similar programs, but in many cases this additional job allows to avoid preservation and sorting of intermediate results of search. Methods of trimming of a tree and search in a tree allow to carry out this additional account effectively. Additional job on validacii estimated formulas optimizatora is required, but from our preliminary job we can conclude, that in control systems of databases not procedural languages of searches with the efficiency comparable to what is supported for modern more procedural languages can be supported.