US5930785A - Method for detecting and optimizing queries with encoding/decoding tables - Google Patents

Method for detecting and optimizing queries with encoding/decoding tables Download PDF

Info

Publication number
US5930785A
US5930785A US08950674 US95067497A US5930785A US 5930785 A US5930785 A US 5930785A US 08950674 US08950674 US 08950674 US 95067497 A US95067497 A US 95067497A US 5930785 A US5930785 A US 5930785A
Authority
US
Grant status
Grant
Patent type
Prior art keywords
tables
table
join
relations
query
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Expired - Lifetime
Application number
US08950674
Inventor
Guy M. Lohman
Bernhard Schiefer
Monica S. Urata
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
International Business Machines Corp
Original Assignee
International Business Machines Corp
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Grant date

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30442Query optimisation
    • G06F17/30448Query rewriting and transformation
    • G06F17/30463Plan optimisation
    • G06F17/30466Join order optimisation
    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F19/00Digital computing or data processing equipment or methods, specially adapted for specific applications
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • Y10S707/99932Access augmentation or optimizing
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • Y10S707/99933Query processing, i.e. searching
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • Y10S707/99933Query processing, i.e. searching
    • Y10S707/99934Query formulation, input preparation, or translation

Abstract

A join optimizer and method for a relational database management system including a data processor, a stored database, and a plurality of database relations, wherein one or more of the relations are retrieved by the processor by means of query commands by performing a plurality of join operations on the relations, the system employing a general purpose heuristic algorithm which excludes or defers Cartesian products as late in the join sequence as possible, the method includes the steps of determining, in association with the execution of, or preferably prior to executing the general purpose algorithm, whether tables referenced in a query command includes a hub table and at least two encoding tables related to the hub table and, when the query command references a hub table and at least two encoding tables, determining the best access plan for the hub table, determining whether the best access plan utilizes an index used to access the hub table and, if so, constructing a plan to join the encoding tables as Cartesian products, constructing a plan to join the hub table and the encoding tables and storing the plans in the data structures of the optimizer for enumeration with other access plans constructed by the optimizer.

Description

This is a continuation of application Ser. No. 08/414,835 filed on Mar. 31, 1995 now abandoned."

The present invention relates relational database management systems for use with data processors and. more particularly, toward optimizing the computer implemented process of retrieving data from databases.

BACKGROUND OF THE INVENTION

Large amounts of data are now typically stored in computer database systems using database management system software to access the data. A relational database management system logically organizes the data into relations. A relation can be thought of as a table where each row is a tuple and each column is a component of the relation designating an attribute. Data can be extracted by querying the database for all tuples in a relation that meet certain criteria using a query statement having the general form:

SELECT <Operation Specification >

FROM <List of Tables Referenced >

WHERE <Boolean Predicate Expression >

The following example query statement on an EMPLOYEE and DEPARTMENT database requests the names, salaries, managers of employees and the location of their departments for those employees who earn between 10000 and 20000 and work in departments on the fifth floor:

SELECT Name, Salary, Manager, Location

FROM EMPLOYEE, DEPARTMENT

WHERE Employee.DNO=Department.DNO and Salary between 10000 and 20000 and Floor=5

The system performs a join operation to connect data from two or more relations whereby tuples with matching attributes are joined to form a new tuple. A join order is the order in which join operations are performed. A join method is a technique which for performing the join of two relations.

A query language provides a set of commands, such as that shown above, for storing, retrieving and deleting data. The language for relational database management systems do not require the user to specify the access path to be used to retrieve the information or to specify the order in which joins are to be performed. Rather, the relational database management system is provided with a program called an optimizer which chooses both join order and an access path for each table in the query statement. Of the many possible choices, the optimizer chooses the access path which minimizes the "total access cost" for performing the entire statement. In the example above, the system may choose to retrieve an EMPLOYEE tuple and find all matching DEPARTMENT tuples or retrieve a DEPARTMENT tuple and find all matching EMPLOYEE tuples or to scan two order tables simultaneously. Additional information about query statements, query optimization, cost evaluation and enumeration may be obtained from "Access Path Selection in a Relational Database Management System", P. G. Selinger et al, Proceedings of ACM SIGMOD Conference, May, 1979 and "Measuring the Complexity of Join Enumeration in Query Optimization", Ono and G. M. Lohman, Proceedings of Sixteenth International Conference on Very Large Data Bases, September 1990, Brisbane, Australia, pp. 314-324, both of which are incorporated herein by reference.

Thus, query optimizers are sophisticated, highly developed systems used in relational database management systems to translate non-procedural queries into a procedural plan for execution. As mentioned, optimizers do so by developing many alternative plans, estimating the cost of execution of each plan and choosing the plan having the lowest estimated cost. Increasing the set of feasible plans that the optimizer evaluates improves, but does not guarantee, the chances that the optimizer will find a better plan while increasing the cost for it to optimize the query.

One of the major decisions an optimizer must make is with respect to the order or sequence in which to join the tables referenced in a query. In most systems, the join operation is implemented as a diadic (2-way) operator. Thus, the optimizer must generate plans that achieve N-way joins as a sequence of 2-way joins. When joining more than a few tables, the number of possible sequences is the dominant factor in the number of alternative plans: N| different sequences are possible for joining N tables. Even when dynamic programming is used, as most systems do, theoreticians have used the exponential worst case complexity to argue that heuristic search methods should be used. However, these search methods cannot guarantee optimality of their solution, as can dynamic programming.

For this reason, many existing optimizers use heuristics within dynamic programming to limit the number of join sequences evaluated. Some systems exclude plans which exclude so-called composite inners in which the inner table (the second operand of a join) is a result which must be materialized in memory or, if it is too big, on disk. While the heuristic avoids this materialization, it may exclude better plans for certain queries. Some systems restrict the class of predicates which qualify to those which are of certain form and some do not derive implied predicates, i.e. predicates which are not specified but which are implied by the query given by the user. Some systems either exclude or always defer Cartesian products as late in the join sequence as possible on the assumption that they would result in large intermediate tables. That is because, by definition, there is no join predicate between Cartesian products that restricts the results and, hence, every row in one table is joined with every row in the other table. This avoids the need for the query optimizer to search many join orderings that are unlikely to be very good since, in general, Cartesian products increase rather than decrease the size of intermediate results and, thus, cause more work to be done later. Again, such heuristics may exclude the optimal plan for certain queries that can benefit from Cartesian products. For instance, if the tables to be joined are small, and especially where they contain one tuple each, a Cartesian product is quite inexpensive and its result may have columns forming a composite key for another, much larger table to be accessed later, thus making the Cartesian product more advantageous. Obviously, these exclusions and limitations are beneficial in most cases.

Database designers often encode wide columns in a large table and store the encodings for each such column in a separate table. For example, the large table might have one column for storing a two-letter abbreviation for a state and another table for storing all of the possible abbreviations of the states and their respected unabbreviated forms; another column for storing country abbreviations and another encoding table for storing all possible country abbreviations and their unabbreviated forms; and so on. There are no join predicates relating the state encoding table and the country encoding table. Since there are no join predicates relating any of the encoding tables to each other, joining the encoding tables to each other is not considered by most join enumerators, since to the general-purpose heuristic excludes or defers Cartesian products as discussed above.

Thus, when there are multi-column indexes on the encoded columns and a query has limiting predicates on the encoding tables, the general-purpose heuristic is actually counter-productive, because it will not consider the best execution strategy, which is to first join the encoding tables as Cartesian products, and then use the rows so formed to access the data table using the multi-column index and the join predicates as start/stop key conditions.

SUMMARY OF THE INVENTION

The present invention seeks to better optimize queries that reference encoding/decoding tables and similar situations. In accordance with the present invention, the optimizer first determines whether such a situation exists, identifies the large or main table (later referred to as a "hub" table) and its encoding tables (later called "spokes" or "spoke tables"), and then evaluates the cost of the plan that overrides the usual deferral of Cartesian products by first joining each of the encoding tables with Cartesian products, before proceeding with the normal query optimization. In this way, the resulting plan competes based upon its estimated cost with all other possible plans (recited as "first plans") that the optimizer considers when determining the optimal plan. The present invention is easily added to most existing query optimizers by being performed before the rest of optimization, and by re-using pieces of the standard query optimization system.

One aspect of the present invention provides an improved query optimizer used for a relational database management system including a data processor, a stored database, and a plurality of database relations, wherein one or more of the relations are retrieved by the processor by means of query commands performing a plurality of join operations on the relations, an improved optimizing module for use in optimizing query commands comprising means for determining whether at least one table referenced in a query statement is a hub table, means responsive to identification of a hub table for constructing a plan ("recited as a second plan") for joining the hub table and associated spoke tables; means for generating a plan ("recited as a third plan") for joining all tables referenced in the query statement; and means for enumerating all of the plans (i.e., the first, second, and third plans) to determine the best plan for joining the tables referenced in the query statement.

Another aspect of the present invention provides an improved method of optimizing query statements in relational database management systems including a data processor, a stored database, and a plurality of database relations, wherein one or more of the relations are retrieved by the processor by means of query commands by performing a plurality of join operations on the relations, the system having an optimizer for optimizing query commands and which employs a general purpose heuristic algorithm which excludes or defers Cartesian products as late in the join sequence as possible, the improvement comprising the steps of, in association with the execution of, or preferably prior to executing the general purpose algorithm, determining whether tables referenced in a query command includes a hub table and at least two encoding tables related to the hub table, when the query command references a hub table and at least two encoding tables, determining the best access plan for the hub table, determining whether the best access plan utilizes an index used to access the hub table and, if so, constructing a plan to join the encoding tables as Cartesian products, constructing a plan to join the hub table and the encoding tables and storing the plans in the data structures of the optimizer for enumeration with other access plans constructed by the optimizer.

BRIEF DESCRIPTION OF THE DRAWINGS

These and other features of the invention will become more apparent from the following description in which reference is made to the appended drawings wherein:

FIG. 1 diagrammatically illustrates a hub table T0 and four spoke tables T1, T2, T3 and T4;

FIG. 2 is a graphical view of the tables illustrated in FIG. 1;

FIG. 3 is an overview of the flow of the major modules or steps according to the preferred embodiment of the optimizer of the present invention;

FIGS. 4a and 4b are flow diagrams illustrating the first major component or step according to the preferred embodiment of the optimizer of the present invention;

FIG. 5 is a flow diagram illustrating the second and third major components or steps according to the preferred embodiment of the optimizer of the present invention;

FIG. 6 is a view similar to that shown in FIG. 1 for use in illustrating a specific example of the optimization of a query statement in accordance with the preferred embodiment of the present invention;

FIG. 7 illustrates an operator tree constructed by joining all spoke tables by the component illustrated in FIG. 5; and

FIG. 8 illustrates an operator tree constructed by the component illustrated in FIG. 5 by joining the hub table with the operator tree illustrated in FIG. 6.

DESCRIPTION OF PREFERRED EMBODIMENT

FIG. 1 illustrates the situations of interest of the present invention. A large table, T0, has a multi-column index on columns C1, C2, C3, . . . , Cn and join predicates P1, P2, P3, . . . , Pn on each of these columns to tables T1, T2, T3, . . . , Tn, respectively. If a graph was created where the nodes were the tables and the arcs were the predicates between the tables, table T0 would be in the center, like the hub of a wheel, the predicates P1, . . . , Pn would radiate outwardly like spokes of the wheel with encoding tables T1, . . . , Tn at the ends, but there would be no rim connecting each spoke to each other. FIG. 2 illustrates the graph. Table T0 will be referred to as the "hub table" and each of the encoding tables T1, . . . , Tn will be referred to as a "spoke table".

In a query to hub table T0, the decoded value must be found for each encoded value in a column Cj of the hub table by joining each column Cj with the encoding column of its corresponding spoke table Tj. For example, table T0 might have a column called "STATE-- CODE" for storing a two-letter abbreviation for a state or province. The encoding table for this column, say T1, would have two columns: one column with the possible values that STATE-- CODE can take on, and the other column called STATE-- FULL with the corresponding unabbreviated value. One such row would have the values "CA" and "California" and another would have the values "AL" and "Alaska". Any query having a predicate on the unabbreviated value would have to translate the predicate into a predicate on the value of STATE-- CODE by adding a join predicate between large table T0 and encoding table T1, i.e.

WHERE T1.STATE-FULL=`CALIFORNIA` AND TO.STATE-CODE=T1.STATE-CODE

Since there are no join predicates relating any of the encoding tables to each other, joining the encoding tables to each other is not considered by most join enumerators, due to the general-purpose heuristic deferring Cartesian products. However, when there are multi-column indexes on the encoded columns of hub table T0 and the query has limiting predicates on the encoding tables, this general-purpose heuristic is actually counter-productive because it will not consider the best execution strategy which is to first join the encoding tables as Cartesian products and then use the rows so formed to access the data table T0 using the multi-column index and the join predicates as start/stop key conditions.

In accordance with the present invention, the optimizer first determines whether such a situation exists, identifies the hub table and its spoke tables and then evaluates the cost of the plan that overrides the usual deferral of Cartesian products by joining each of the encoding tables with Cartesian products before proceeding with the normal query optimization. In this way, the plan produced by the present invention competes based on its estimated cost with all other plans that the optimizer considers when determining the optimal plan. The present invention can be incorporated into existing optimizers or it may be implemented as a separate utility which is called by existing optimizer at the appropriate point before the rest of the normal optimization routines. In the following description, the term optimizer or main optimizer refers to existing optimizers and the term module refers to the utility of the present invention.

FIG. 3 illustrates the three major components or steps of the module of the present invention. At the appropriate point in the routine of the main optimizer before the normal optimization routines are executed, the main optimizer invokes the present invention. The first component 10 determines whether the tables referenced in the query statement include a hub table as defined above. FIGS. 4a and 4b, described below, illustrate the various steps of component 10. If component 10 fails to identify a hub table, process control returns to the main optimizer and normal optimization processing is carried out. If on the other hand, component 10 succeeds in identifying a hub table, processing proceeds to component 12. Component 12 constructs and stores a plan to join the spoke tables as Cartesian products. Component 14 then constructs a plan to join the hub table with the Cartesian product constructed by component 12 and stores this plan together with the final and intermediate plans constructed by component 12 in the data structures of the main enumerator module (not shown). Processing then to the main optimizer which then proceeds with normal optimization.

Component 10 will now be described with referenced to FIGS. 4a and 4b. Step 20 counts the number of tables referenced in the FROM portion of query statement prepared by the user and stores the number in a variable N. Step 22 then compares the value of N is greater than 2. The situation of interest must have three or more tables because, if there are only two tables, there will not be two encoding tables which would not have a join predicate between them. Thus, if there are at less then three tables, the module returns to the main optimizer at 24.

However, if there are at least three tables to join, steps 26, 28, 30, 32, 34 and 36 loop through each of the tables in turn in an effort to identify a table having more join predicates than any of the other tables. In step 26, initializes a counter k, and variables TableID and MaxCount. TableID is used to store the identity of the current hub table candidate and MaxCount stores the largest number of join predicates. Step 26 counts the number of distinct other tables that are joined by join predicates to the current table. The number is stored in a variable count. These other tables are potential spoke tables. Step 30 compares the value of count with the value of MaxCount. If Count is larger it replaces the value of MaxCount with the value of Count, increments the value of k at 36 and then loops back to step 28. In the first iteration, the name of the first table is stored in TableID and the count for the first table will be stored in MaxCount. When the value of k exceeds the value of N, the module breaks out of the loop at step 36 and proceeds to step 38 in FIG. 4b.

Another characteristic of the encoding/decoding or hub/spoke table situation is that there is only one hub table and, thus, there will be only one table with the largest number of spoke tables. Accordingly, when all tables have been considered, the optimizer determines at step 38 whether there is more than one table with the largest number of spoke tables. If so, then control returns to the main optimizer. However, this step is optional. If there are two or more candidate hub tables with the same, largest number of candidate spoke tables, each may be analyzed in the manner described below if desired.

In either case, the module temporarily considers the join predicates of the candidate hub table as local predicates by calling "predicate push-down" routine at 42. "Predicate push-down" of join predicates routines are well known in the art and, accordingly, are not described in detail herein. The module then determines at step 44 the best access plan for the table when all of the local predicates plus the pushed-down join predicates of the candidate table are applied. Best Access Plan routines are well known in the art and therefore need not be described herein. The main optimizer already incorporates such a routine and the routine can be called from this module of the present invention.

Step 46 determines whether the winning access plan utilizes an index, the candidate table is identified as a hub table at step 50 and processing proceeds to the next major component. Otherwise, control reverts at 48 to the main optimizer.

Once a hub table has been identified, the module of the present invention constructs a plan to join the spoke tables as a Cartesian product at step 60. More specifically, step 60 constructs a plan that will join the spoke tables that have join predicates, such as equality join predicates with columns of the index by any desired algorithm. A preferred algorithm is known as the Greedy algorithm. Step 62 then constructs a plan to join the hub table with the final Cartesian product developed at step 60. Step 64 stores the plans for all intermediate results as well as the plan for the final result. FIGS. 7 and 8, described later with reference to an example query statement, illustrate such plans.

Finally, at step 66 the module moves all of the plans for intermediate and final results to the data structures of main join enumeration routine so that the intermediate and final results are available options for the main optimizer when enumerating plans. Steps 64 and 66 can be combined, and may be done during the execution of steps 60 and 62. At step 68, control reverts to the main optimizer which then proceeds to enumerate all of the plans with any join enumeration method desired, allowing the plans for the intermediate results and for the final result to compete with similar plans produced by this step on the basis of cost.

When looking for spoke tables connected to the hub table candidate through a join predicate, the optimizer may be designed to count a table only if the join predicate is on a column that no other spoke table has a join predicate on thus far. Having more than one table joined to the same hub table column does not add value. The purpose of creating the join of the spoke tables is to allow the join between the hub table and the spoke tables so that the join predicates may be used as start/stop conditions when accessing the hub table. Unless multiple join predicates on the same column can be combined in a start/stop condition, including more than one spoke table joined to the same hub table column is not beneficial. Cost is also incurred to join the unnecessary table to the others.

EXAMPLE

Reference will now be made to FIG. 6 which illustrates five tables of a relational database management system. The database includes a table T0 called PEOPLE in which each row stores statistical information about one person and in which the first four columns store state, job category, education and race codes, the decoded versions of which are stored in encoding tables T1, T2, T3 and T4, and four additional columns for storing name, salary, age and address. Table T0 includes one index called "super-index" which is a concatenation of the data in the first four columns, namely, state-- code, job-- code, edu-- code and race-- code. Table T1, called STATES, is an encoding table which stores the codes and names of states and is related to table T0 by the join predicate T0.C1=T1.C1. Table T2, called JOB-- CATEGORY, stores job category codes and descriptions and is related to Table T0 by join predicate T0.C2=T2.C1. Table T3, called EDUCATION, stores education codes and descriptions and is related to Table T0 by join predicate T0.C3=T3.C1 and Table T4, called RACE, stores race codes and descriptions and is related to Table T0 by join predicate T0.C4=T4.C1.

Assume that a user wishes to extract the salary and age of all Hispanics with a doctorate degree and in medical jobs in California. An appropriate query statement is as follows:

______________________________________                     Line                     Number______________________________________SELECT  salary, age             1FROM    people, states, job.sub.-- category, education,                           2aceWHERE   race.sub.-- full=`Hispanic`                           3AND     job.sub.-- full=`Medical`                           4AND     state.sub.-- full=`California`                           5AND     edu.sub.-- full=`Doctorate`                           6AND     people.state.sub.-- code=states.state.sub.-- code                           7AND     people.job.sub.-- code=job.sub.-- category.job.sub.-- code                           8AND     people.edu.sub.-- code=education.edu.sub.-- code                           9AND     people.race.sub.-- code=race.race.sub.-- code                           10______________________________________

Lines 3-6 specify "Local predicates" because they are "local" to a single table while lines 7-10 specify "Join predicates" as they "join" more than one table. It will be noted that there are no join predicates linking directly any of the encoding tables, namely, STATES, JOB-- CATEGORY, EDUCATION, RACE. Therefore, without the method described in this invention, a join between those tables BEFORE accessing table PEOPLE would not be considered by any existing optimizers.

The module of the present invention first determines whether there are at least three tables to join. The module determines from line 2 of the query statement there are five tables to access. Therefore, the subject query meets this test.

The module then loops through each of the tables to determine the number of other tables joined to it by join predicates. The module determines from lines 7-10 of the query statement that there are four tables joined to the table PEOPLE and one table is joined to each of the other four tables. At the end of each loop, the module compares the count of the current table with the largest count thusfar determined and stores the largest count together with the identity of the table having that count. In the present example, the module will determine that the table with the largest number of tables joined to it is PEOPLE and the count is 4.

The next step is to determine whether there are any two or more tables with the same number of joins. In the example, there are none. If there were, one option would be to abandon the balance of the routine and proceed with the normal general purpose heuristic. Another option is to evaluate all of the tables which had the largest number of tables joined to it.

The next step is to assume that the join predicates are local predicates. This means that State.State.Code, Job-- Category. Job.Code, Job-- Education. Edu-- Code, and Race.Race.Code are fixed. This is a well-known technique in the current art called "join predicate push-down", in which the join predicates can be applied while accessing the inner table of a nested-loop join because that access is done once per row of the outer table, during which time the values from the outer column are fixed. Thus, for example, considering the join of STATES with PEOPLE using STATES as outer table and PEOPLE as inner table. Then, the predicate on line 7 will, for each row of STATES, have a fixed value of States.State-- Code so that it can be applied as if it were a local predicate on PEOPLE. It will appear to the access of PEOPLE as:

fixed-value-for-STATES.STATE.sub.-- CODE=PEOPLE.STATE.sub.-- CODE

The next step involves determining the best access plan for the table when all of the local predicates plus the push-down join predicates are applied. This involves estimating the cost of accessing the table PEOPLE when predicates in lines 3-6 and 7-10 are applied. This is done using the existing state of the art optimizer components, as follows:

The hub table may have many indexes. The present example has only one index, named "super-index". If there were more than one index, the module would iterate through all indexes. For each index, the module performs two tasks. First, for each column of the index, the module determines the join predicates which link that column to another table. The module assumes that those join predicates can be "pushed down" on the access of PEOPLE. The join predicates are:

STATE-- CODE: join predicate on line 7

JOB-- CODE: join predicate on line 8

EDU-- CODE: join predicate on line 9

RACE-- CODE: join predicate on line 10

Second, with these predicates applied as local predicates, the index "super-index" is used to go directly to those rows that satisfy predicates in lines 7-10 of the query statement. The disk pages needed to access these rows are typically three pages in the index to get to the key values of interest, and only a handful of index pages containing the keys of interest, plus another handful of pages to access the corresponding rows (via the row identifier in the index) on the date pages to extract the information needed in the SELECT list (line 1 of the query statement) that is not in the "super-index" key values.

The only other alternative is to sequentially scan PEOPLE by applying predicates 3-10 as during the scan. Except for very small tables, this is much more expensive inasmuch as it is necessary to access every page in PEOPLE. Therefore, the winning plan uses an index and PEOPLE is the hub table with the most potential spoke tables.

As already noted, one variation of the preferred embodiment would relax the requirement that there be no ties for candidate hub tables. It would instead apply all steps described above for each of the potential hub tables which tied for having the most potential spoke tables connected to them by join predicates.

Continuing to the next step, as previously mentioned, the usual implementation of join algorithms joins only two tables at a time. Much of the computational complexity of the optimizer is attributable to assessing the cost of plans with various permutations of the ordering of N tables, joined two at a time. The usual heuristic is to avoid Cartesian products, i.e. those pairs for which there is no join predicate to reduce the result size somewhat.

In the present invention, the next step is to compute the cost of constructing a Cartesian product of the spoke tables first using state-of-the-art optimization techniques. The spoke tables are any potential spoke tables whose columns have a join predicate that references a column in the index used to access the hub table. In this example, all four potential spoke tables individually have a join predicate that references a column in "super-index", so all are spoke tables. If, however, "super-index" did not exist, and there was only an index named "3col" on columns RACE-- CODE, JOB-- CODE, and EDU-- CODE and that index was the cheapest way to access PEOPLE, then only RACE, JOB-- CATEGORY, and EDUCATION would be spoke tables.

Returning to the plan using "super-index", suppose that the spoke tables had the following number of rows after applying their local predicates (whose line number is given):

______________________________________           Local Predicate                      RemainingTable           Line Number                      Rows______________________________________STATES          5          1JOB.sub.-- CATEGORY           4          1EDUCATION       6          1RACE            3          1______________________________________

Since the size of each table, when reduced by local predicates, is only one row in this case, the order of forming the Cartesian products of these tables does not matter. If the size differed, however, most general purpose heuristics order them in increasing size. Thus, the plan to join all the tables appears like the operator tree shown in FIG. 7, where CP stands for Cartesian product.

It will be seen that there are three Cartesian products. The first Cartesian product joins tables STATES and JOB-- CATEGORY, the second Cartesian product joins the first Cartesian product and table EDUCATION and the third Cartesian product joins the second Cartesian product and table RACE. The result of each Cartesian product operation is called an "intermediate result", regardless whether the results of the operation are ever materialized in one place, such as, for example, putting them to a temporary table. The module of the present invention saves those plans and their estimated cost of execution for consideration by the main optimizer.

The module also constructs the plan that joins the operator tree shown in FIG. 7 with the hub table to provide a plan that will produce the final result of the query as shown as an operator tree in FIG. 8. The estimated cost of this plan is determined using existing state of the art techniques and saved together with all the intermediate plans used in its construction.

The above described steps merely generated a plan that otherwise would not be generated by general purpose heuristics. There is no guarantee it is the best plan, although often it is. The next step then is to construct the other plans that would normally be constructed and evaluated in state-of-the-art optimizers and lets those plans compete against the plans constructed in by the module of the present invention. The plan for the final result whose estimated cost is least will be chosen by the optimizer to be executed to satisfy the query.

While the preferred embodiment of the present invention has been illustrated in detail, it should be apparent that various modifications and alterations may occur to one skilled in the art without departing from the scope of the present invention as set forth in the following claims.

Claims (15)

The embodiments of the invention in which an exclusive property of privilege is claimed are defined as follows:
1. In a relational database management system including a data processor, a stored database, and a plurality of database relations stored in the form of tables, wherein one or more of said relations are retrieved by the processor responsive to a query statement which specifies desired relations, the query statement including first, second, and third tables, and further including a join predicate between relations of the first and second tables and a join predicate between relations of the first and third tables, but not including a join predicate between the relations of the first and third tables, the system producing first plans for performing a plurality of join operations on the desired relations, an optimizing module for use in optimizing query commands, the optimizing module comprising:
means for determining that the first table referenced in the query statement is a hub table, and for determining that the second and third tables are spoke tables associated with the hub table because of the respective join predicates therebetween;
means, operable responsive to identification of the hub table, for constructing a second plan for joining the hub table and the associated spoke tables;
means for generating a third plan for joining the second and third tables of the desired relations referenced in said query statement; and
means for enumerating the first, second, and third plans to determine the best plan for joining said tables referenced in said query statement.
2. A query optimizer as defined in claim 1, said determining means further including:
means for determining the identity of the table in said query statement having the largest number of distinct tables of the tables in said query statement joined to it by a join predicate and the magnitude of said largest number;
means for determining the best access plan for said table having the largest number of tables which are joined to it; and
means for determining whether said best access plan utilizes an index.
3. In a query optimizer as defined in claim 1, said determining means further including:
means for determining whether said query statement includes more than two tables and, if so,
means for counting the number of distinct tables of each of the tables referenced in said query statement which are joined by join predicates to the other tables in said query statement; and
means for storing the identity of the table having the largest number of tables which are joined to it and the magnitude of said largest number.
4. A query optimizer as defined in claim 3, said determining means further including:
means for determining the best access plan for said table having the largest number of tables which are joined to it when all local predicates and push-down predicates are applied; and
means for determining whether said table having the largest number of tables which are joined to it utilizes an index and, if so, designating said table having the largest number of tables which are joined to it as a hub table;
join predicate push-down means for applying join predicates between said tables as local predicates.
5. A query optimizer as defined in claim 1, said determining means further including:
means for determining whether said query statement includes more than two tables and, if so,
means for counting the number of distinct tables of each of the tables referenced in said query statement which are joined by join predicates to the other tables in said query statement; and
means for storing the identity of the table having the largest number of tables which are joined to it and the magnitude of said largest number;
means for determining the best access plan for said table having the largest number of tables which are joined to it when all local predicates and push-down predicates are applied; and
means for determining whether said table having the largest number of tables which are joined to it utilizes an index and, if so, designating said table having the largest number of tables which are joined to it as a hub table.
6. In a query optimizer as defined in claim 1, said responsive means further including:
means for designating said table having the largest number of tables which are joined to it as a hub table and designating tables joined to said hub table by join predicates in said query statement as spoke tables;
means for computing the cost of constructing a Cartesian product of said spoke tables for each index of said hub table; and
means for storing each said plan for joining and all intermediate plans.
7. For use by a relational database management system, the system including a data processor, a stored database, and a plurality of database relations, wherein one or more of said relations are retrieved by the processor by means of a query command, the query command including first, second, and third tables, and further including a join predicate between relations of the first and second tables and a join predicate between relations of the first and third tables, but not including a join predicate between the relations of the first and third tables, by performing a plurality of join operations on said relations, the system further including an optimizer for optimizing the query command, the optimizer employing a general purpose heuristic algorithm which excludes or defers Cartesian products as late in the join sequence as possible, a method comprising the steps of:
in association with the execution of said general purpose algorithm, determining that the first table referenced in the query command includes a hub table, and for determining that the second and third tables are related to said hub table as encoding tables because of the respective join predicates therebetween; and
responsive to the query command referencing a hub table and at least two encoding tables, performing the steps of:
(i) determining the best access plan for said hub table,
(ii) determining whether said best access plan utilizes an index used to access said hub table,
(iii) if so, constructing a plan to join said encoding tables as Cartesian products,
(iv) constructing a plan to join said hub table and said encoding tables, and
(v) storing said plans in the data structures of said optimizer for enumeration with other access plans constructed by said optimizer.
8. A method as defined in claim 7, said step of determining whether tables referenced in said query command include a hub table and at least two encoding tables related to said hub table further including the step of, for each table referenced in said query command, counting the number of tables which are joined to it by join predicates and storing the identity of the table having the largest number of tables joined to it and the magnitude of said largest number.
9. A method as defined in claim 8, further including the step of applying a predicate push-down algorithm with respect to said join predicates prior to determining said best access plan.
10. A method as defined in claim 9, further including the steps of:
for each index of said hub table, for each column of the index determining the join predicates which link said column to one of said encoding tables; and
applying said join predicates as local predicates, using said column of said index to access those rows of said table which satisfy join predicates in query statement.
11. For use by a relational database management system including a data processor, a stored database, and a plurality of database relations, wherein one or more of said relations are retrieved by the processor by means of a query command, the query command including first, second, and third tables, and further including a join predicate between relations of the first and second tables and a join predicate between relations of the first and third tables, but not including a join predicate between the relations of the first and third tables, by performing a plurality of join operations on said relations, the system further including an optimizer for optimizing the query command, the optimizer employing a general purpose heuristic algorithm which excludes or defers Cartesian products as late in the join sequence as possible, a method comprising the steps of:
in association with the execution of said general purpose algorithm, determining that the first table referenced in the query command includes a hub table and for determining that the second and third tables are related to said hub table as encoding tables because of the respective join predicates therebetween, the step of determining including:
(i) the step, executed for each table referenced in said query command, of counting the number of tables which are joined to the referenced table by join predicates, and
(ii) the step of storing the identity of the table having the largest number of tables joined to it and the magnitude of said largest number;
responsive to the query command referencing a hub table and at least two encoding tables,
(i) determining the best access plan for said hub table,
(ii) determining whether said best access plan utilizes an index used to access said hub table and, if so:
(a) for each index of said hub table and for each column of the index, determining the join predicates which link said column to one of said encoding tables, and applying said join predicates as local predicates, using said column of said index, to access those rows of said table which satisfy the join predicates in the query statement;
(b) constructing a plan to join said encoding tables as Cartesian products;
(c) constructing a plan to join said hub table and said encoding tables; and
(d) storing said plans in the data structures of said optimizer for enumeration with other access plans constructed by said optimizer.
12. For use by a relational database management system, the system including a data processor, a stored database, and a plurality of database relations, wherein one or more of said relations are retrieved by the processor by means of a query command, the query command including first, second, and third tables, and further including a join predicate between relations of the first and second tables and a join predicate between relations of the first and third tables, but not including a join predicate between the relations of the first and third tables, by performing a plurality of join operations on said relations, the system further including an optimizer for optimizing query commands and which employs a general purpose heuristic algorithm which excludes or defers Cartesian products as late in the join sequence as possible, the improvement comprising the steps of:
in association with the execution of said general purpose algorithm, determining whether the first table specified in the query command includes a hub table, and further determining whether the second and third tables specified in the query command include two tables related to the hub table as encoding tables because of the respective join predicates therebetween; and
when said tables include two or more encoding tables, executing the steps of:
(i) determining a best access plan for said table related to two or more encoding tables when said tables includes two or more encoding tables;
(ii) determining whether said best access plan employs an index used to access said table related to two or more encoding tables; and
(iii) if so, executing the steps of:
(a) constructing a plan to join said encoding tables as a first Cartesian product;
(b) constructing a plan to join said table related to two encoding tables; and
(c) storing each said plan in the data structures of said optimizer for enumeration with other plans constructed by said optimizer.
13. A relational database management system as in claim 7, wherein the step of determining whether tables referenced in a query command includes a hub table and at least two encoding tables related to said hub table related to said hub table is carried out prior to executing said general purpose algorithm.
14. A relational database management system as in claim 11, wherein the step of determining whether tables referenced in a query command includes a hub table and at least two encoding tables related to said hub table related to said hub table is carried out prior to executing said general purpose algorithm.
15. A relational database management system as in claim 12, wherein the step of determining whether tables referenced in a query command includes a hub table and at least two encoding tables related to said hub table related to said hub table is carried out prior to executing said general purpose algorithm 1.
US08950674 1995-03-31 1997-10-16 Method for detecting and optimizing queries with encoding/decoding tables Expired - Lifetime US5930785A (en)

Priority Applications (2)

Application Number Priority Date Filing Date Title
US41483595 true 1995-03-31 1995-03-31
US08950674 US5930785A (en) 1995-03-31 1997-10-16 Method for detecting and optimizing queries with encoding/decoding tables

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
US08950674 US5930785A (en) 1995-03-31 1997-10-16 Method for detecting and optimizing queries with encoding/decoding tables

Related Parent Applications (1)

Application Number Title Priority Date Filing Date
US41483595 Continuation 1995-03-31 1995-03-31

Publications (1)

Publication Number Publication Date
US5930785A true US5930785A (en) 1999-07-27

Family

ID=23643174

Family Applications (1)

Application Number Title Priority Date Filing Date
US08950674 Expired - Lifetime US5930785A (en) 1995-03-31 1997-10-16 Method for detecting and optimizing queries with encoding/decoding tables

Country Status (2)

Country Link
US (1) US5930785A (en)
CA (1) CA2168287C (en)

Cited By (54)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6134540A (en) * 1997-05-09 2000-10-17 International Business Machines Corporation System, method, and program for applying query rewrite technology to object building
WO2000063798A1 (en) * 1999-04-16 2000-10-26 Targit A/S A method and an apparatus for the processing of queries to a database
WO2001046864A1 (en) * 1999-12-23 2001-06-28 Bull Hn Information Systems Inc. Method and data processing system for detecting patterns in sql to allow optimized use of multi-column indexes
WO2002010986A1 (en) * 2000-07-28 2002-02-07 Powerway, Inc. System and method for supply chain product and process development collaboration
US6351742B1 (en) * 1999-03-18 2002-02-26 Oracle Corporation Method and mechanism for database statement optimization
US6356889B1 (en) * 1998-09-30 2002-03-12 International Business Machines Corporation Method for determining optimal database materializations using a query optimizer
US6370522B1 (en) * 1999-03-18 2002-04-09 Oracle Corporation Method and mechanism for extending native optimization in a database system
US6374235B1 (en) * 1999-06-25 2002-04-16 International Business Machines Corporation Method, system, and program for a join operation on a multi-column table and satellite tables including duplicate values
US6397204B1 (en) * 1999-06-25 2002-05-28 International Business Machines Corporation Method, system, and program for determining the join ordering of tables in a join query
US6401083B1 (en) * 1999-03-18 2002-06-04 Oracle Corporation Method and mechanism for associating properties with objects and instances
US6421663B1 (en) * 1999-06-14 2002-07-16 International Business Machines Corporation Optimization of joined table expressions by extended access path selection
US6438542B1 (en) 1999-08-30 2002-08-20 International Business Machines Corporation Method of optimally determining lossless joins
US6446063B1 (en) * 1999-06-25 2002-09-03 International Business Machines Corporation Method, system, and program for performing a join operation on a multi column table and satellite tables
US20020184225A1 (en) * 2001-05-31 2002-12-05 Hovhannes Ghukasyan Automatic generation of join graphs for relational database queries
US6493700B2 (en) * 1997-10-14 2002-12-10 International Business Machines Corporation System and method for specifying custom qualifiers for explain tables
US20030055813A1 (en) * 2001-05-15 2003-03-20 Microsoft Corporation Query optimization by sub-plan memoization
US20030097354A1 (en) * 2001-11-19 2003-05-22 Finlay Ian R. Method and system for index sampled tablescan
US20030100960A1 (en) * 2001-11-29 2003-05-29 International Business Machines Corporation Database query optimization apparatus and method
EP1349082A1 (en) * 2002-03-28 2003-10-01 LION Bioscience AG Method and apparatus for querying relational databases
US20050071083A1 (en) * 2003-09-29 2005-03-31 International Business Machines Corporation Method and structure for monitoring moving objects
US20050071321A1 (en) * 2003-09-29 2005-03-31 International Business Machines Corporation System and method for monitoring events against continual range queries
EP1555624A1 (en) * 2002-10-24 2005-07-20 Turbo Data Laboratories Inc. Method for converting concatenated join tables into tree structure and conversion program
US20050203940A1 (en) * 2004-03-12 2005-09-15 Sybase, Inc. Database System with Methodology for Automated Determination and Selection of Optimal Indexes
US20060075132A1 (en) * 2004-09-15 2006-04-06 Nokia Corporation Compressing, filtering, and transmitting of protocol messages via a protocol-aware intermediary node
US20060129535A1 (en) * 2004-12-15 2006-06-15 Purcell Terence P Generated predicates from materialized result
US20060136380A1 (en) * 2004-12-17 2006-06-22 Purcell Terence P System and method for executing a multi-table query
US20060212429A1 (en) * 2005-03-17 2006-09-21 Microsoft Corporation Answering top-K selection queries in a relational engine
US7185004B1 (en) 2005-12-09 2007-02-27 International Business Machines Corporation System and method for reverse routing materialized query tables in a database
US20070050347A1 (en) * 2005-08-30 2007-03-01 Microsoft Corporation Query comprehensions
US20070050367A1 (en) * 2005-08-31 2007-03-01 International Business Machines Corporation System and method for controlling database access
US7191138B1 (en) 2000-04-15 2007-03-13 Mindloft Corporation System for cataloging, inventorying selecting, measuring, valuing and matching intellectual capital and skills with a skill requirement
US20070106637A1 (en) * 2005-10-28 2007-05-10 Mediareif Mostl & Reif Kommunikations- Und Informationstechnologien Oeg Method for Controlling a Relational Database System
US20070130107A1 (en) * 2005-12-02 2007-06-07 Microsoft Corporation Missing index analysis and index useage statistics
US20070192215A1 (en) * 2006-02-10 2007-08-16 Taylor Thomas B Computer-implemented registration for providing inventory fulfillment services to merchants
US20070240224A1 (en) * 2006-03-30 2007-10-11 International Business Machines Corporation Sovereign information sharing service
US7319997B1 (en) * 2004-06-07 2008-01-15 Ncr Corp. Dynamic partition enhanced joining
US20080016038A1 (en) * 2006-07-11 2008-01-17 Matthias Moestl Method for controlling a relational database system
US20080235181A1 (en) * 2007-03-23 2008-09-25 Faunce Michael S Query Expression Evaluation Using Sample Based Projected Selectivity
US20090271362A1 (en) * 2006-06-01 2009-10-29 Mediareif Moestl & Reif Kommunikations-Und Infomationstechnologien Oeg Method for controlling a relational database system
US20090307192A1 (en) * 2008-06-10 2009-12-10 Microsoft Corporation Virtualizing objects within queries
US20090313210A1 (en) * 2008-06-17 2009-12-17 Bestgen Robert J Encoded matrix index
US7640244B1 (en) 2004-06-07 2009-12-29 Teredata Us, Inc. Dynamic partition enhanced joining using a value-count index
US7716215B2 (en) 2003-10-31 2010-05-11 International Business Machines Corporation System, method, and computer program product for progressive query processing
US20100169381A1 (en) * 2008-12-31 2010-07-01 International Business Machines Corporation Expression tree data structure for representing a database query
US7853480B2 (en) * 2007-05-21 2010-12-14 Amazon Technologies, Inc. System and method for providing export services to merchants
US7873629B1 (en) * 2004-06-07 2011-01-18 Teradata Us, Inc. Dynamic partition enhanced inequality joining using a value-count index
US20120278349A1 (en) * 2005-03-19 2012-11-01 Activeprime, Inc. Systems and methods for manipulation of inexact semi-structured data
US20140031956A1 (en) * 2008-12-04 2014-01-30 Io Data Centers, Llc Data center intelligent control and optimization
US20140372470A1 (en) * 2013-06-14 2014-12-18 International Business Machines Corporation On-the-fly encoding method for efficient grouping and aggregation
US9292560B2 (en) 2013-01-30 2016-03-22 International Business Machines Corporation Reducing collisions within a hash table
US9311359B2 (en) 2013-01-30 2016-04-12 International Business Machines Corporation Join operation partitioning
US9317517B2 (en) 2013-06-14 2016-04-19 International Business Machines Corporation Hashing scheme using compact array tables
US9672248B2 (en) 2014-10-08 2017-06-06 International Business Machines Corporation Embracing and exploiting data skew during a join or groupby
US9922064B2 (en) 2015-03-20 2018-03-20 International Business Machines Corporation Parallel build of non-partitioned join hash tables and non-enforced N:1 join hash tables

Citations (8)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5197005A (en) * 1989-05-01 1993-03-23 Intelligent Business Systems Database retrieval system having a natural language interface
US5276870A (en) * 1987-12-11 1994-01-04 Hewlett-Packard Company View composition in a data base management system
US5367675A (en) * 1991-12-13 1994-11-22 International Business Machines Corporation Computer automated system and method for optimizing the processing of a query in a relational database system by merging subqueries with the query
US5386556A (en) * 1989-03-06 1995-01-31 International Business Machines Corporation Natural language analyzing apparatus and method
US5519859A (en) * 1993-11-15 1996-05-21 Grace; John A. Method and apparatus for automatic table selection and generation of structured query language instructions
US5542073A (en) * 1993-01-07 1996-07-30 International Business Machines Corporation Computer program product for choosing largest selectivities among eligible predicates of join equivalence classes for query optimization
US5548755A (en) * 1995-02-17 1996-08-20 International Business Machines Corporation System for optimizing correlated SQL queries in a relational database using magic decorrelation
US5560007A (en) * 1993-06-30 1996-09-24 Borland International, Inc. B-tree key-range bit map index optimization of database queries

Patent Citations (8)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5276870A (en) * 1987-12-11 1994-01-04 Hewlett-Packard Company View composition in a data base management system
US5386556A (en) * 1989-03-06 1995-01-31 International Business Machines Corporation Natural language analyzing apparatus and method
US5197005A (en) * 1989-05-01 1993-03-23 Intelligent Business Systems Database retrieval system having a natural language interface
US5367675A (en) * 1991-12-13 1994-11-22 International Business Machines Corporation Computer automated system and method for optimizing the processing of a query in a relational database system by merging subqueries with the query
US5542073A (en) * 1993-01-07 1996-07-30 International Business Machines Corporation Computer program product for choosing largest selectivities among eligible predicates of join equivalence classes for query optimization
US5560007A (en) * 1993-06-30 1996-09-24 Borland International, Inc. B-tree key-range bit map index optimization of database queries
US5519859A (en) * 1993-11-15 1996-05-21 Grace; John A. Method and apparatus for automatic table selection and generation of structured query language instructions
US5548755A (en) * 1995-02-17 1996-08-20 International Business Machines Corporation System for optimizing correlated SQL queries in a relational database using magic decorrelation

Non-Patent Citations (4)

* Cited by examiner, † Cited by third party
Title
K. Ono & G. M. Lohman, Measuring the Complexity of Join Enumeration in Query Optimization, Proceedings of Sixteenth International Conference on Very Large Data Bases, pp. 314 324, Sep. 1990. *
K. Ono & G. M. Lohman, Measuring the Complexity of Join Enumeration in Query Optimization, Proceedings of Sixteenth International Conference on Very Large Data Bases, pp. 314-324, Sep. 1990.
P. G. Selinger et al., Access Path Selection in a Relational Database Management System, Proceedings of ACM SIGMOD Conference, pp. 23 34, May 1979. *
P. G. Selinger et al., Access Path Selection in a Relational Database Management System, Proceedings of ACM SIGMOD Conference, pp. 23-34, May 1979.

Cited By (97)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6134540A (en) * 1997-05-09 2000-10-17 International Business Machines Corporation System, method, and program for applying query rewrite technology to object building
US6493700B2 (en) * 1997-10-14 2002-12-10 International Business Machines Corporation System and method for specifying custom qualifiers for explain tables
US6356889B1 (en) * 1998-09-30 2002-03-12 International Business Machines Corporation Method for determining optimal database materializations using a query optimizer
US6370522B1 (en) * 1999-03-18 2002-04-09 Oracle Corporation Method and mechanism for extending native optimization in a database system
US6351742B1 (en) * 1999-03-18 2002-02-26 Oracle Corporation Method and mechanism for database statement optimization
US6738782B2 (en) 1999-03-18 2004-05-18 Oracle International Corporation Method and mechanism for extending native optimization in a database system
US6401083B1 (en) * 1999-03-18 2002-06-04 Oracle Corporation Method and mechanism for associating properties with objects and instances
WO2000063798A1 (en) * 1999-04-16 2000-10-26 Targit A/S A method and an apparatus for the processing of queries to a database
US6732091B1 (en) 1999-04-16 2004-05-04 Targit A/S Method and an apparatus for the processing of queries to a database
US6421663B1 (en) * 1999-06-14 2002-07-16 International Business Machines Corporation Optimization of joined table expressions by extended access path selection
US6374235B1 (en) * 1999-06-25 2002-04-16 International Business Machines Corporation Method, system, and program for a join operation on a multi-column table and satellite tables including duplicate values
US6446063B1 (en) * 1999-06-25 2002-09-03 International Business Machines Corporation Method, system, and program for performing a join operation on a multi column table and satellite tables
US6397204B1 (en) * 1999-06-25 2002-05-28 International Business Machines Corporation Method, system, and program for determining the join ordering of tables in a join query
US6601063B2 (en) 1999-08-30 2003-07-29 International Business Machines Corporation Method of optimally determining lossless joins
US6438542B1 (en) 1999-08-30 2002-08-20 International Business Machines Corporation Method of optimally determining lossless joins
US6353821B1 (en) * 1999-12-23 2002-03-05 Bull Hn Information Systems Inc. Method and data processing system for detecting patterns in SQL to allow optimized use of multi-column indexes
WO2001046864A1 (en) * 1999-12-23 2001-06-28 Bull Hn Information Systems Inc. Method and data processing system for detecting patterns in sql to allow optimized use of multi-column indexes
US7191138B1 (en) 2000-04-15 2007-03-13 Mindloft Corporation System for cataloging, inventorying selecting, measuring, valuing and matching intellectual capital and skills with a skill requirement
WO2002010986A1 (en) * 2000-07-28 2002-02-07 Powerway, Inc. System and method for supply chain product and process development collaboration
US7747572B2 (en) 2000-07-28 2010-06-29 Waypoint Global Ii, Inc. Method and system for supply chain product and process development collaboration
US20020052862A1 (en) * 2000-07-28 2002-05-02 Powerway, Inc. Method and system for supply chain product and process development collaboration
US6850925B2 (en) * 2001-05-15 2005-02-01 Microsoft Corporation Query optimization by sub-plan memoization
US20030055813A1 (en) * 2001-05-15 2003-03-20 Microsoft Corporation Query optimization by sub-plan memoization
US20020184225A1 (en) * 2001-05-31 2002-12-05 Hovhannes Ghukasyan Automatic generation of join graphs for relational database queries
US6996567B2 (en) * 2001-05-31 2006-02-07 Heuristic Physics Laboratories, Inc. Automatic generation of join graphs for relational database queries
US20030097354A1 (en) * 2001-11-19 2003-05-22 Finlay Ian R. Method and system for index sampled tablescan
US7680821B2 (en) * 2001-11-19 2010-03-16 International Business Machines Corporation Method and system for index sampled tablescan
US6968330B2 (en) * 2001-11-29 2005-11-22 International Business Machines Corporation Database query optimization apparatus and method
US20030100960A1 (en) * 2001-11-29 2003-05-29 International Business Machines Corporation Database query optimization apparatus and method
US20060074857A1 (en) * 2002-03-28 2006-04-06 Thure Etzold Method and apparatus for querying relational databases
EP1349082A1 (en) * 2002-03-28 2003-10-01 LION Bioscience AG Method and apparatus for querying relational databases
WO2003083712A1 (en) * 2002-03-28 2003-10-09 Lion Bioscience Ag Method and apparatus for querying relational databases
CN100419750C (en) 2002-10-24 2008-09-17 特博数据实验室公司 Method for converting concatenated join tables into tree structure and conversion program
EP1555624A4 (en) * 2002-10-24 2007-01-24 Turbo Data Lab Inc Method for converting concatenated join tables into tree structure and conversion program
EP1555624A1 (en) * 2002-10-24 2005-07-20 Turbo Data Laboratories Inc. Method for converting concatenated join tables into tree structure and conversion program
US20050071321A1 (en) * 2003-09-29 2005-03-31 International Business Machines Corporation System and method for monitoring events against continual range queries
US8972380B2 (en) 2003-09-29 2015-03-03 International Business Machines Corporaton System and method for monitoring events against continual range queries
US20050071083A1 (en) * 2003-09-29 2005-03-31 International Business Machines Corporation Method and structure for monitoring moving objects
US7835953B2 (en) * 2003-09-29 2010-11-16 International Business Machines Corporation Method and structure for monitoring moving objects
US7716215B2 (en) 2003-10-31 2010-05-11 International Business Machines Corporation System, method, and computer program product for progressive query processing
US7406477B2 (en) 2004-03-12 2008-07-29 Sybase, Inc. Database system with methodology for automated determination and selection of optimal indexes
US20050203940A1 (en) * 2004-03-12 2005-09-15 Sybase, Inc. Database System with Methodology for Automated Determination and Selection of Optimal Indexes
US7873629B1 (en) * 2004-06-07 2011-01-18 Teradata Us, Inc. Dynamic partition enhanced inequality joining using a value-count index
US7640244B1 (en) 2004-06-07 2009-12-29 Teredata Us, Inc. Dynamic partition enhanced joining using a value-count index
US7319997B1 (en) * 2004-06-07 2008-01-15 Ncr Corp. Dynamic partition enhanced joining
US20060075132A1 (en) * 2004-09-15 2006-04-06 Nokia Corporation Compressing, filtering, and transmitting of protocol messages via a protocol-aware intermediary node
US20060129535A1 (en) * 2004-12-15 2006-06-15 Purcell Terence P Generated predicates from materialized result
US7953726B2 (en) 2004-12-15 2011-05-31 International Business Machines Corporation Generated predicates from materialized result
US20080288469A1 (en) * 2004-12-15 2008-11-20 International Business Machines Corporation Generated predicates form materialized result
US7536379B2 (en) * 2004-12-15 2009-05-19 International Business Machines Corporation Performing a multiple table join operating based on generated predicates from materialized results
US20060136380A1 (en) * 2004-12-17 2006-06-22 Purcell Terence P System and method for executing a multi-table query
US20060212429A1 (en) * 2005-03-17 2006-09-21 Microsoft Corporation Answering top-K selection queries in a relational engine
US20120278349A1 (en) * 2005-03-19 2012-11-01 Activeprime, Inc. Systems and methods for manipulation of inexact semi-structured data
US20070050347A1 (en) * 2005-08-30 2007-03-01 Microsoft Corporation Query comprehensions
US7730079B2 (en) * 2005-08-30 2010-06-01 Microsoft Corporation Query comprehensions
US20070050367A1 (en) * 2005-08-31 2007-03-01 International Business Machines Corporation System and method for controlling database access
US7676456B2 (en) * 2005-08-31 2010-03-09 International Business Machines Corporation System and method for controlling database access
US8117187B2 (en) 2005-10-28 2012-02-14 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US20070106637A1 (en) * 2005-10-28 2007-05-10 Mediareif Mostl & Reif Kommunikations- Und Informationstechnologien Oeg Method for Controlling a Relational Database System
US7769744B2 (en) * 2005-12-02 2010-08-03 Microsoft Cororation Missing index analysis and index useage statistics
US20070130107A1 (en) * 2005-12-02 2007-06-07 Microsoft Corporation Missing index analysis and index useage statistics
US7185004B1 (en) 2005-12-09 2007-02-27 International Business Machines Corporation System and method for reverse routing materialized query tables in a database
US20070192215A1 (en) * 2006-02-10 2007-08-16 Taylor Thomas B Computer-implemented registration for providing inventory fulfillment services to merchants
US20070240224A1 (en) * 2006-03-30 2007-10-11 International Business Machines Corporation Sovereign information sharing service
US8607350B2 (en) * 2006-03-30 2013-12-10 International Business Machines Corporation Sovereign information sharing service
US7937390B2 (en) 2006-06-01 2011-05-03 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US20090271362A1 (en) * 2006-06-01 2009-10-29 Mediareif Moestl & Reif Kommunikations-Und Infomationstechnologien Oeg Method for controlling a relational database system
US8296313B2 (en) 2006-07-11 2012-10-23 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US20100250520A1 (en) * 2006-07-11 2010-09-30 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US7774337B2 (en) 2006-07-11 2010-08-10 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US20080016038A1 (en) * 2006-07-11 2008-01-17 Matthias Moestl Method for controlling a relational database system
US8688682B2 (en) 2007-03-23 2014-04-01 International Business Machines Corporation Query expression evaluation using sample based projected selectivity
US20080235181A1 (en) * 2007-03-23 2008-09-25 Faunce Michael S Query Expression Evaluation Using Sample Based Projected Selectivity
US7853480B2 (en) * 2007-05-21 2010-12-14 Amazon Technologies, Inc. System and method for providing export services to merchants
US20090307192A1 (en) * 2008-06-10 2009-12-10 Microsoft Corporation Virtualizing objects within queries
US7917547B2 (en) 2008-06-10 2011-03-29 Microsoft Corporation Virtualizing objects within queries
US20090313210A1 (en) * 2008-06-17 2009-12-17 Bestgen Robert J Encoded matrix index
US8738632B2 (en) * 2008-06-17 2014-05-27 International Business Machines Corporation Encoded matrix index
US8832112B2 (en) * 2008-06-17 2014-09-09 International Business Machines Corporation Encoded matrix index
US20120179712A1 (en) * 2008-06-17 2012-07-12 International Business Machines Corporation Encoded matrix index
US20140031956A1 (en) * 2008-12-04 2014-01-30 Io Data Centers, Llc Data center intelligent control and optimization
US9791837B2 (en) * 2008-12-04 2017-10-17 Baselayer Technology, Llc Data center intelligent control and optimization
US8239406B2 (en) 2008-12-31 2012-08-07 International Business Machines Corporation Expression tree data structure for representing a database query
US20100169381A1 (en) * 2008-12-31 2010-07-01 International Business Machines Corporation Expression tree data structure for representing a database query
US9317548B2 (en) 2013-01-30 2016-04-19 International Business Machines Corporation Reducing collisions within a hash table
US9665624B2 (en) 2013-01-30 2017-05-30 International Business Machines Corporation Join operation partitioning
US9292560B2 (en) 2013-01-30 2016-03-22 International Business Machines Corporation Reducing collisions within a hash table
US9311359B2 (en) 2013-01-30 2016-04-12 International Business Machines Corporation Join operation partitioning
US9317517B2 (en) 2013-06-14 2016-04-19 International Business Machines Corporation Hashing scheme using compact array tables
US9367556B2 (en) 2013-06-14 2016-06-14 International Business Machines Corporation Hashing scheme using compact array tables
US9405858B2 (en) * 2013-06-14 2016-08-02 International Business Machines Corporation On-the-fly encoding method for efficient grouping and aggregation
US20140372411A1 (en) * 2013-06-14 2014-12-18 International Business Machines Corporation On-the-fly encoding method for efficient grouping and aggregation
US9471710B2 (en) * 2013-06-14 2016-10-18 International Business Machines Corporation On-the-fly encoding method for efficient grouping and aggregation
US20140372470A1 (en) * 2013-06-14 2014-12-18 International Business Machines Corporation On-the-fly encoding method for efficient grouping and aggregation
US20160253390A1 (en) * 2013-06-14 2016-09-01 International Business Machines Corporation On-the-fly encoding method for efficient grouping and aggregation
US9672248B2 (en) 2014-10-08 2017-06-06 International Business Machines Corporation Embracing and exploiting data skew during a join or groupby
US9922064B2 (en) 2015-03-20 2018-03-20 International Business Machines Corporation Parallel build of non-partitioned join hash tables and non-enforced N:1 join hash tables

Also Published As

Publication number Publication date Type
CA2168287A1 (en) 1996-10-01 application
CA2168287C (en) 2000-05-23 grant

Similar Documents

Publication Publication Date Title
Ilyas et al. Supporting top-k join queries in relational databases
Cui et al. Tracing the lineage of view data in a warehousing environment
Graefe et al. Dynamic query evaluation plans
Chamberlin et al. A history and evaluation of System R
US6324533B1 (en) Integrated database and data-mining system
US6618719B1 (en) Database system with methodology for reusing cost-based optimization decisions
US6928431B2 (en) Dynamic end user specific customization of an application&#39;s physical data layer through a data repository abstraction layer
US6301581B1 (en) Method and system for managing access to a plurality of data objects
US6026394A (en) System and method for implementing parallel operations in a database management system
US5826257A (en) Method and structure for maintaining and utilizing a lookup value associated with a stored database value
US5873075A (en) Synchronization of SQL actions in a relational database system
US4769772A (en) Automated query optimization method using both global and parallel local optimizations for materialization access planning for distributed databases
US7171427B2 (en) Methods of navigating a cube that is implemented as a relational object
US5619692A (en) Semantic optimization of query order requirements using order detection by normalization in a query compiler system
US5940819A (en) User specification of query access paths in a relational database management system
US8126870B2 (en) System and methodology for parallel query optimization using semantic-based partitioning
US5745746A (en) Method for localizing execution of subqueries and determining collocation of execution of subqueries in a parallel database
US6377943B1 (en) Initial ordering of tables for database queries
US6266660B1 (en) Secondary index search
US7693820B2 (en) Use of materialized transient views in query optimization
US5758144A (en) Database execution cost and system performance estimator
US5261093A (en) Interactive relational database analysis with successive refinement steps in selection of ouput data from underlying database
US6947927B2 (en) Method and apparatus for exploiting statistics on query expressions for optimization
US6792420B2 (en) Method, system, and program for optimizing the processing of queries involving set operators
US6212514B1 (en) Data base optimization method for estimating query and trigger procedure costs

Legal Events

Date Code Title Description
FPAY Fee payment

Year of fee payment: 4

FPAY Fee payment

Year of fee payment: 8

FPAY Fee payment

Year of fee payment: 12