Query Optimization in a Parallel Computer System with Multiple Networks

Abstract

An apparatus and method for a database query optimizer to optimize a query that uses multiple networks. The database query optimizer optimizes a query that uses multiple networks to satisfy the query by splitting the query execution to use multiple networks. Thus, the query optimizer rewrites or optimizes a query to execute on multiple nodes or networks to more efficiently execute the query and reduce network traffic on a network. The query optimizer uses plan cache statistics to determine whether to use multiple networks to optimize the query.

1 ) A computer apparatus comprising:
 a plurality of nodes each having a memory and at least one processor;   a database residing in the memory;   a plurality of networks connecting the plurality of nodes;   a query residing in the memory; and   a query optimizer residing in the memory and executed by the at least one processor, wherein the query optimizer creates an optimized query that splits the query to use multiple networks in executing the query.   
   
   
       2 ) The computer apparatus of  claim 1  wherein the optimized query utilizes additional networks to increase communication speed to allow the query to execute faster. 
   
   
       3 ) The computer apparatus of  claim 1  wherein the at least one of the plurality of networks would be overloaded by executing the query without using multiple networks. 
   
   
       4 ) The computer apparatus of  claim 1  further comprising plan cache statistics that are used by the query optimizer to determine whether to use multiple networks to optimize the query. 
   
   
       5 ) The computer apparatus of  claim 4  wherein the plan cache statistics are selected from the following: query identification (ID), execution time, nodes involved, and estimated number of bytes for the query. 
   
   
       6 ) The computer apparatus of  claim 1  further comprising a service node connected to the plurality of nodes that controls the plurality of nodes, and a network monitor that periodically monitors the plurality of networks to determine network loading. 
   
   
       7 ) The computer apparatus of  claim 1  further comprising a private network with second plurality of nodes and global network connecting the first and second plurality of nodes; and wherein the query optimizer optimizes the query to join a result from the second plurality of nodes on the private network and then join the results with first plurality of nodes on the global network. 
   
   
       8 ) The computer apparatus of  claim 1  further comprising a network file that is used by the query optimizer and wherein the network file contains network file information selected from the following: network ID, a timestamp, current utilization, future utilization, availability, latency and retransmits. 
   
   
       9 ) A computer implemented method for optimizing a query on a parallel computer system comprising the steps of:
 receiving a query to a database;   checking a plan cache statistics table for information on networks involved in the query;   where the query can be divided, checking other networks for availability and splitting the query to move a portion of the query to an available network;   optimizing the query to split the query to multiple networks; and   executing the optimized query.   
   
   
       10 ) The computer implemented method of  claim 9  further comprising the step of logging plan cache statistics that are used by the query optimizer to determine whether to use multiple networks to optimize the query. 
   
   
       11 ) The computer implemented method of  claim 10  wherein the plan cache statistics are selected from the following: query identification (ID), execution time, nodes involved, and estimated number of bytes for the query 
   
   
       12 ) The computer implemented method of  claim 9  further comprising the step of optimizing the query by joining a first result from a second plurality of nodes on a private network and then join the first result with a second result from the first plurality of nodes on the global network. 
   
   
       13 ) An article of manufacture for executing on a parallel computer system with a plurality of compute nodes comprising:
 a query optimizer that creates an optimized query to reduce network traffic over at least one of a plurality of networks connecting the plurality of compute nodes on the parallel computer system by splitting the query to use multiple networks; and   computer-readable medium in which computer instructions are stored, which instructions, when read by a computer, cause the computer to perform the steps of the query optimizer.   
   
   
       14 ) The article of manufacture of  claim 13  wherein the optimized query utilizes additional networks to increase communication speed to allow the query to execute faster. 
   
   
       15 ) The article of manufacture of  claim 13  wherein the at least one of the plurality of networks would be overloaded by executing the query without using multiple networks. 
   
   
       16 ) The computer apparatus of  claim 13  further comprising plan cache statistics that are used by the query optimizer to determine whether to use multiple networks to optimize the query. 
   
   
       17 ) The article of manufacture of  claim 16  wherein the plan cache statistics are selected from the following: query identification (ID), execution time, nodes involved, and estimated number of bytes for the query. 
   
   
       18 ) The article of manufacture of  claim 13  further comprising a private network with second plurality of nodes and global network connecting the first and second plurality of nodes; and wherein the query optimizer optimizes the query to join a result from the first second plurality of nodes on the private network and then join the results with first plurality of nodes on the global network. 
   
   
       19 ) The article of manufacture of  claim 13  further comprising a network file that is used by the query optimizer and wherein the network file contains network file information selected from the following: network ID, a timestamp, current utilization, future utilization, availability, latency and retransmits