The paper is devoted to the research of algorithms implementing intersection, union and difference in tables and multitables. The subject of the work is relevant, since despite the importance and applicability of set-theoretical operations in relational databases, for some reason, the attention of researchers was focused on optimizing other table operations, first of all, the join. Meanwhile, the optimal implementation of set-theoretical operations will lead to a faster execution of the query, which containing at least one of set-theoretical operations, and will significantly reduce the time of processing information in the database management systems. For each set-theoretical operation algorithms that implement them on tables, in which strings are not repeat, and on multi-tables, in which the strings can be repeated, are considered. After that the modifications of the basic algorithms, that we found, which allow to significantly reduce the number of computations are considered. As an average case, we understand the most general case in which the domain of each attribute of the table schema is fixed and known above, and the distribution of values for each attribute in each table is uniform. For each of the six cases (three table operations and three multi-table operations), the fastest algorithms by this criterion were found. For all 6 algorithms considered on the tables (basic and fastest modifications of the basic ones) we found exact complexity on average. The found formulas defining the complexity of the proposed algorithms do not contain O-asymptotics. For the experimental confirmation of the results we developed the software system, which, for tables with given parameters, finds the actual number of computations performed for each of the proposed algorithms. The experiments carried out confirmed the theoretical estimates found for the tables and identified the fastest algorithms for the multitables. The results of the work can be used both in relational databases theory and in practice in queries optimization and to reduce the processing time in database management systems.
Guide on data models in the selection and use of database management systems
