Eliminating many-to-many joins between database tables

Abstract

A method, computer system and computer program product, the method comprising: upon receiving a database query comprising at least a first and a second join operations: storing the database query in the memory; identifying by the processor within the database query the first join operation, wherein the first join operation is a many-to-many join operation or a one-to-many join operation between a first table and a second table, in which fields of the second table are used only for filtering rows from the first table or for joining with another table; and converting by the processor the first join operation to an enhanced operation which eliminates returning at least two matches for at least one row from the first table when the database query is executed.

What is claimed is: 
     
         1 - 16 . (canceled) 
     
     
         17 . A method for enhancing database query execution, comprising:
 identifying a first join operation within a database query between a first table and a second table, wherein the first join operation is a many-to-many join operation or a one-to-many join operation, wherein fields of the second table are used for filtering rows from the first table or for joining with another table; and   converting the first join operation to an enhanced operation, wherein the enhanced operation includes a semijoin operation.   
     
     
         18 . The method of  claim 17 , wherein the enhanced operation eliminates returning at least two matches for at least one row from the first table when the database query is executed. 
     
     
         19 . The method of  claim 17 , wherein the fields of the second table are used only for filtering rows from the first table or for joining with another table. 
     
     
         20 . The method of  claim 17 , further comprising:
 converting the second join operation to an enhanced operation when the second join operation is between a third table and a fourth table, wherein the fourth table fields are used only for filtering rows from the third table or for joining with other tables.   
     
     
         21 . The method of  claim 17 , wherein the first join operation is converted to an enhanced operation during execution of the database query. 
     
     
         22 . The method of  claim 17 , wherein identifying the first join operation comprises:
 building a graph data structure from the database query, wherein the graph data structure includes a respective node for each table in the database query and an edge connecting two nodes, wherein the database query include a join operation between the two tables represented by the two nodes, wherein the edge the first join operation; and   traversing the graph to identify the edge representing the first join operation.   
     
     
         23 . The method of  claim 22 , wherein building the graph data structure comprises:
 generating the graph data structure based on the database query;   indicating, as an invisible node, each node connected to an edge in the graph in which no field is used for display, computation, or aggregation; and   indicating, as a filtering node, each node connected to an edge in the graph representing a table in which at least one field is used for filtering.   
     
     
         24 . The method of  claim 17 , wherein converting the first join operation to the enhanced operation is subject to at least one condition selected from the group comprising: a user indicating that the first join should be converted; the user indicating that any of a group of join operations that includes the first join operation may be converted; the user indicating that any join operation may be converted; the first join being a many-to-many join operation; and size of the first join operation exceeding a predetermined number of records. 
     
     
         25 . The method of  claim 17 , wherein the database is a columnary database or a relational database. 
     
     
         26 . A non-transitory computer readable medium having stored thereon instructions for causing a processing circuitry to execute a process, the process comprising:
 identifying a first join operation within a database query between a first table and a second table, wherein the first join operation is a many-to-many join operation or a one-to-many join operation, wherein fields of the second table are used for filtering rows from the first table or for joining with another table; and   converting the first join operation to an enhanced operation, wherein the enhanced operation includes a semijoin operation.   
     
     
         27 . A system for enhancing database query execution, comprising:
 a processing circuitry; and   a memory, the memory containing instructions that, when executed by the processing circuitry, configure the system to:   identify a first join operation within a database query between a first table and a second table, wherein the first join operation is a many-to-many join operation or a one-to-many join operation, wherein fields of the second table are used for filtering rows from the first table or for joining with another table; and   convert the first join operation to an enhanced operation, wherein the enhanced operation includes a semijoin operation.   
     
     
         28 . The system of  claim 27 , wherein the enhanced operation eliminates returning at least two matches for at least one row from the first table when the database query is executed. 
     
     
         29 . The system of  claim 27 , wherein the fields of the second table are used only for filtering rows from the first table or for joining with another table. 
     
     
         30 . The system of  claim 27 , wherein the system is further configured to:
 convert the second join operation to an enhanced operation when the second join operation is between a third table and a fourth table, wherein the fourth table fields are used only for filtering rows from the third table or for joining with other tables.   
     
     
         31 . The system of  claim 27 , wherein the first join operation is converted to an enhanced operation during execution of the database query. 
     
     
         32 . The system of  claim 27 , wherein the system is further configured to:
 build a graph data structure from the database query, wherein the graph data structure includes a respective node for each table in the database query and an edge connecting two nodes, wherein the database query include a join operation between the two tables represented by the two nodes, wherein the edge the first join operation; and   traverse the graph to identify the edge representing the first join operation.   
     
     
         33 . The system of  claim 32 , wherein the system is further configured to:
 generate the graph data structure based on the database query;   indicate, as an invisible node, each node connected to an edge in the graph in which no field is used for display, computation, or aggregation; and   indicate, as a filtering node, each node connected to an edge in the graph representing a table in which at least one field is used for filtering.   
     
     
         34 . The system of  claim 27 , wherein converting the first join operation to the enhanced operation is subject to at least one condition selected from the group comprising: a user indicating that the first join should be converted; the user indicating that any of a group of join operations that includes the first join operation may be converted; the user indicating that any join operation may be converted; the first join being a many-to-many join operation; and size of the first join operation exceeding a predetermined number of records. 
     
     
         35 . The system of  claim 27 , wherein the database is a columnary database or a relational database.