Abstract:
A system and method for providing parallel result streams for database queries is provided. The system includes a network including a client, a server, and a database. The client executes an application and sends a query to the server. In response, the server compiles the query to produce a query plan, executes statements in the query plan and sends parallel result streams to the client.

Description:
FIELD OF THE INVENTION 
     The present invention relates generally to databases, database queries, and more particularly to parallel extraction and parallel streams. 
     BACKGROUND 
     A data stream (a/k/a stream) may be defined as the communication of data over time. A result stream may be defined as a data stream resulting from a query within a database. Large databases, such as, for example, those used in data warehousing, may be partitioned using range partitioning, list partitioning, hash partitioning, or composite partitioning, for example. A database extract operation may, for example, extract information from a hash partitioned database. 
     Database extracts to satisfy a query are typically provided as a single stream for the whole query. A single stream for the whole query may severely limit performance of an extract operation and provide limited opportunities for network and throughput scaling. In addition, as a workaround, range queries (i.e., a query over a range of values) may be used to parallelize database extracts. Range queries may result in resource consumption that is a multiple of the number of parallel range queries issued, when the data is hash-partitioned and every disk hosting a partition for the table may have to do work to materialize data for the query regardless of the ranges in use. 
    
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
       Embodiments according to the invention are illustrated by the following drawings. 
         FIG. 1  is a block diagram illustrating an exemplary system for providing parallel result streams for database queries; 
         FIG. 2  is a block diagram illustrating an exemplary method and system for an initiator session in an application running on a workstation to send a query to a database server; and 
         FIG. 3  is a block diagram illustrating an exemplary system for a number of consumer sessions to receive parallel result streams in response to the query requested in  FIG. 2 ; and 
         FIGS. 4A and 4B  are flow diagrams illustrating exemplary methods according to exemplary embodiments of the invention 
     
    
    
     DETAILED DESCRIPTION 
       FIG. 1  illustrates an exemplary system  100  for providing parallel result streams for database queries. The exemplary system  100  may be a client-server system and may include various hardware, firmware, software, and/or other computing devices. One or more client hosts  102  may communicate over a network  104  to one or more server hosts  106 , which may be coupled to one or more databases  108 . The database  108  may be, for example, a hash-partitioned parallel database or some other kind of database. One or more applications  110 , such as, for example, a load/extract application may run on the client host  102  and may send query text to the server host  106  over the network  104 . The client host  102  and the server host  106  each may include a storage medium, a processor, and communication hardware and software. The server host  106  may include one or more query plans  112 , a query text compiler  114 , a number of executing processes  116 , and one or more instances of an execution engine  118 . For example, the query text sent from the client host  102  may be compiled by the compiler  114  and executed by processes  116  and/or instances of the execution engine  118 . The results of the query may then be sent to the application  110  the client host  102 . 
       FIGS. 2 and 4A  illustrate an exemplary system  200  and method. System  200  is one example of the system  100  of  FIG. 1 . In system  200 , the client node  102  may be a workstation  204  including an initiator session  202 . The initiator session  202  may send query text to the server host  106 , which may be a database server  206  as shown in  FIG. 2 , step  410 . The exemplary system  200  may perform one or more methods or database applications, such as, for example, online transaction processing (OLTP), query processing in data warehousing, or data movement. 
     The workstation  204 , in a database environment, may include a load/extract application that may establish the initiator session  202  with the database server  206 . The load/extract application may issue queries within the initiator session  206 . When the load/extract application makes a request to query data from the database  108 , a query plan  112  for servicing the query may be generated, step  420 . 
     The query plan  112  may be a sequence of steps or operations that the database server  206  may perform to satisfy the query. The query plan  112  may be executed to generate the results, which may be presented to the application. The query plan  112  may be stored in a data structure, such as, for example, a tree with one root node  208 , top-level nodes  210  and lower nodes  212  in the tree that represent the levels of processing steps or operations of the query. These intermediate processing nodes may involve joins, sorts, merges, disk access or other kinds of processing steps or operations. The non-root nodes  210 ,  212  in the query plan  112  may execute in different database server processes  116  and may, for example, be spread throughout a cluster, in a clustered database architecture. 
     The database server  206  may identify portions of the query plan that may be executed in parallel by, for example, accessing table partitions and intermediate sorts, and schedule them to run in parallel. The final results may be delivered to the root node  208 , which is associated with the initiator session  202 , steps  430 ,  440 . 
       FIGS. 3 and 4B  illustrate an exemplary method and system  200  for a number of consumer sessions  214  to receive parallel result streams in response to the query requested in  FIG. 2 . While the prior art delivered final query results in a serial manner, the exemplary system  200  may deliver query results in a parallel manner. This may facilitate faster data movement for some data warehouse applications, such as, for example, parallel extract transform load (ETL) jobs. 
     Using the initiator session  202 , the application  110  may submit a query request, which may include a desired number of parallel result streams, to the database server  206 . The workstation  204  and the database server  206  may communicate to create a number of consumer sessions  214  in the application  110  with access to parallel result streams from the top-level nodes  210 , where the access may be through a number of root nodes  208 . 
     The access to the parallel result streams may be provided with various security mechanisms. The initiator session  202  may send authentication information from the database server  206  to the consumer sessions  214  to be used to access the result streams. The consumer sessions  214  may share the transactional context of the initiator. 
     The system  200  may allow users to initiate, for example, a parallel extract operation by running a load/extract application running on the workstation  204 . The input to the operation may be the text of a query, the requested number of result streams, and/or a destination  216  for each stream. Destinations  216  may include files, named pipes, processes, file transfer protocol (FTP) targets and other destinations. The parallel extract operation may divide the query result stream into a collection of smaller streams and move those streams in parallel to the destinations  216 . The load/extract application may annotate structured query language (SQL) statements in the query text sent from the initiator session  202  to the database server  206  with a desired number of result streams, step  450 . 
     In response to the query text, the database server  206  may create the query plan  112  so that the number of top-level nodes  210  is the same as the desired number of result streams. Each top-level node  210  may receive one result stream from lower nodes  212  throughout a network cluster for parallelism and load balancing benefits. The database server  206  may use knowledge of data placement and the cost of SQL operations to best determine the placement of the top-level nodes  210 . 
     The initiator session  202  may send query text to the database server  206  and the database server  206  may initialize the query plan  112 . After the database server  206  creates the query plan  112 , the application  110  on the workstation  204  may run. The application  110  may be, for example, a load/extract application. The load/extract application may send a request for information about the top-level nodes  210  to the database serer  206 . The database server  206  may respond with information (e.g., a collection of internally-generally SQL statements) that may be used by the application  110  to contact the top-level nodes  210 . The application  110  may use this information to establish a number of consumer sessions  208  and may issue a consumer query (e.g., SQL statement) from each consumer session  208  to each top-level node  210 . The consumer queries may execute in parallel (e.g., within different processes or threads) on the workstation  204 , step  460 . 
     Within the initiator session  202 , the load/extract application may issue a request to the database server  206  to begin sending the parallel result streams. The database server  206  may respond with a status, such as, for example, successful delivery of the parallel result streams. 
     Within each consumer session  208 , the database server  206  may process a consumer query by compiling it, modifying it and creating a partial query plan (not shown). The partial query plan may include knowledge of one of the top-level nodes  210 . When the partial query plan is executed, this particular top-level node  210  may be contacted and instructed where to send its result stream (from the lower nodes  212  under that top-level node  210 ). Each result stream may flow out of a top-level node  210  and into a consumer session that is under control of the load/extract application. As result streams are delivered or communicated directly to the consumer sessions  214 , the load/extract application may move data into the appropriate destinations  216 . In general, the delivery of the parallel result streams may be implemented in various ways using a listener-handler (e.g., each top-level node as a listener and each consumer session as a handler) model. The parallel result streams may be delivered using SQL statements or in other ways. 
     Exemplary embodiments have many benefits, including providing parallel result streams from a query. In a data warehouse scenario, parallel result streams may be returned from operations such as ETL aggregation queries and data movement queries, resulting in faster extracts and loads. 
     While various embodiments of the present invention have been described above, it should be understood that they have been presented by way of example only, and not limitation. Thus, the breadth and scope of the present invention should not be limited by any of the above-described exemplary embodiments, but should instead be defined only in accordance with the following claims and their equivalents.