Classifications

• • G—PHYSICS
  • G06—COMPUTING; CALCULATING OR COUNTING
  • G06F—ELECTRIC DIGITAL DATA PROCESSING
  • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
  • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
  • G06F16/24—Querying
  • G06F16/245—Query processing
  • G06F16/2453—Query optimisation
  • G06F16/24532—Query optimisation of parallel queries
• • Y—GENERAL 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
  • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
  • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
  • Y10S707/00—Data processing: database and file management or data structures
  • Y10S707/964—Database arrangement
  • Y10S707/966—Distributed
• • Y—GENERAL 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
  • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
  • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
  • Y10S707/00—Data processing: database and file management or data structures
  • Y10S707/99931—Database or file accessing
  • Y10S707/99932—Access augmentation or optimizing
• • Y—GENERAL 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
  • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
  • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
  • Y10S707/00—Data processing: database and file management or data structures
  • Y10S707/99931—Database or file accessing
  • Y10S707/99933—Query processing, i.e. searching
• • Y—GENERAL 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
  • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
  • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
  • Y10S707/00—Data processing: database and file management or data structures
  • Y10S707/99941—Database schema or data structure
  • Y10S707/99943—Generating database or data structure, e.g. via user interface
• • Y—GENERAL 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
  • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
  • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
  • Y10S707/00—Data processing: database and file management or data structures
  • Y10S707/99951—File or database maintenance
  • Y10S707/99952—Coherency, e.g. same view to multiple users

Definitions

• This invention relates to digital data processing and. more particularly, to methods and apparatus for database management systems on multiprocessor digital data processing systems.
• One or more indexes on large tables are generally provided to facilitate the most common data accesses, e.g., look-ups based on employee name.
• corresponding rows in two or more tables are identified by matching data values in one or more columns.
• the department name corresponding to a given employee may be identified by matching his or her department number to a row in a department data table that gives department numbers and department names.
• This is in contrast to hierarchical, network, and other DBMS's that use pointers instead of data values to indicate corresponding rows when tables are combined, or "joined.”
• Relational DBMS's typically permit the operator to access information in the database via a query. This is a command that specifies which data fields (columns) are to be retrieved from a database table and which records (rows) those fields are to be selected from.
• a query for the names of all employees in department 10 might be fashioned as follows:
• a particular relational data table need not be stored in a single computer file but, rather, can be partitioned among many files. This makes such tables particularly suited for use on multiprocessor computer systems, i.e., computer systems having multiple processors and multiple disk drives (or other storage devices) of the type disclosed in U.S. Patent 5,055,999.
• prior art DBMS's have not proven capable of taking full advantage of the power of such multiprocessing systems and, particularly, their power to simultaneously process data (in parallel) from multiple partitions on multiple storage devices with multiple central processing units.
• an object of the invention is to provide improved methods and apparatus for database management and, particularly, improved methods and apparatus for data base management capable of operating on multiprocessor systems.
• a further object of the invention is to provide improved systems for database management capable of effectively accessing a relational database contained in multiple tables and multiple partitions.
• a still further object is to provide improved methods and apparatus for storing and retrieving data for access by a DBMS.
• DBMS database management system
• the improvement is characterized by a parallel interface that intercepts selected queries prior to substantive processing by the standard interface.
• the standard interface is often called the "server" interface; it is accessed by clients that are the source of queries.
• a decomposition element within the parallel interface generates multiple subqueries from the intercepted query. Those subqueries, each representing a request for access to data stored in a respective partition of the table, are applied in parallel to the standard interface in lieu of the intercepted query.
• Responses by the DBMS to the subqueries are reassembled to generate a final response representing the response the DBMS would have generated to the intercepted query signal itself.
• Such reassembly can include interleaving the data contained in the responses (e.g., to create a single sorted list) or applying an aggregate function (e.g., sum or average) to that data.
• the decomposition element generates the subqueries to be substantially identical to the intercepted signal but including an "intersecting predicate" (i.e., additional query conditions) that evaluates true for all data records in respective partitions of said database table and ⁇ /-?e for all others.
• This can be, for example, a logically AND'ed condition that evaluates true for records in the respective partition.
• a subquery for the first partition could be generated as follows (where rowid has three parts, the last of which indicates the partition number):
• the invention contemplates a further improvement to a digital data processing system of the type described above, wherein the DBMS responds to selected queries for accessing data records joined from one or more of database tables, and wherein the DBMS includes an optimizer for determining an optimal strategy for applying such queries to the tables.
• the improvement of this aspect is characterized by an element for identifying, from output of the optimizer, a driving table whose partitions will be targeted by subqueries generated in responding to an intercepted query.
• the improvement is further characterized by generating the subqueries to include, in addition to the predicate list of the intercepted query, an intersecting predicate for all data records in respective partitions of the driving database table.
• tables referenced in the query other than the driving table need not be identically partitioned to the driving table, nor co-located with its partitions on storage devices.
• Tables may be accessed through either full- table scans or indexed scans, i.e., whether the DBMS searches all blocks of the relevant partition or only those indicated by a relevant index.
• the invention provides an improvement to a digital data processing system of the type described, wherein the DBMS's standard interface is invoked by a procedure or function call.
• the improvement is characterized by functionality for invoking the parallel interface in lieu of the client-side portion of the standard interface in response to such a procedure/function call. .And, by responding to a query for generating plural subqueries in the form of further procedures/functions to the standard server interface.
• the parallel interface can form part of an object code library for linking with a computer program including procedures/function calls for invoking the DBMS.
• the invention contemplates an improvement to a digital data processing system as described above, wherein the standard interface normally responds to insert/select queries by placing requested data from the database table means in a further database table (i.e., as opposed to merely printing the requested data or otherwise outputting it in text form or merely returning the data to the requesting program).
• the improvement of this aspect is characterized by generating the plural subqueries so as to cause the DBMS to place the data requested from each respective partition in the designated database table.
• a digital data processing system as described above can include functionality for executing multiple threads, or "lightweight processes," each for applying a respective subquery signal to the DBMS's interface element. Those threads can be executed in parallel on multiple central processing units, and can be serviced by multiple server processes within the DBMS that also execute in parallel.
• a digital data processing system of the type having a storage element (e.g., a disk drive or other random-access media 1 for storing and retrieving data records, as well as a DBMS having (i) a hashing element to effect storage of data records in "hash bucket' ' regions in the storage element, where each record is stored in a root hash bucket region corresponding to a hash function of a selected value of the data record or, alternatively, to effect storage of data records in an overflow hash bucket region associated with that root hash bucket region; and (2) an indexing element to index each stored data record for direct access in accord with a respective value of that data record.
• a storage element e.g., a disk drive or other random-access media 1 for storing and retrieving data records
• DBMS having (i) a hashing element to effect storage of data records in "hash bucket' ' regions in the storage element, where each record is stored in a root hash bucket region corresponding to a hash function of a selected
• the improvement is characterized by a scatter cluster retrieval element that responds to a request for accessing a data record previously stored via the hashing element, by invoking the indexing element to retrieve that record in accord with the index value thereof, where stored records have previously been indexed by the indexing element with respect to the same fields (columns) used by the hashing element.
• the hashing element stores the data records in hash bucket regions that are sized so as to create at least one overflow hash bucket region per root bucket region, and such that overflow bucket regions for a given root bucket region are distributed roughly evenly across different storage partitions.
• Another aspect of the invention provides a digital data processing system of the type described above, in which plural subcursor buffers are associated with each subquery signal for storing results generated by the DBMS's standard interface means in response to that subquery signal.
• a root buffer stores a then- current result, while a fetching element simultaneously assembles a final result signal based upon those results currently stored in selected subcursor buffers.
• results are taken from each of those buffers, they are emptied.
• a subquery is applied to the standard interface asynchronously with respect to demand for that buffer's contents in assembling the final result.
• the root buffer stores then-current results in a temporary table to be queried later by an aggregate query generated by the decomposition element.
• the invention provides a method for digital data processing paralleling the operation of the digital data processing system described above; i.e., "transparent" to the DBMS client other than by improved performance.
• Figure 1 depicts a preferred multiprocessing system used to practice the invention.
• FIG. 1 illustrates in greater detail processing cells and their interconnection within the processing system of Figure 1.
• Figure 3 A depicts a standard arrangement of processes and software modules utilized in digital data processor 10 without query decomposition and data access according to the invention.
• Figure 3B depicts a preferred arrangement of threads, processes and software modules utilized in digital data processor 10 for query decomposition and data access according to the invention.
• Figure 4 shows the operation of assembler 74B on results generated by the DBMS 76 and threads 78A, 78B, 78C in response to the subquery signals.
• Figure 5 depicts a preferred mechanism, referred to as “scatter clustering,” for storing and retrieving data from database 72.
• Figures UM 9-1 and UM 9-2 are used in connection with the discussion of the operation and use of a preferred query decomposition system according to the invention.
• Figures 26 - 1 through 26 - 3 are used in connection with the discussion of design provided in Database Note #26.
• Figures 61 - 1 through 61 - 3 are used in connection with the discussion of query decomposition for applications running on client workstations in Database Note #61.
• Figures 32 - 1 through 32 - 3 are used in connection with the discussion of the framework of rules for automating query decomposition in Database Note #32.
• Figures 36 - 1 through 36 - 7 are used in connection with the discussion of parallel cursor building blocks in Database Note #36.
• Figures 37 - 1 and 37 - 2 are used in connection with the discussion of parse tree requirements for query decomposition in Database Note #37.
• Figures 41 - 1 and 41 - 2 are used in connection with the discussion of query decomposition control structures in Database Notes #41.
• Figures 42 - 1 through 42 - 3 are used in connection with the discussion of upper tree parallelism in parallel cursors in Database Note #42.
• FIG. 1 depicts a preferred multiprocessing system used to practice the invention.
• the illustrated system 10 includes three information transfer levels: leve O, level: 1, and level:2.
• Each information transfer level includes one or more level segments, characterized by a bus element and a plurality of interface elements.
• levehO of the illustrated system 10 includes six segments, designated 12A, 12B, 12C, 12D, 12E and 12F, respectively.
• level: 1 includes segments 14A and 14B
• level:2 includes segment 16.
• Each segment of levehO i.e., segments 12A, 12B, ... 12F, comprise a plurality of processing cells.
• segment 12A includes cells 18 A, 18B and 18C;
• segment 12B includes cells 18D, 18E and 18F; and so forth.
• Each of those cells include a central processing unit and a memory element, interconnected along an intracellular processor bus (not shown).
• the memory element contained in each cells stores all control and data signals used by its associated central processing unit.
• Certain cells of the processing system 10 are connected to secondary storage devices.
• cell 18C is coupled with disk drive 19A
• cell 18D is coupled with disk drive 19B
• cell 180 is coupled with disk drive 19C.
• the disk drives 19A - 19C are of conventional design and can be selected from any of several commercially available devices. It will be appreciated that secondary storage devices other than disk drives, e.g., tape drives, can also be used to store information.
• FIG. 2 illustrates in greater detail processing cells and their interconnection within the processing system of Figure 1.
• plural central processing units 40 A, 40B and 40C are coupled, respectively, to associated memory elements 42A, 42B and 42C. Communications between the processing and memory units of each pair are carried along buses 44A, 44B and 44C, as shown.
• Network 46 representing the aforementioned level segments and routing cells, transfers information packets (passed to the network 46 over buses 48A, 48B and 48C) between the illustrated processing cells 42A - 42C.
• the central processing units 40A, 40B and 40C each include an access request element, labeled 50A, 50B and 50C, respectively.
• These access request elements generate requests for access to data stored in the memory elements 42 A, 42B and 42C.
• access requests signals generated by elements 50A, 50B and 50C is the ownership-request, representing a request for exclusive, modification access to a datum stored in the memory elements.
• access request elements 50A, 50B and 50C comprise a subset of an instruction set implemented on CPU's 40A, 40B and 40C. This instruction subset is described below.
• the central processing units 40A, 40B, 40C operate under control of an operating system 51 , portions 51 A, 51 B and 51 C of which are resident on respective ones of the central processing units.
• the operating system 51 provides an interface between applications programs executing on the central processing units and the system 10 facilities, and includes a virtual memory management system for managing data accesses and allocations.
• a preferred operating system for controlling central processing units 40A, 40B and 40C is a UNIX-like operating system and, more preferably, OSF/1, modified in accord with the teachings herein.
• the memory elements 40A, 40B and 40C include cache control units 52A, 52B and 52C. respectively. Each of these cache control units interfaces a data storage area 54A. 54B and 54C via a corresponding directory element 56A, 56B and 56C, as shown. Stores 54A, 54B and 54C are utilized by the illustrated system to provide physical storage space for data and instruction signals needed by their respective central processing units.
• Figure 3 A depicts a standard arrangement of processes and software modules utilized in digital data processor 10 without query decomposition and data access according to the invention.
• Figure 3B depicts a preferred arrangement of processes and software modules utilized in digital data processor 10 for query decomposition and data access according to the invention.
• An. initiating process 70 generates a query for accessing data stored in relational database 72 having data partitions 72A, 72B, 72C.
• the query is generated in a conventional format otherwise intended for a conventional DBMS 76.
• that conventional format is SQL
• that conventional DBMS is the ORACLE7 Database Management System (hereinafter, "ORACLE” or "ORACLE Version 7”) of Oracle Corporation.
• ORACLE ORACLE7 Database Management System
• DBMS such as ORACLE Version 7
• ORACLE Version 7 DBMS
• Element 74A (responsible for decomposing the query) routes queries not susceptible to decomposition to DBMS 76, but for a decomposable query it generates a set of subqueries, each of which is based on the initial query but which is directed to data in one or more respective of the partitions 72A, 72B, 72C of database 72. Then element 74A initiates and invokes threads 78A, 78B, 78C, which initiate execution of the subqueries.
• PUPI parallel user program interface
• the subqueries corresponding to threads 78A, 78B, 78C are routed to the user program interface ("UPI" or "standard interface") of DBMS 76 (in lieu of the intercepted query), as shown in the drawing.
• UPI user program interface
• Multiple subqueries are preferably applied to the UPI of DBMS 76 in parallel with one another, thus capitalizing on the database partitions and on the multiprocessing nature of the preferred digital data processing system 10.
• Each thread routes its subquery to a separate server process in DBMS 76.
• the DBMS 76 responds in the conventional manner to each subquery by generating appropriate requests (e.g., a disk read) for access to the database 73 and, particularly, for access to respective partitions of that database (unless the data requested is already in memory).
• Data retrieved from the database 72 in response to each subquery is processed in the normal manner by DBMS 76 and is routed to processes 76A, 76D and 76G.
• Those responses are routed to parallel interface assembly section 74B which assembles a response like that which would have been generated by the DBMS 76 had the intercepted response been applied directly to it.
• the assembled response produced by assembly section 74B is generally returned to the initiating process 70 more quickly than that which would have been generated by the DBMS 76 had the intercepted query been applied directly to it. This is a consequence of decomposition of the intercepted query and its parallel application to the UPI of DBMS 76. It is also a consequence of the architecture of the underlying multiprocessor, which permits multiple server processes to run simultaneously. Though it will be appreciated that, even when running on a uniprocessor, the concurrent execution of multiple subqueries could speed access where there is overlapping I/O and CPU processing.
• the decomposer 74A generates subqueries based on the conventional- format query intercepted from the initiating process. For simple, single-table queries, the decomposer 74A generates corresponding subqueries by duplicating the query and appending a predicate for matching records in the corresponding table partition.
• rowid has three parts, the last of which indicates the partition number.
• Other subqueries would be of similar form, with changes to the partition numbers referenced in the rowid predicates.
• the decomposer 74A For queries joining two or more tables, the decomposer 74A generates corresponding subqueries by duplicating the query and appending a predicate for matching records in the corresponding table partition of the driving table, which is selected by the decomposer 74A based on the access strategy chosen by the query optimizer portion 76B of the DBMS 76.
• information from the optimizer 76B including possible tables to be chosen as the driving table, can be obtained from data files generated by the DBMS 76 in connection with the query, and accessed by use of the "EXPL.A-IN" command.
• Figure 4 shows the operation of assembler 74B on results generated by the UPI of DBMS 76 and threads 78A, 78B, 78C in response to the subquery signals. More particularly, the drawing shows that for intercepted queries that call for aggregate data functions, element 74C performs a like or related data function of the results of the subqueries. Thus, for example, if the intercepted query seeks a minimum data value from the database table ⁇ and, likewise, the subqueries seek the same minimum value from their respective partitions — then element 74C generates a final result signal representing the minimum among those reported to the assembler 74B by the DBMS 76 and threads 78A, 78B, 78C.
• element 74C generates an average table value through a weighted average of the reported subquery results.
• the decomposer 74A generates subqueries requesting related functions of the data, e.g., the sum, count and sum of the squares of the data.
• Such aggregate processing is preferably applied to, for example, intercepted queries requesting (i) a minimum or maximum of an item in the records (ii) an average of selected items, (iii) a standard deviation and variance of selected items, and (iv) a sum and a count of selected items.
• element 74D For intercepted queries that call for non-aggregate data functions, element 74D generates a final result signal by interleaving the results of the subqueries.
• element 74D generates a final result signal by interleaving (in the specified sort order) the items presented in the results reported to the assembler 74B by the DBMS 76 and threads 78A, 78B, 78C.
• Other non-aggregate queries involving, for example, (i) a distinct value of an entire result row, (ii) a nested selection of items, and/or (iii) a correlated selection of items are processed accordingly.
• the decomposer 74A For queries involving grouping operations, the decomposer 74A generates corresponding subqueries by duplicating the query, along with the grouping clause in its predicate list. For each group, data retrieved by the DBMS in response to those subqueries is placed in a temporary table. For that group, the assembly section 74B generates and passes to the DBMS a "group by" combining query to be applied to the temporary table. The results of those queries are returned to the initiating process 70 in lieu of the response that would have been generated by the DBMS 76 had the intercepted query been applied directly to it.
• the decomposer 74A and assembly section 74B operate in the manner describe above, except, that the "having" clause is not included in the subqueries. That clause is, however, incorporated into the combining queries that are executed on the temporary table.
• Figure 5 depicts a preferred mechanism, referred to as “scatter clustering” or “small bucket hashing,” for storing and retrieving data from database 72.
• the mechanism combines cluster-storage and index-access techniques to disperse and retrieve data records from storage media 80A, 80B, 80C (e.g., disk drives) upon which database 72 is contained.
• Data records are stored using the DBMS's 76 cluster-storing capabilities, based on a conventional hash function of its key value (as generated by element 76B), and using a smaller-than-normal bucket size chosen to insure that at least one overflow hash bucket will be created for each root bucket. More preferably, the bucket size is chosen to insure that hash buckets are spread over storage devices to maximize the potential for parallel access.
• Each stored record is simultaneously indexed for direct access in accord with the same key value(s) used by the hash function.
• the DBMS 76 responds to requests to store data records by invoking the hashing element 76B to store those data records in accord with a hash on their key values.
• the DBMS 76 also populates index 76C by invoking DBMS's 76 corresponding indexing functionality.
• the decomposer 74A When accessing data records, the decomposer 74A generates subqueries specifying that requested data records are to be accessed via the index element 76c, not the hashing element 76b.
• the database 72 is organized to achieve the best mix of I/O parallelism and hit ratio.
• I/O parallelism the more threads 78A, 78B, 78C can be used, in parallel, to initiate data retrievals.
• hit ratio the greater the number of relevant records each thread 78A, 78B, 78C gets with each retrieval.
• the aforementioned scatter clustering technique achieves a good mix of I/O parallelism and hit ratio. It does this by storing the data records using the DBMS's 76 hash- based storage techniques with abnormally small bucket size, thereby distributing small bucket-size clusters of related information around the disk, and by retrieving the data using the DBMS's indexing mechanism.
• the invention contemplates operating on database tables with any plurality of partitions. .And, that the invention contemplates using any plurality of subqueries (and corresponding threads) to execute retrievals against those partitions. Moreover, it will be appreciated that the invention does not require that the number of partitions and subqueries be identical. Preferably, the number of subqueries (and threads) is an integral divisor, greater than one. of the number of partitions. Thus, for example, three subqueries can be beneficially run against six partitions.
• ORACLE database management system (which is commercially available from Oracle Corporation and can be adapted for operation with a number of computer systems, including the Kendall Square Research Corporation multiprocessors). Each of the sections which follow is identified by a “Database Note Number " (or DBN #). Those identifications are used to cross-reference the sections, typically, in lieu of their titles. The inventors are alternatively referred to as “we, " “ “I, " “KSR, " and other like terms.
• KSR QD The KSR Query Decomposer
• Fig. UM 9-1 shows the basic steps of query decomposition:
• KSR QD intercepts it and generates subqueries against existing data partitions on disks.
• KSR QD is compatible with the ORACLE architecture (transparent to your applications), while at the same time leveraging ORACLE'S existing optimization strategies.
• Fig. UM 9-2 is a conceptual view of the ORACLE program interface.
• the UPI User Program Interface
• the UPI is the common point of access to the ORACLE kernel for all applications.
• the KSR QD implementation sits between the UPI external interface and the UPI program library, transforming a serial interface into a parallel one.
• KSR QD Decisions made automatically by KSR QD include the following:
• KSR QD will not notify you whether your query was decomposed, because everything is meant to happen transparently—unless you want it to be otherwise. If you want more details, you should issue an EXPLAIN PLAN command to see the actual execution plan for your query. If it is being decomposed, a row of the execution plan will be labeled KSR PARALLEL EXECUTION. For further information, see Section 11.4.
• End users do not need to be aware of KSR Query Decomposer activities.
• the database administrator must set up a default environment that enhances the performance of queries normally issued in your application environment.
• the DBA's most impo ⁇ ant step is to distribute data in a way to take advantage of the parallelism provided by KSR QD.
• KSR QD parallelizes a query by dividing it into subqueries, each of which uses a rowid range predicate on the driving table to specify one or more files to which that query's reads will be restricted.
• the approach depends on partitioning tables across files on multiple disk drives, so the files can be read in parallel.
• the DBA partitions large database tables over multiple disks to maximize parallel reads from disk. There may be tens or even hundreds of partitions for a given table. This is described in Chapter 10.
• the only change in the subqueries is the addition of the rowid range predicates.
• the first subquery will read only the blocks of the EMP table which are in file 1 : the second, file 2; the third, file 3. This is an example of decomposing a full-table scan.
• the overall query needs to read all blocks of the table, and you gain near-linear speedup by reading the separate files across which the table is partitioned in parallel. The total number of reads is not changed, but they occur in parallel.
• Query decomposition also can work with queries that use an index. Suppose you have the following query:
• the only change in the subqueries is the addition of rowid range predicates.
• the subqueries read the index blocks concurrently and process them in parallel. Index blocks are cached, so the index blocks should be read in from disk only once.
• a subquery finds an index entry for DEPTNO 5, however, it will examine the rowid stored in that index entry to see whether it falls within the range for that subquery. Only if it does will that subquery read the data page containing the row with that DEPTNO value and rowid.
• ORACLE The component of ORACLE whose goal is to choose the most efficient way to execute an SQL statement.
• a table must be partitioned to be used as a partitioning table by KSR QD.
• the view definition must not contain a GROUP BY clause, an aggregate function, or any construct not listed above.
• KSR QD will not alert you as to whether decomposition has taken place. You will receive the results of your query without any interrupting messages. KSR QD always runs transparently to your operation.
• KSR QD supports the following products in a local client/server environment:
• KSR QD is a separate software component that interacts seamlessly with ORACLE for KSR. It is developed and distributed by Kendall Square for use on KSR/Series computers. KSR QD is not expected to impact general database administration procedures.
• ORACLE installation includes KSR QD components and KSR QD files in the correct directories.
• Section 10.1 "General KSR QD Start-up Information" - This section provides information for performing a post-installation assessment to make certain all KSR QD files are in place. It provides a procedure to establish the views and tables necessary for KSR QD operation.
• libora.qd.a This is a customized libora.a file containing all the KSR QD code. There are KSR QD modules and modified UPI modules in this copy of the library. It is located in $ORACLE_HOME/lib. sqlplus.qd This is SQL*Plus built with KSR QD linked in. It is located in $ORACLE_HOME/bin.
• KSR QD Product Overview describes how KSR QD is related to the ORACLE UPI.
• the programs comprising the UPI normally are found in the libora.a library.
• the libora.qd.a file in your distribution is the modified ORACLE libora.a file containing KSR QD code.
• the sqlplus.qd executable you received was linked with this modified libora.qd.a file. . Any new applications automatically will include KSR QD when they are built with libora.qd.a.
• KSR_.ALL_TABLE_FILES This view must exist in a database for query decomposition to be possible. It permits KSR QD to get a count and list of the files into which a given table is partitioned. It is owned by SYS and must have SELECT privilege granted to public (or. at the DBA's discretion, only to those users permitted to use KSR QD). It is created, with appropriate grants, by the SQL script
• KSR_PLAN_TABLE This is the default plan table for the temporary execution plans generated by KSR QD when it calls EXPL.AIN PLAN. This plan table has the same format as a standard ORACLE plan table, is owned by SYS. must have SELECT.
• Standard ORACLE includes a script called utlxplan.sql, which any user can employ to create a private plan table (named PLAN_TABLE by default). For a given user. KSR QD will use PLAN_TABLE if it exists: otherwise, it will use KSR_PLAN_TABLE. If KSR_PLAN_TABLE does not exist in a given database, only users with a private plan table explicitly named PLAN_TABLE can use KSR QD in that database.
• KSR_DISABLE_QD This is a public synonym for the table DUAL: which is transparently created when the script $ORACLE_HOME/rdbms/admin/ksr_disable_gd.sgl is run. It is transparently dropped when you subsequently run the script
• KSR QD is enabled for that database.
• KSR QD creates a temporary table when it decomposes a query containing aggregate functions. It uses this table to gather partial aggregate results from the parallel subqueries.
• a KSR QD temporary table has a name beginning with QDIT (for Query Decomposer Intermediate Table) with a unique 9-digit suffix. It is owned by the user executing the query and is created in that user's TEMPORARY tablespace (which defaults to SYSTEM).
• a user must have the ability to create tables in his or her TEMPORARY tablespace to apply query decomposition to queries containing aggregate functions.
• the ALTER USER statement can be used to assign a TEMPORARY tablespace for a particular user. The user
• RULE 26 can be enabled to create tables in that tablespace by either granting the RESOURCE role to the user (which enables creating tables in any tablespace) or using the QUOTA clause of the ALTER USER statement to grant the user a storage quota in a specified tablespace. If a quota is used, it must be sufficient to permit creating a table with default storage attributes. The minimum necessary quota varies depending on the database configuration (e.g., in a database with DB_BLOCK_SIZE of 8192. the minimum quota required to create KSR QD intermediate tables is 40 KB).
• Each file of the tablespace constitutes a KSR QD "partition" for each table.
• ORACLE extents are automatically allocated in a balanced manner across the tablespace as data is loaded.
• a database may contain multiple partitioned tablespaces. each of which may have a different number of partitions.
• a table created in a partitioned tablespace potentially has the same number of partitions as the tablespace.
• KSR QD will consider as partitions only those files which contain at least one extent of the table in question. For example, if a tablespace has 20 files, but a particular table in the tablespace has extents in only 10 of those files, KSR QD considers that table to have 10 partitions, not 20. This means KSR QD generates at most 10 parallel subqueries for a query with this table as its partitioning table.
• the maximum effective degree of partitioning of a tablespace is limited by the number of disk drives, because KSR QD does not benefit significantly from using more than one partition on the same disk, for a given tablespace.
• the number of processors does not impose a hard limit on the degree of partitioning, because multiple KSR QD threads may run on a given processor in a time-sharing fashion.
• a gradual decline of added speedup per additional degree of partitioning will occur as the degree of partitioning exceeds the number of processors. It is recommended the degree of partitioning not exceed the number of processors for the initial run.
• the degree of partitioning of a tablespace determines the maximum potential degree of parallelism KSR QD can use for queries whose driving table is in that tablespace.
• the actual maximum degree of parallelism is equal to the number of files in the tablespace that contain at least one extent of the table.
• KSR QD can be made to use a smaller degree of parallelism than the maximum by using the KSR_MAXPARTITIONS environment variable or query directive, as explained in Section 11.1, "KSR QD Environment Variables.”
• KSR QD divides its work according to the partitions of one table, the "driving" table of the join (see Section 11.4, "Kendall Square Extensions to EXPLAIN PLAN,” to find out how to determine which is the partitioning table for a given query).
• a given KSR QD parallel subquery is responsible for fetching rows from a particular partition of the partitioning table, and for each of those rows, finding the matching rows from the other tables. If more than one of the tables being joined is partitioned across the same disks, one parallel subquery looking for matching rows from a non-partitioning table may contend for the same disk with another parallel subquery looking for rows matching its own partition of the partitioning table. Using a cluster solves this problem, because the rows of non-partitioning tables usually are in the same block (and thus the same partition) as the corresponding rows of the partitioning table to which they join.
• a useful rule of thumb when creating tablespaces to be used for query decomposition is to define all files of your tablespace to be the same size. This will provide even data distribution across all the files.
• KSR QD Some useful rules of thumb when creating tables to be used by KSR QD include the following:
• KSR QD can effectively decompose queries using an index to retrieve rows from the driving table (although speedup will not tend to be as dramatic as for full-table scans), but the reading of the index itself is not decomposed (i.e.. each parallel subquery reads the same index blocks). Placing the index in a separate tablespace avoids disk contention between index reads by one parallel subquery and table reads by another parallel subquery.
• KSR QD is most effective when target data is evenly distributed among all the files of a tablespace. This is so because KSR QD divides the work of a query so each parallel subquery covers the same number of table partitions as nearly as possible. For example, if a given table has 20 partitions (i.e.. it has at least one extent in each of 20 files) and the user has specified a maximum degree of decomposition of 10 (using the KSR_MAXPARTITIONS directive, described in Section 11.2). each parallel subquery will be responsible for retrieving data from two partitions. If no maximum degree of decomposition has been specified, each parallel subquery will retrieve data from one partition. If each of the 20 partitions contains roughly the same amount of data, each parallel subquery has roughly the same amount of work to do. Therefore, when they are executed in parallel, all the parallel subqueries will complete at about the same time. The execution time for the overall query is always slightly
• a given file contains no extents for a given table, that file is not considered a partition of that table by KSR QD. If, for example, a table in a 20-file tablespace has equal amounts of data in each of 10 files and has no extents in the remaining 10 files, a query on that table can be decomposed into 10 parallel subqueries. If the same data were instead distributed among all 20 files, a higher degree of parallelism, and thus a higher degree of speedup, would be possible.
• data blocks of the table are read only if they contain at least one row which satisfies the predicates applied to the index (e.g.. if an index on DEPTNO is used, only those data blocks are read which contain at least one row with a DEPTNO value in the range specified in the query's WHERE clause). Therefore, for a given indexed query, the skew of distribution of data blocks containing rows falling within the specified ranges on the indexed columns is important. This type of skew is more difficult to avoid for two reasons: First, one must predict the most frequently queried value ranges to determine the ideal data distribution.
• Scatter clustering can be quite valuable.
• the goal of scatter clustering is to create a hashed cluster with a large number of overflow blocks, each of which contains records with the same key value.
• KSR QD can ameliorate the problem by splitting up the load in parallel but if the index does not provide speedup relative to full-table scan without query decomposition, it will not provide that speedup with query decomposition either.
• each index entry consists of a key value/rowid pair, which points directly to the block containing the row in question. It also can be used for range predicates as well as direct match predicates.
• ORACLE optimizer When presented with a query with an exact-match predicate on the hash-key columns, the ORACLE optimizer will choose hashed access rather than using the index on those same columns, because under normal circumstances, hashed access would unquestionably be faster.
• KSR QD notices (in the execution plan) ORACLE has chosen hashed access and there is a regular index which has all the columns of the hash key as its leading columns. it generates an INDEX optimizer hint in the parallel subqueries. coercing the ORACLE optimizer to use the regular index rather than hashing. Since the parallel subqueries have rowid range predicates, this regular indexed query can be decomposed like any other. Because the data is clustered on the same column values with blocks for each cluster-key value well distributed among the files of the partitioned table, many fewer blocks need to be read than if this were not a hashed table.
• HASHEDJTABLE a partitioned table hashed on the column HASHKEY_COLUMN. and there also is an index called REGULAR_INDEX on the same column.
• the regular index optionally may contain additional trailing columns beyond those which match columns of the hash key. This means it can be used to further restrict the rows read. according to additional predicates in the querv. This could be particularly useful to give added flexibility, because a hash key must be chosen by a DBA before a table is created. Once the hashed table is populated, it requires a complete reorganization to add additional hash-key columns. It is much easier, however, to add columns to an index (or replace it with a different index) without affecting the data itself. So. if additional, frequently used selection criteria are identified after a hash table exists, these columns can be added to the regular index.
• KSR QD must choose one of these indexes arbitrarily for ORACLE to use. In this event, however, the user optionally may choose the index by placing the INDEX optimizer hint in the original query. KSR QD always leaves any hints from the original query in the parallel subqueries to provide the user this extra degree of customized control over optimization when needed.
• KSR QD determines whether you are running KSR QD. If your SQL*Plus has KSR QD linked in, then when you run SQL*Plus, you will see an indication KSR QD is running (e.g., KSR QD version 1.0).
• KSR_NOPARTITION environment variable or directive If you are running KSR QD, try the KSR_NOPARTITION environment variable or directive. This disables KSR QD as explained in Section 11.1.
• KSR QD intermediate tables are dropped automatically upon completion of query execution, but in exceptional cases (e.g.. if an application is canceled during query execution), they may not be dropped.
• a utility called qdcleanup is provided for easily removing KSR QD temporary tables.
• ORACLE To run qdcleanup. ORACLE must be started and the database must be open.
• 0 KSR QD is intended to enhance the performance of complex decision-support queries executing over large databases. It works in coordination with the ORACLE optimizer to automatically provide what it believes to be the optimal strategy for running your query, based on what it knows about your application and the structure of your data.
• KSR QD works on your behalf without you even being aware that it is there, except for the performance enhancements you see in the execution of your queries.
• KSR QD can not be 5 expected to deduce. To further optimize your queries, that information may be made available to KSR QD. This is the purpose of the KSR QD user interface.
• This control is provided in a granularity allowing for individual customization:
• KSR QD control variable being used as an environment variable. See Table 11-1 for a list of KSR QD control variables.
• Variables can be unset by issuing the unsetenv command with a null value.
• KSRQD_ARRAY_S If set to an unsigned integer, indicates the array size for array fetch
• KSR QD array fetch minimizes the number of client-server messages by fetching multiple rows per message in arrays.
• KRSQD _ARRAY_SIZE must be set to a non-zero value. The default value is zero and means no array fetch. A value of one also means no array fetch, although the array fetch mechanism would be employed.
• KRSQD_ARRAY If set to an unsigned integer, indicates the array fetch threshold. A JTHRESHOLD given parallel subquery starts using array fetch only after this many rows have been fetched. The default value is 0. meaning start right away (i.e., before the first row is fetched). If KSRQD_ARRAY_SIZE is zero, there will be no array fetching.
• KSRQD_DISPLAY If set to any value, causes KSR QD timing information to be JTIME computed and displayed. By default, this variable is not set. See Section 11.6 for more information.
• KSRQD_DISPLAY JTIME is also _FILE set. indicates the file where timing information is to be written. By default, this variable is not set. and all information is displayed on standard output.
• KSRQD If set to a positive integer, determines the maximum degree of _MAXPARTITION parallelism for queries (even if the partitioning table has more
• the actual degree of parallelism for a query will be the minimum of this value (if set), and the number of partitions in the partitioning table of the query.
• the default value is the actual number of partitions. This parameter must be greater than zero.
• KSRQD If set to any value, disables KSR QD within the user environment. _NOPARTITION It cannot be overridden by a KSRQD_MAXPARTITIONS query directive. Unsetting or removing this variable re-enables KSR QD within the local shell. By default, this parameter is not set. 11.2 KSR QD Directives
• a directive comment is any comment which immediately follows the SELECT keyword (with or without intervening white space), and has a plus sign (+) immediately after the open-comment delimiter (/ * or — ).
• a directive comment may contain zero or more KSR QD directives separated by blanks.
• KSR QD will use a maximum of 10-way parallelism, and it will use array fetching to fetch 100 rows at a time, per parallel subquery.
• the full (emp) ORACLE optimizer hint will be treated as a comment by KSR QD but passed along to the ORACLE optimizer in the parallel subqueries, forcing ORACLE to use a full-table scan on the emp table.
• KSR QD can be enabled/disabled at three levels:
• KSR QD • Per query (via KSR QD directives) Disabling KSR QD at a higher level prevents enabling it at a lower level (i.e. if KSR QD is disabled in a database, it cannot be enabled within user environments for queries on that database: if it is disabled in a user environment, it cannot be enabled for any query run in that environment). At each level, the default is for KSR QD to be enabled, unless it is disabled at a higher level.
• Enabling/disabling KSR QD is largely transparent except insofar as it affects performance. The same queries will return the same results. Queries containing explicit KSR QD directives can be run in an environment where KSR QD is disabled: the KSR QD directives will simply be ignored, and the query will not be decomposed.
• KSR QD is disabled will not show parallel execution as one of the steps for any query.
• KSR QD When KSR QD is used on a query containing aggregate functions or a GROUP BY clause, there is an implicit commit when the cursor for the query is opened, and another implicit commit when it is closed (caused by KSR QD creating and dropping the temporary table used to collect aggregate results from the parallel subqueries). Users are advised not to use KSR QD on queries in transactions having uncommitted updates unless the implicit commits generated by KSR QD are perfectly acceptable.
• KSR QD is enabled by default in a newly created database, provided the scripts described in Section 10.1 have been run. Any user with DBA privilege may disable query decomposition for that database by executing the following SQL script:
• a DBA may re-enable KSR QD for that database as follows:
• KSR QD is enabled by default in a user environment, unless the user accesses a database for which KSR QD is disabled.
• KSR QD may be disabled in a user environment by setting the environment variable KSRQD_NOPARTITION to any value.
• KSR QD may be re-enabled by unsetting that environment variable.
• KSR QD is enabled by default for a query, unless it is disabled at the database or environment level. It may be disabled by specifying the KSRQD_NOPARTITION directive within the query. KSRQD_NOPARTITION overrides any other KSR QD directives specified in the same query (so a user may add it and remove it without making any other editing changes to the query). When KSR QD is disabled for a query, the query will not be decomposed. When KSR QD is enabled, it may be decomposed at the discretion of KSR QD. based on whether analysis of the query indicates decomposition is likely to be effective.
• EXPLAIN PLAN When query decomposition is enabled and EXPLAIN PLAN is invoked for an SQL query, if the query in question would be decomposed, EXPLAIN PLAN produces an execution plan that includes a row providing information about how KSR QD is used for this query. The other rows of the plan show the optimization strategy ORACLE has chosen for executing the parallel subqueries.
• OBJECT DWNER Indicate the owner of the partitioning table.
• OBJECT_NAME Indicate the name of the partitioning table.
• SEARCH_COLUMNS Indicate the degree of partitioning (i.e.. the number of parallel subqueries).
• PARENT ID Indicate the ID of the logical parent of this row. Set to null for the special KSR QD row (PARENT D is always null for the row whose ID is 1). 11.4.1 EXPLAIN PLAN Examples
• the first EXPLAIN PLAN statement is for a simple query (no ORDER BY or GROUP BY clauses, joins, or aggregates).
• the emp table has 20 partitions.
• the second EXPLAIN PLAN statement is for a query requesting DISTINCT values.
• KSR QD uses a MERGE combining function in this case.
• EXPLAIN PLAN SET STATEMENTJD 'query2' FOR SELECT DISTINCT LNAME FROM EMP: SELECT OPERATION. OPTIONS. OBJECT_NAME. ID. PARENT JD.
• the third EXPLAIN PLAN statement is for a query joining two tables and has aggregation and grouping.
• KSR QD chooses emp as the partitioning table because it is the driving table in ORACLE'S plan for the join.
• ORACLE uses a nested-loops join and uses the unique key pk_dept to retrieve dept in the parallel subqueries.
• ORACLE hints are allowed in SELECT, DELETE, or UPDATE statements, but since DELETE and UPDATE statements are never decomposed, KSR QD directives only have meaning after a SELECT statement.
• ORACLE also permits non-hint comments to be interspersed with hints. From ORACLE'S perspective, the KSR QD directives are comments, while from KSR QD's perspective. ORACLE hints are comments.
• KSR QD directives and ORACLE hints are semantically independent.
• the presence of ORACLE hints in a query does not affect KSR QD's decision whether to decompose the query, except insofar as the hints yield an optimizer plan appropriate for decomposition.
• the presence of a KSR QD directive in a query has no affect on ORACLE'S optimization. Note, however, that the presence of any ORACLE hint other than NOCOST implies use of the cost-based optimizer, even in cases where statistics are not available for any table in the query. Without the hints, the heuristic optimizer will be used.
• KSR QD parser does not need to understand ORACLE hints, and it ignores them along with anything else within a comment that is not a KSR QD directive.
• KSR QD passes the input query to the ORACLE EXPLAIN PLAN facility, the latter will take any hints into account in producing the execution plan.
• KSR QD does not need to know whether the plan was influenced by hints, only what the plan is.
• any hints are replicated in the subqueries. This ensures ORACLE chooses the same strategy for executing the subqueries as when EXPLAINING the input query (KSR QD relies on the assumption this will be the case).
• KSR QD always partitions on the driving table of a join, provided that table is retrieved in a manner facilitating partitioning (indexed and full-table scans permit partitioning).
• ORACLE hints provide a way to control the choice of driving table and the manner of retrieving that table, and thereby provide a way to control the choice of the partitioning table.
• ORACLE optimizer 's default strategy for a query may not always be the most efficient strategy to use in conjunction with KSR QD.
• ORACLE hints provide a means for users to second-guess the ORACLE optimizer, based on their ability to take KSR QD into account and coerce a different choice of driving table.
• the timing utility can be enabled at the session level with an environment variable.
• the timing utility is accessed through SQL*Plus or a KSR QD linked Pro*C program.
• KSRQD JSPLAY JTIME controls display of KSR QD's timing information. If it is set. KSR QD measures and displays timing statistics for various steps in the execution of a query.
• KRSQD_DISPLAY_FILE Setting the environment variable KRSQD_DISPLAY_FILE to a filename causes the timing statistics to be placed in that file. If this is not set. the information is sent to standard output.
• the timing utility provides information about five rocessing phases, as shown in Table 1 1-2.
• Table 11-2 The timing utility provides information about five rocessing phases, as shown in Table 1 1-2. Table 11-2
• Preparing Query Full Preparation time Time to analyze and prepare a query (including parsing the query, performing semantic analysis, checking whether the query would benefit from decomposition, and generating data structures needed for later KSR QD processing)
• Timing statistics are provided only for significant steps in the KSR QD process.
• KSR Query Decomposer Version 0.1.4.10
• Array processing can improve performance by reducing the number of calls from an application to ORACLE. Array processing allows an application to fetch multiple rows with only a single call to the ORACLE kernel. This is an important performance technique you can use in your applications.
• An array size of 1,000 reduces the number of calls to 10. This performance gain is relatively small compared to the gain from increasing the array size from 1 to 100. Increasing the array size to 1,000 also increases the amount of memory needed to hold the array.
• ORACLE application tools can take advantage of array processing. These tools include:
• SQL*Plus for example, uses array processing automatically to return many rows from the database at once. It allows you to control the number of rows returned at a time through the SQL*Plus variable ARRAYSIZE (see “SOL *Plus User's Guide and Reference”). You can improve the performance of your queries by setting ARRAYSIZE appropriately.
• the trade ⁇ off is SQL*Plus needs a larger buffer to store the rows of your query if it is fetching them using a bigger array size.
• Array processing can be used at the query level, the subquery level (by KSR QD), or both. Both query and subquery array processing give you an analogous way to improve your query performance, but they operate independently.
• KSR QD Users of KSR QD require the ability to create tables in their TEMPORARY tablespace to use KSR QD on queries using an intermediate table (all queries containing aggregate functions).
• TEMPORARY tablespace is SYSTEM, but this can be altered by the TEMPORARY TABLESPACE clause of the ALTER USER statement.
• the TEMPORARY tablespace is used by ORACLE to create temporary segments (e.g., for sorts), and this does not require any special privilege on the user's part.
• KSR QD intermediate tables while temporary from KSR QD's stand ⁇ point, are ordinary tables as far as ORACLE is concerned. The user must have the privilege to create tables.
• a user can be enabled to create tables in a given tablespace either by granting the RESOURCE role to the user, or by using the QUOTA clause of the ALTER USER state ⁇ ment to grant the user a storage quota in a specified tablespace. If a quota is used, it must be sufficient to permit creating a table with default storage attributes. The quota also must be sufficient to hold all the rows of the intermediate table, which
• Queries using an intermediate table may run out of space for the intermediate table.
• the KSR QD intermediate table stays quite small, because at any given time it contains at most one row for each parallel subquery (i.e., number of rows ⁇ degree of partitioning).
• the intermediate table can potentially grow much larger: queries which contain both a GROUP BY clause and an ORDER BY clause. For such queries, the maximum number of rows in
• the intermediate table equals the degree of partitioning times the number of groups in the query result. It is very unlikely this will exhaust the space in the tem ⁇ porary tablespace, because it is less temporary space than ORACLE needs for each parallel subquery (which already will be freed up before the stage of query execu ⁇ tion at which the KSR QD intermediate table is populated). It is possible a user's personal quota will be exhausted, however, since ORACLE'S temporary segments for sorts are not governed by the quota, but the KSR QD intermediate table is gov-
• KSR QD decides whether to decompose a query based on analyzing the query and ORACLE'S explain plan. If KSR QD decides not to decompose, it silently lets ORACLE execute the query, as if KSR QD were not present.
• KSR QD requires the driving table of a query have at least KSRQD_ INPARTITIONS partitions and be retrieved by either indexed scan or full-table scan (not hash access), to decompose a query.
• KSR QD There is a performance overhead for KSR QD.
• KSR QD Cost to check whether KSR QD is enabled. This cost is payed for all queries (whether or not KSR QD is enabled) when running a client application (e.g., SQL*Plus) with KSR QD linked in.
• client application e.g., SQL*Plus
• KSR QD intermediate tables may not get dropped in some cases, if errors occur during query execution.
• a KSR QD intermediate table is created when a user executes a query containing aggregate functions, using KSR QD. It is owned by that user, -.. and has a unique name starting with QDIT (for example, QDIT169538333).
• KSR QD is supposed to drop the intermediate table at the end of a query, or if any error occurs during query execution, but in some cases (particularly if the appli ⁇ cation executing the query crashes), this does not occur.
• the presence of these tables is relatively benign, but eventually they may exhaust a user's storage quo ⁇ tas, so it is best to drop them. They may be dropped using the DROP TABLE state ⁇ ment or running the qdcleanup program (see Section 10.3.2).
• Described below is a "front-end" to the ORACLE database management sytem that can parallelize a reasonable class of decision support queries without requiring major changes to the DBMS itself.
• query decomposition in which parallel subqueries are submitted to the DBMS, matching the physical data declustering already permitted through table "striping" in ORACLE.
• query decomposition is applicable to a very significant class of decision support queries, has excellent potential for performance gain for this class, and will be achievable with reasonable engineering effort at KSR.
• this is an approach that can eventually benefit all users of ORACLE on parallel and shared-memory multiprocessor machines.
• Section 2 (of this database note) describes our query decomposition approach in more detail, including a simple example.
• Section 3 discusses the critical problems that need to be solved to implement this approach.
• Section 4 analyzes the applicability of query decomposition with respect to a number of sample queries.
• ORACLE permits the DBA to specify table "striping" in the CREATE TABLESPACE command.
• a large table may be broken up into a number of files, spread across multiple disks. This is mainly viewed as an OLTP-oriented technique, aimed at optimizing random access to tables. Depending on how the file extents are populated, there may be some degree of data skew in terms of tuple distributions. However, striping is effectively a physical partitioning that we believe is adequate to support query decomposition.
• Query decomposition is done by making a number of copies of the original query, and then appending additional predicates to each subquery to make it match one of the existing partitions of one of the tables in the query. These subqueries are then executed in parallel. Finally, a combining query (or function) over the subquery results produces the result of the original query. Most commonly, this is the union over the subquery results.
• the degree of parallelism is limited by the number of physical partitions of the partitioning table, but not by the inherent parallelism in the query, as is the case for inter-operator parallelism.
• it should be possible to leverage our initial work by basing query decomposition on hash-partitioned data, or by decomposing queries according to other criteria than matching data partitions.
• in_interval(t.FILEID,i) is true for tuples in the ith group of files for table t.
• the predicate translates into the appropriate conditions on FILEIDs (i.e., on SUBSTRft. ROWID, 15,4)), as was shown in the example in section 2.
• index(t.x) means there exists an index on the J attribute of table t.
• a nested loops join, with a as the outer table and b as the inner will be written NU(a,b).
• a merge join of a and b will be written MJ(a,b).
• Queries Ql through Q12 are against tables a, b, and c.
• tables a, b, and c By starting with simple, abstract queries and adding increasingly complex conditions, we hope to better characterize the applicability of the query decomposition approach. Given our decision-support orientation, we have considered just read-only queries, and not data manipulation statements that do updates, deletions, or modifications.
• index(a.x) Assume index(a.x). According to ORACLE, the index will be used to apply the predicate on a.x and the predicates on FILEID. This effectively parallelizes the subqueries. If there is no index, then the query can be treated as was Ql, with the a.x predicate being checked against all rows scanned by each subquery.
• index (a. z) and index (b.z) If index (a. z) and index (b.z), then one of a and b will be chosen by the optimizer as the outer table, and should also be used as the partitioning table. By default, the optimizer will pick the smaller table as the outer one. However, if the smaller table has very few partitions, it is preferable to direct the optimizer to choose the larger table as the outer one, and to use it as the partitioning table as well. In either case, the subqueries can be effectively parallelized.
• ORACLE will generate MJ(a,b), and will sort both a and b before performing the join. While the query can still be decomposed into subqueries, say Q3/a/i, the problem is that each subquery will sort the entire b table. The likely result is relatively little performance speedup. Note that a parallel hash join operator would help in this case, if it were available.
• NU(a,b) will be generated.
• the index on a.x will be used to apply the predicate and to get FILEIDs; this is straightforward and effective.
• NU(a,b) will also be generated.
• index(a.x) and index(a.z) and indexfb.z) are generated if index(a.x) and index(a.z) and indexfb.z), with the two indexes on a being intersected before a tuples are retrieved.
• Q4 reduces to the Q3 case. In other words, there is no problem unless not index(a.x) and not index(a.z) and not index ⁇ .z). In that case, MJ(a,b) will be generated, and the subqueries cannot be effectively parallelized.
• index(a.x) and index ⁇ .y) and index(a.z) and index ⁇ .z) then nested loop joins are possible with either a or b as the outer table.
• the choice will be made based on the selectivity of the two single-table predicates D the more selective predicate will be applied to the outer table. If NU(a,b) is generated, then Q5/a/i is appropriate; if it is NU(b,a), then Q5/b/i is the preferred decomposition into subqueries. Either way, the subqueries can be effectively parallelized.
• index(a.x) If only one of the indexes supporting single-table predicates is present, say index(a.x), then Q5 reduces to the Q4 case. If neither is present, then Q5 reduces to the Q3 case.
• the preferred join order of tables is: first, the largest unindexed table, if one exists; followed by all indexed tables, in order of decreasing predicate selectivity (including both join predicates and single-table predicates); followed by all remaining unindexed tables, if any.
• the subqueries can be effectively parallelized. Since ORACLE currently does a sort on a.x for each subquery in order to weed out duplicates, the subquery results are assumed to be sorted on this field. Combining the subquery results then requires just one more level of duplicate elimination.
• the keyword DISTINCT can also appear inside of an aggregate function (e.g., A VG (DISTINCT a.y)). This construct cannot be effectively parallelized; it is impossible to combine subquery results in a meaningful way.
• a VG DISTINCT a.y
• partitioning table also referred to as "driving table” elsewhere
• join order the set of decomposable queries (assuming that the underlying tables are all partitioned). We expect these rules to be refined over time.
• a first implementation may use the first table in the optimizer's EXPL.AIN plan as the partitioning table.
• the preferred join order of tables is: first, the largest unindexed table, if one exists; followed by all indexed tables, in order of decreasing predicate selectivity (including both join predicates and single-table predicates); followed by all remaining unindexed tables, if any. This supports access plans that consist of one or more nested loops joins, followed by zero or more merge joins.
• Non-flattenable nested subqueries can be effectively parallelized, if they do not contain any other problematic constructs.
• Decomposition will be done when the answers to (a), (b), and (c) are yes.
• the user will always retain the ability to disable decomposition if desired. We intend to automate the answers to all of these questions.
• An application programmer can override any of the automatic decomposition decisions by using directives in the SELECT statement, in the form of embedded comments.
• the exact form of these directives are not described in this database note, but will adhere to the style used in ORACLE. For purposes of this database note, we will make some rational guesses about what they might look like.
• Query decomposition can be used with Pro*COBOL, Pro*C, SQL*Plus, OCI, SQL*Report, and possibly SQL*ReportWriter when it gets rewritten to use UPI in ORACLE version 7.0. (It might also work with the precompilers for other languages, but we will make no special effort to insure that.) We would like to support QD for PL/SQL, but have not yet determined how much additional work would be needed, if any.
• the parallel execution of queries via QD can be selectively enabled and disabled without changing any application code.
• a parallel application can be written and initially tested in serial mode. After it is working correctly, parallelization can be turned on with some kind of switch.
• ORACLE application processes queries by iteratively performing fetches on a cursor, which steps through a virtual table of result rows. This result table does not necessarily exist as a complete entity at any point in time. It is frequently constructed on the fly, so that the result
• One of our design goals is to modularize query decomposition to allow that code to be maintained separately from the rest of the ORACLE code. This follows Oracle's policies on port-specific modifications and will simplify the appropriate sharing of maintenance between KSR and Oracle.
• the UPI (User Program Interface) is the common point of access to the ORACLE kernel for all applications.
• a parallel UPI library (PUPI, pronounced "puppy") will be developed that intercepts each call to UPI (for performing operations like connect, parse, fetch, etc.) and generates multiple calls to UPI, which generally will be executed in parallel (see Figure 26 - 1).
• KSR ORACLE should be runable without the PUPI.
• PUPI will pass the original query on to UPI to have it parsed and verify that the syntax is correct. After that, the query will be scanned to parse the parallel directives, if any. By default, we will decompose any queries where it is correct and effective to do so, as long as decomposition has been enabled. The user can override the decision to decompose or the choice of partitioning table. Once the partitioning table has been determined, the PUPI will look up the table name in ORACLE'S catalog to find out the number of files comprising it and the list of file Jd's. The number of files determines the number of subqueries and, therefore, the number of additional connections to ORACLE that are needed.
• Rows will be fetched asynchronously from the subcursors and returned to the application as needed.
• the rows returned from the subcursors may need to be combined or ordered in some way before the root cursor's fetch can be satisfied. See the Parallel Cursors section below for more details.
• the PUPI will consist of a set of functions that have the same external interface as their UPI counterparts, but will call the appropriate UPI functions multiple times. Not all the UPI functions will be duplicated in the PUPI, since not all of them can be or need to be parallelized. We need a way to easily switch between serial and parallel query processing. At different times, the same application may call either UPI or PUPI functions without (by our own requirements) changing any code. (See Figure 26 - 3. The three functions shown in each library parse a query, execute it, and fetch the results. There are many more functions that need to be implemented.) The "Application" in this figure can be assumed to include SQLLIB and OCI, i.e., everything above the UPI level.
• the UPI maintains a hstdef " (host definition) structure for every connection that exists. We will allocate a hstdef for each additional connection we need (one for each subquery). The proper hstdef for each connection must be referenced when performing any actions related to the subqueries.
• the extra connections can't be made until after the original query has been parsed and the number of subqueries has been determined. At that time, we will also have access to the hstdef that was set up on the first connection, which may contain information we need in order to make additional connections to the same database. (We need to have access to the connect string (user, password, host, etc.), or its equivalent. Without that, we have no way of knowing where the original connection was made.) We may also need access to the
• ALL_DATA_FILES doesn't yet exist, but could be created as a duplicate of
• DBA_DATA_FILES with the additional condition that the tablespace_name must exist in .ALL_T.ABLES.
• a public synonym could be created for DBA_DATA_FILES, with public select access. It depends on how concerned users are about letting everyone see what database files exist on the system.
• a set of combining functions will be developed to produce a single result row for the application from all of the subquery rows available for consideration. Only the most recent row from each subquery needs to be considered.
• the specific method used for merging or ordering the subquery results is completely dependent on the nature of the query. The existence of aggregate functions, ORDER BY, or GROUP BY clauses are the main factors to consider. Sometimes multiple combining functions need to be applied to the same query. For example, the query
• each subquery will have returned a single row containing the aggregate result for its partition. Combine all of these rows into a single row, using the appropriate aggregate function(s).
• the combining function cannot simply take one row from each subquery and combine them. It needs to select and combine rows where the group values match each other. For the first root cursor fetch, all the DEPTNO 10's will be combined; the next fetch will combine the 20's, etc. Since GROUP BY implies ascending ordering before the aggregate function was applied, we can select the lowest available group value and all of its duplicates.
• the need to combine multiple rows implies that the query has at least one aggregate. Combining can be viewed as collapsing several rows into one. All the eligible subquery rows are identical in the non-aggregate columns. These columns can simply be copied into the result row.
• the aggregate columns can be combined by calling the appropriate combining function, passing the column number and pointers to the relevant rows. Note that averages need some special handling - the corresponding COUNT column also needs to be identified and taken into account by the combining function.
• Airay fetches will need some special consideration.
• the combining functions may have to be called iteratively until the array is full.
• the user requested decomposition and the query cannot be decomposed correctly .
• the user requested decomposition and the query can be correctly decomposed, but not effectively. It may even run slower.
• Data skew or partition skew in the partitioning table with respect to the query.
• data skew here to mean any distribution of data that causes result rows to be fetched from the subcursors in something other than round-robin fashion. For example, sorted output may appear in clumps so that several rows in succession from the same subcursor are returned to the root cursor. During such periods of time, little, if any, parallel fetching will occur. This phenomenon may appear and disappear many times during the course of a single query. Increasing the number of fetch buffers per subquery will help to minimize the effects of this type of data skew.
• Partition skew is defined as a distribution of data that results in unequal-sized partitions. During the latter part of query execution, and possibly even during the entire query, some partitions will have no more rows to fetch. This will reduce the degree of parallelism for the remainder of the query.
• the database partitions may actually be equal in size, but the effective partition size for any given query might be reduced by predicates in the query.
• ORACLE or OS limits on the number of processes, threads, connections, etc.
• query decomposition is designed to work in conjunction with other parallel processing techniques, such as parallel relational operators and pipelining.
• parallel relational operators and pipelining.
• Our Query Decomposition parallelizes a query by dividing it into subqueries, each of which use a rowid range predicate to specify one or more files to which that query's reads will be restricted.
• the approach depends on partioning tables across files on multiple disk drives, so that the files can be read in parallel. So, for a trivial example, if the table EMP is partitioned across 3 files with ORACLE fileid's 1, 2, and 3, then the query SELECT * FROM EMP can be decomposed into three subqueries:
• the first query will only read blocks of the EMP table which are in file 1, the second will only read blocks from file 2, and the third from file 3.
• ORACLE has been modified to restrict reads during full table scans, based on rowid range predicates, as a necessary prerequisite to implementing this approach.
• Query Decomposition can also work with queries that use an index.
• Each of these subqueries must redundantly read the same index blocks, to find index entries for DEPTNO 5, but hopefully the index blocks will be cached by the first subquery which gets to each one, so they are only read once.
• a subquery finds an index entry for DEPTNO 5, however, it will examine the rowid stored in that index entry, to see whether it fall within the range for that subquery. Only if it does will that subquery read the data page containing the row with that DEPTNO value and rowid. Speedup is not as close to linear as with full table scans, because only the table reads are partitioned. Logically, the total reads are increased due to redundant reading of the index, but the redundant reading happens in parallel, and hopefully caching will eliminate most actual redundant I/O.
• Query Decomposition as described above speeds up query execution by parallelizing the reads involved in a query, but not by reducing their total number. While this improves individual query response time, it does not improve system throughput (and may even reduce throughput, due to the added overhead of additional threads and processes, and of redundant index reads).
• ORACLE'S clusters and hashed clusters are approaches to speeding up query execution by greatly reducing the number of reads needed to accomplish certain queries.
• "Regular" (i.e. non-hashed) clusters reduce the reads needed for commonly-executed joins by clustering together the rows of several related tables based on common join column values, further reducing the number of blocks needed to read a related set of rows by storing each cluster key value only once for all rows of all tables sharing that key value.
• This kind of cluster still has an associated index on the cluster key, but the index entries simply point the to root block for the cluster key value, rather than having separate rowid entries for individual rows.
• Hashed clusters reduce reads for queries which seek rows of an individual table that exactly match a given key value. Rows with key values that hash to the same hash key value are clustered together, and no index is needed to navigate directly to the root block for a given hash key value.
• Query Decomposition has more general applicability: as long as a DBA decides in advance to partition a given table across multiple disks, Query Decomposition can be used on that table for any query that uses either a full table scan or any regular index, rather than being restricted to queries with predicates on certain predetermined columns.
• Query Decomposition and clustering cannot be used in conjunction to optimize access to the same table in the same query. This is so because accessing a table through a cluster key, whether hashed or otherwise, does not use either a full table scan or a regular indexed scan. Instead, it uses the cluster index (for regular clusters) or hashing to find the root block for the cluster key value. Then, if all rows for the specified cluster key value are in that one block, that's all that has to be read, so there's no opportunity for parallel partitioning. Otherwise, all of the chained blocks for that cluster key value must be read in sequence, whether they are in the same or different files.
• the index entry for a particular key value just points to the first block of the overflow chain, so there's no opportunity to examine rowid's and decide whether they fall in a specified range, to decide whether to read a data block.
• an index has a fairly small number of distinct values, relative to the number of rows in a table, and if rows with a given index value can be scattered anywhere in the table, without regard to their key value on that index, then even after using the index, a much larger volume of data may have to be read from the table than the volume represented by rows with the desired key values, because only a small fraction of each block read consists of the desired rows. In the worst cases, all blocks of the table must be read, so that performance is worse than if the index isn't used at all (because of the extra reads of the index, and because of the higher proportion of random to sequential I/O's).
• QD can ameliorate the problem by splitting up the load in parallel, but it remains the case that if the index doesn't provide speedup relative to full table scan without QD, then it won't provide speedup relative to full table scan with QD.
• each index entry consists of a key value /rowid pair, which points directly to the block containing the row in question. Also because it is a regular index, it can be used for range predicates as well as direct match predicates.
• the ORACLE optimizer When presented with a query that has an exact-match predicate on the hash key columns, the ORACLE optimizer will choose hashed access rather than using the index on those same columns, because under normal circumstances, hashed access would unquestionably be faster.
• the Query Decomposer notices (in the EXPLAIN plan) that ORACLE has chosen hashed access, and that there is a regular index which has all of the columns of the hash key as its leading columns, it can generate an INDEX optimizer hint in the parallel subqueries, coercing the ORACLE optimizer to use the regular index rather than hashing. Since the parallel subqueries have rowid range predicates, this regular indexed query can be decomposed like any other. But because the data is clustered on the same column values, with blocks for each cluster key value well-distributed among the files of the partitioned table, many fewer blocks need to be read than if this were not a hashed table.
• the regular index may optionally contain additional trailing columns, beyond those which match columns of the hash key. This means it can be used to further restrict the rows read, according to additional predicates in the query. This could be particularly useful to give added flexibility, because a hash key must be decided upon by a DBA before a table is created, and once the hashed table is populated, it would require a complete reorg to add additional hash key columns. It is much easier, however, to add columns to an index (or replace it with a different index) without affecting the data itself. So if additional frequently- used selection criteria are identified after a hash table already exists, these columns could be added to the regular index.
• the Query Decomposer must choose one of these indexes arbitrarily, as the one it will tell ORACLE to use, because it is not equipped to perform the function of a full-fledged query optimizer, to analyze the predicates in the query and decide which index would be best to use. In this event, however, the user may optionally choose the index by placing the INDEX optimizer hint in the original query.
• the Query Decomposer always leaves any hints from the original query in the parallel subqueries, to provide the user this extra degree of customized control over optimization when needed in this or other situations.
• ORACLE applications use a client-server architecture in which all database access is performed on behalf of an application program by a separate server or "shadow" process. While this architecture is used even when the client application and the server are running on the same machine, ORACLE'S SQL*Net network software supports the seamless connection of remote clients and servers running on heterogeneous platforms. This permits the KSR1 to play the role of database server for a network of workstations, a configuration which is becoming increasingly prevalent, and may be preferred or even required by some potential KSR customers.
• Section 1 explains why remote workstations cannot be supported by the current QD architecture; Sections 3 and 4 present alternate architectures to solve the problem; and Section 5 draws conclusions about which architecture is likely to be preferable, and how much effort will be required to implement it.
• PUPI Parallel User Program Interface
• This set of routines emulates the calling sequence and behavior of the UPI routines, but is also capable of decomposing a query into parallel subqueries, creating and managing the threads in which those parallel subqueries are executed, and combining the results to emulate the result of the original query. For each parallel subquery, a separate thread is created, and a connection is made from within that thread to a separate ORACLE server.
• PUPI routine is called for a task which does not require parallelism, behavior is the same as for an ordinary UPI routine, and the call is serviced by the server from the original user connection (which we may designate the primary server to distinguish it from the servers used for parallel subqueries).
• This architecture is shown in Figure 61 - 1.
• This architecture takes advantage of ORACLE'S separation of client and server processes, even for local connections, to manage parallelism inside the client process, thereby requiring minimal change to the server. Unfortunately, this only works when the client is executing on the KSR1. To support a remote client, the architecture must be changed so that parallelism can be managed on the server side of the remote client/server boundary.
• An advantage of this solution is that it introduces no new processes or connections, other than those specifically needed for executing parallel subqueries.
• a client program makes sends a message to the server which does not require parallel processing, that call is simply passed on into the kernel, without requiring an additional message.
• the ORACLE server is playing a dual role, both as a standard ORACLE server, and as a QD server.
• ORACLE kernel From the standpoint of detailed design and implementation, changes of this nature to the ORACLE kernel present much room for unpredictable difficulties and side effects.
• Prior experience indicates that it can be very difficult to emulate client behavior inside a server, since the two sides of a client/server interface, if not specifically implemented to allow for this, may contain variables with corresponding names and purposes, but which are used in subtly different ways.
• the current implementation of QD assumes its residence in the client; ORACLE functions are called which have similar but different counterparts on the server side.
• the QD server would incorporate routines from the outermost, message handling layers of the ORACLE kernel (in particular, modules of the SQL*Net and Two Task Common, or TTC, layers), but its dispatcher would call PUPI routines, rather than OPI or POPI routines, to service requests.
• This architecture is shown in Figure 61 - 3 below.
• a key advantage of this approach is that, while it incorporates some peripheral kernel routines, it does not constitute modification of the ORACLE kernel itself.
• QD code is completely segregated from the kernal. There are likely to be fewer dangers of side effects, and much less danger of unintentional security violations (the latter danger is not entirely eliminated, because emulating an ORACLE server from the client's perspective may still require access to the ORACLE SGA, but in a better-isolated and more easily-controlled context).
• Some degree of message translation may be necessary to relay incoming messages, intended to be processed by OPI calls, to UPI or PUPI calls which will pass them along to an ORACLE server. Furthermore, while the majority of UPI calls do not require PUPI counterparts in the current implementation, because they are not directly related to retrieving query results (e.g. calls for managing transactions, for connecting to ORACLE, or for modifying data), a QD server would need to be able to relay
• ORACLE server More detailed study of the ORACLE code will be required to determine the amount of effort involved, and whether it outweighs the advantages of leaving QD in the PUPI layer. It could turn out that this approach is not as different from the approach of relocating QD inside the OPI layer as it would superficially appear to be.
• Another disadvantage of this approach is that requests for database operations which do not require parallelization must make an extra message hop to get from the client application to the ORACLE server which will service them. Since the QD code decides whether a given UPI call requires parallelization, if the QD code is in the QD server rather than in the application program, then the application program can't "know" whether to send a given request to the QD server or the ORACLE server, so it must always choose one or the other. We can provide mechanisms to let the DBA or application user decide globally or per application whether to enable QD for remote queries, so that applications with little or no need for QD can avoid the extra overhead of the intermediate QD server.
• a hybrid approach could place inside the application program those portions of QD logic which determine whether to decompose a query, while managing the parallelism in a QD server. This approach, however, would require substantially more effort to implement, since it would involve a re-partitioning of QD functionality among processes.
• the QD server approach appears preferable to the approach of locating QD in the ORACLE server, but not dramatically so.
• the QD server approach avoids modifying the ORACLE kernel, but this is somewhat offset by the added architectural complexity and possible complications in packaging and code integration. Maintaining the same QD/ORACLE interface for remote and local clients is certainly preferable conceptually, but may be offset by difficulties in relocating some kernel routines in a separate server, and in relaying messages to UPI routines which were intended for OPI routines.
• the QD server approach introduces extra performance overhead for non- parallelized ORACLE calls; this can be limited at the cost of slight extra administrative complexity, and might be reduced further by optional hybrid approaches, at the cost of greater development effort.
• a reasonably conservative initial estimate of development cost would be one person-month to implement the basic QD server functionality, with an additional two to three weeks to resolve peripheral issues of administration, configuration, and packaging.
• the initial phase of development would involve a detailed examination of the relevant ORACLE code, which would facilitate making a final decision between the alternate approaches, and producing a more reliable development cost estimate and task breakdown.
• This paper provides a conceptual framework for automating the process of query decomposition proposed in Database Notes #21 and #26.
• This framework can be viewed as a general structure within which to answer the question "What do we know, and when do we know it?", during the stages of transformation from an original input query to a decomposed query ready for parallel execution.
• this paper provides a breakdown of the categories of rules involved in query decomposition, their input information and goals, and the categories of generated queries associated with them.
• OAT model whose name is an acronym for three forms through which a collection of information passes during a transformation: the original form (O-form), the analyzed form (A-form), and the transformed form (T-form).
• the process of query decomposition consists of producing, for a given input query, the collection of parallel subqueries, combining queries, combining function control structures, and other control structures needed to retrieve data in parallel and combine it to emulate the result table of the original query.
• This can be viewed conceptually as a transformation of the original query (which we will designate as the O-form of the query) to that collection of objects which comprise the decomposed query (which we will designate the T-form of the query).
• To automate this process we must specify a collection of rules whose starting point is the O-form of a query, and whose ultimate goal is the T-form. This highest-level goal path is shown in Figure 32 - 1.
• An SQL query submitted to the system does not contain within itself all of the information needed to decompose it. Strategic information such as index usage, table cardinalities, predicate selectivity, and join order and method must be obtained from the query optimizer to make decisions about decomposition strategy, such as choice of a partitioning table. Semantic information about tables, columns, clauses and expressions in the query must be gathered from the data dictionary to determine the details of combining functions and queries (for example, what kind of comparisons to perform for a merge sort,
• the A-form includes the original query definition and any needed cross-references between that definition and the other collected information, so that no information is lost in the transition from O-form to A-form.
• the A-form provides a valuable "fire wall" between the gathering/analyzing rules and the transformation rules. It prevents radical differences in the gathering/analyzing approach from having any effect on the transformation approach (for example, the difference between parsing the input query and then querying the data dictionary to bind semantic information to the parsed query, or obtaining a parse tree with already-bound semantic information from the query optimizer, and translating that to our standardized A- form). It also permits us to expand our repertoire of parallelization techniques relatively independently of the gathering/analyzing rules.
• query decomposition process Much of the query decomposition process, both in the gathering/analyzing and transformation phases, is accomplished through the generation and execution of queries.
• query is used in the broad sense to include DDL commands such as CREATE and DROP, para-DML commands such as EXPLAIN, and logical equivalents to these and other DML commands which do not necessarily involve explicit generation or processing of SQL.
• Query generation is used to mean applying rules to define a query and prepare it for execution.
• Query execution is used to mean retrieving information through the query.
• Queries can be broken down into five categories: probing
• gathering/analyzing rules can be divided into two classes: gathering rules which govern the generation and execution of probing queries, and analyzing rules which analyze and restructure the gathered information to produce the A-form of the query.
• Probing queries also fall into two groups: those which gather information on query optimizer strategy and associated cardinality and selectivity estimates; and those which gather semantic information about objects referenced in the query from the data dictionary. (This may be an over-simplification in some cases. For example, queries about file partitioning have more to do with retrieval strategy than semantics, but formally they may have more in common with data dictionary queries than with optimizer queries, if the file partition information is accessed through a data dictionary view.)
• Optimizer strategy information can be obtained by invoking EXPLAIN to produce an access plan for the query, and then generating and executing appropriate queries against the plan table to obtain information about join order, join methods (nested loop vs. merge), and index usage. (If a later release of EXPLAIN also provides cardinality and selectivity estimates, these will be gathered as well.)
• Semantic information can be obtained by asking queries against data dictionary views, and by using DESCRIBE SELECT to generate a SQLDA structure describing the output columns (select list items) of the original input query, or of transformations of that query. In some instances alternate strategies for obtaining information are possible.
• Additional data dictionary queries beyond those which gather basic semantic information, may be needed in some cases to establish cross-references between the semantically-augmented parse tree and the query optimizer plan. These could be needed, for example, to determine which index name in the optimizer plan corresponds to which table name in the query definition, or to match table synonyms used in the query definition to actual table names.
• Set-up queries are generated during the transformation phase of query decomposition, and, as the name implies, they are executed during an initial set-up phase of query execution. They fall into two general groups: DDL set-up queries to create temporary tables or indexes; and DML set-up queries, which could be used in multi-stage execution strategies to populate temporary tables with intermediate results. Potentially, a DML set-up query could itself be decomposed and executed in parallel.
• Temporary tables may be created at set-up time, and populated during main query execution, to gather rows from parallel subqueries for final aggregation or testing of a HAVING clause by a combimng query.
• Creating temporary indexes, and populating intermediate sorted tables during set ⁇ up, are also steps of alternative approaches to merge joins which avoid redundant sorting of the non-driving table in the join by each parallel subquery, either by pre-sorting or by pre- indexing the non-driving table. If presorting is used, only those rows which satisfy single- table predicates are inserted in a temporary table, which is indexed on the join columns, and the temporary table replaces the original table in the FROM clauses of the parallel subqueries. If pre-indexing is used, the entire table must be indexed on the join columns. Either way, the resulting table can now be used as the inner table in a nested loops join.
• Clean-up queries are generated at the same time set-up queries are generated, and are executed when the overall parallel cursor is closed.
• Output rows from parallel subqueries provide the input rows to the combining functions and queries discussed below.
• the combining functions or queries dynamically merge the output streams of the parallel subqueries, so that the parallel subqueries do not have to be executed to completion before executing the combining functions or queries.
• a combination of combining functions and queries is used to merge the output streams of parallel subqueries, producing a single output stream identical except possibly for ordering to that which would have been produced by directly executing the O-form of the query.
• a single combimng function is used to produce the logical "union all" of the separate parallel streams.
• More complex cases can involve multiple functions or queries working together to perform merging of sorted streams, merging of groups, aggregation, and expression evalution (e.g. testing of HAVING clauses), as well as the set operations UNION, INTERSECT, and MINUS. The means by which multiple combining functions and queries can coordinate their efforts are discussed in detail in DBN #36.
• Combining functions are generic and predefined (e.g. one predefined grouping function, one predefined merging function, etc.), but their roles in executing a particular decomposed query are governed by control structures which are generated during the transformation phase of query decomposition. The interconnection of these structures governs the way in which the different combining functions and queries coordinate their work.
• a control structure When a combining query is called for, a control structure will be generated as for a combining function, but in addition, the query itself must be generated. This is done by starting from the A-form of the query, and applying transformations analogous to, but different from, those used to generate parallel subqueries. These can include the following:
• a goal of the transformation phase of query decomposition is the generation of control structures to glue together and coordinate the overall parallel cursor, and to keep track of housekeeping details such as memory buffers and DBMS connections.
• this means that the several types of queries produced by transformation rules are not separate and independent goals, but rather coordinated pieces which together constitute the embodiment of a parallel execution strategy, which is the T- form of a query.
• probing queries differ from the other four in that they are created during the gathering/analyzing phase of query decomposition, rather than during the transformation phase. They also differ in that while their generation is a goal of some of the gathering rules, they are used as a tool by other gathering rules, and the output of their execution serves as input to the analyzing rules, and so, indirectly, to the transformation phase of query decomposition.
• the remaining categories of queries can all be considered end products of query decomposition, and collectively (together with parallel cursor control structures) they constitute the T-form of a query.
• Figure 32 - 3 summarizes the query decomposition process. Solid arrows in the diagram represent the application of rules, and point towards the goals of those rules. .Arrows with dashed lines indicate query execution, and point from the query being executed to the query which depends on the output of that execution. Note that while there is
• 97 - is a sequence of execution dependencies between the four types of queries belonging to the T-form, the rules which generate them can conceptually be applied in parallel.
• Prolog provides an ideal tool for the definition, prototyping, and "proof-of-concept" testing of the rules of query decomposition. Rules can be specified clearly, concisely, and non-procedurally in Prolog, which can greatly facilitate testing of complex combinations of rules. Prolog also supports syntax for concise specification of grammar, which would facilitate developing a basic SQL parser to drive the rule testing. Once the set of rules has been verified in Prolog, it can be hard-coded in C for optimal efficiency of the actual implementation. As rules change or new rules are added to the system in subsequent releases, the Prolog prototype will provide a flexible tool for testing them together with the existing rules before adding them to the C implementation. The present document provides a framework within which to define and test specific rules in the Prolog prototype.
• pnodes referred to as “building blocks” or “bb's” elsewhere. These can be arranged into a doubly- linked tree called a pnode tree. Each pnode has one pointer to its parent, and zero or more pointers to its children, depending on its node type (some node types have a variable number of pointers to children). Other attributes of all pnodes include:
• Node ID Uniquely identifies this pnode within a particular pnode tree
• Each node type has its own executor function
• a variant portion will contain attributes particular to each node type, sometimes including additional state attributes.
• Each node type also has a specialized executor function, but all executor functions take the same two parameters: a request code indicating the type of operation to perform, and an array of pointers to buffers which is used to locate data.
• pnodes are specialized row sources.
• Pnode trees are parent-driven.
• a parent "pulls" rows from its children, which passively respond to parent requests.
• a parent pulls a child by calling the child's executor function, passing it a request code to distinguish the specific nature of the request. Since all executor functions are of the same type, and since the generic portion of the pnode contains a pointer to its function, a parent can call a child's function without knowing the child's node type, or what specific function to call.
• Request codes might include:
• RESET_CACHE Reset to beginning of cached group of rows, return first
• NEW_CACHE Start a new cached group of rows, return first
• CLEANUP Perform any necessary cleanup, e.g. close cursors
• a second (perhaps overlapping) series of reply codes is returned to the parent by the child, as the return value of its executor function.
• These might include:
• a third (again perhaps overlapping) series of state codes will be maintained by a pnode's execution function as values of its state field, to let the pnode remember its context from one pull to the next.
• State codes might include:

Priority Applications (2)

Applications Claiming Priority (2)

Publications (2)

Family

ID=22697584

Family Applications (1)

Country Status (5)

Cited By (3)

Families Citing this family (521)

Citations (3)

Family Cites Families (17)