Abstract: An improved normalization methodology enabling better optimization of SQL queries is described. The normalization methodology effectively handles three-valued SQL search conditions and operates in reduced memory environments. A query is examined and selectively normalized by examining each segment of the query to determine whether it is advantageous to fully convert that segment to conjunctive normal form (CNF). If full conversion of the segment to CNF is not advantageous, then only the useful conjunctive conditions (prime implicates) that can be derived from the original input are saved. These useful conjuncts (prime implicates) are unioned with the original statement, resulting in a search condition that is semantically equivalent to the original expression, but that can be better exploited by a query optimizer. This enables better optimization of the original query without the system overhead that would result from full normalization of the query.

Abstract: An improved method for creating an index based on a path-compressed binary trie in a database system comprising database tables and indexes on those tables is described. For a given index to be created, a path-compressed binary trie for the given index is determined. The path-compressed binary trie comprises internal nodes and leaf nodes. Based on a traversal of the path-compressed binary trie, an index is created comprising a first array of internal nodes encountered during the traversal, and a second array of leaf nodes encountered during the traversal. The database system employs said first and second arrays for providing index-based access for a given key value.

Abstract: A database system is described that provides a methodology for significantly reducing latching costs associated with index pages in non-covered index scans, all without the need for using a conventional “shadow page.” The database system uses a “look ahead” list (LA) to save up to a preset number (e.g., 60) of ROWIDs (row identifiers) from the qualifying index keys. Since a ROWID is typically small (e.g., about 6 bytes), the space needed to store 60 ROWIDs is typically much smaller than the underlying page size (which typically range from 2K to 32K in systems). Thus, instead of finding just one next qualifying key as is conventionally done, up to a selected number (e.g., 60) of qualifying keys on the current page are found and the ROWIDs from them are stored in the look ahead list. In many cases, the copying costs are eliminated too.

Abstract: An improved method for handling database queries including functional expressions against columns having enumerated storage is described. Upon receipt of a query including a predicate having at least one functional expression referencing at least one database column containing offsets to values in enumerated storage, a look-up table is created for storing results of evaluation of the predicate against the values in enumerated storage. Each functional expression of the predicate is evaluated against the values in enumerated storage and the results of evaluation are stored in the look-up table. Results stored in the look-up table may then be accessed through use of the offsets to values in enumerated storage. The method may also be utilized for projecting expressions against database columns having enumerated storage.

Abstract: A database system providing methods for eager and opportunistic property enforcement is described. Plan fragments are generated for obtaining data requested by a query. Plan fragments are grouped together in classes based upon tables of the database covered by each plan fragment. For each class, a particular plan fragment having the lowest execution costs for obtaining the data requested by the query is determined. If grouping is not required at a given class, an operator enforcing ordering is added to this particular sub-plan. However, if grouping is required at the given class, an operator enforcing both grouping and ordering is added to this sub-plan.

Abstract: A database system providing a methodology, implemented as an “Abstract Plan on Disc” technology (referred to herein as, “Ariadne”), is described for turning cost based optimization decisions into stored, reusable items. In particular, the present invention provides a novel language interface to the optimizer, through an Abstract Plan, through which it can be given a description of the desired query execution plan (QEP). The language interface defines a declarative language syntax that allows description of the QEP. It does not specify the sequence of operations the database system's optimizer and code generator should accomplish to generate the QEP, but rather describes the desired outcome. Such an approach provides an abstraction barrier between an optimizer directives language and some specific optimizer and code generator. In this manner, the present invention allows a database system the ability to generate a better execution plan, and thereby realize better query performance.

Abstract: A system providing methods for transmission of messages conforming to a financial protocol, such as the Financial Information Exchange (FIX) protocol, which requires a particular message sequencing is described. When one or more messages are received for transmission over a communication channel which does not itself provide support for such message sequencing (e.g., SWIFTNet), an identifier is assigned to each message. This identifier is based upon a particular session and a message sequence number within this session. The message is then transmitted over the communication channel. Upon receipt, the message is placed in sequence to provide the particular message sequencing required by the financial protocol. Proper sequencing is based upon the identifier assigned to each message. The same methodology may be used for transmission of multiple messages from a plurality of sessions over one or more communication channels. Methods for splitting messages between multiple communication channels are also described.

Abstract: A Client/Server Database System with an enhanced Lock Manager for improving concurrency is described. The system tracks information about database columns that are updated in the Lock Manager, in addition to the exclusive lock on the data row (in case of data row locking) or data page (in case of data page locking). In particular, a new field, lrcolumns, is added to the system's record lock data structure to track which columns have been modified. When an exclusive lock is requested on a row of the table being updated in the update statement, the Lock Manager sets the value of lrcolumns. In the context of an exclusive lock that was acquired to update one or more columns of a data row, if an exclusive lock was used only to insert or delete (but not update) the data row, the lrcolumns field would be set to 0. Similarly, the lrcolumns field is 0 for locks that are not exclusive locks (e.g., shared locks). With the Lock Manager enhancement of storing information about updated columns, scan (i.e.

Abstract: A database system providing an efficient methodology for performing an online rebuild of a B+-tree index is described. From a high-level perspective, the method operates by copying the index rows to newly-allocated pages in the key order so that good space utilization and clustering are achieved. The old pages are deallocated during the process. This approach differs from the previously-published online index rebuild algorithms in two ways. First, it rebuilds multiple leaf pages and then propagates the changes to higher levels. Also, while propagating the leaf level changes to higher levels, level 1 pages (i.e., the level immediately above the leaf level) are reorganized, eliminating the need for a separate pass. The methodology provides high concurrency, does minimal amount of logging, has good performance and does not deadlock with other index operations. Performance study shows that the approach results in significant reduction in logging and CPU time.

Abstract: An international postal address validation system is described. The system defines a grammar for the encoding of all required address fields (within an address) for a given country, so that the system can determine whether all address fields required for a country are present for a given international postal address. More particularly, the system stores information about: (1) Required address fields (i.e., metadata describing address fields and data that must be present in all addresses for a country), for error checking; (2) Address languages, for normalization; and (3) Address data, for validation. By providing a grammar that encodes information about the required address fields for a given country, and that takes into account the variety of different languages and character encodings that an address may appear in, the system provides international postal address validation even though certain addresses require application of country-specific requirements.

Abstract: A system providing methods enabling data in Extensible Markup Language (“XML”) format to be extracted, transformed and stored in a database, file system or main memory is described. The extraction and transformation process is generalized and can be used on various types of XML data, enabling XML data to be stored and queried using standard database query methodologies. The system includes parse-time functionality to transform XML documents into a structure having an interface that enables efficient access to the underlying data. The system also includes query execution-time functionality providing greater efficiency by bringing only the relevant portions of transformed XML data into memory in response to a query. The system parses and translates queries into a structure that can be executed without the need to write custom application-specific navigation code to search XML data. The system also enables original XML documents (or portions thereof) to be recomposed when required.

Abstract: A small-footprint relational database system providing a deterministic join enumeration methodology for left-deep processing trees is described. By providing a deterministic branch-and-bound join enumeration method for left-deep processing trees, the invention is able to efficiently optimize complex queries with high join degree by employing a novel approach to cost-based pruning of the search space. For each subquery, plan generation involves the following four distinct steps. First, the system adjusts predicate selectivities to account for disjuncts, Between predicates, and user estimates of selectivities. Next, the system constructs a join graph for the query that models inner and outer equijoin predicates, sargable single-variable predicates on single quantifiers, and Cartesian products. The system then enumerates join strategies and prune the search space using a branch-and-bound heuristic. Finally, the system recalls the cheapest strategy and constructs the detailed access plan for that strategy.

Abstract: A database system providing improved methods for optimization and execution of queries aggregating information from multiple database tables is described. The improved method breaks serial operations into a group of child operations that can be individually optimized and processed in parallel. A query including a grouping operator over a union node is split by pushing the grouping operation down into each input arm of the union node. Specifically, grouping operators are inserted into each child arm of qualifying union nodes, enabling separate physical database tables to be summarized in parallel. Each of the child grouping operators concurrently groups data and generates summary results in parallel, taking advantage of capabilities of available parallel processing hardware. The aggregate operators of the original master grouping operator are modified to accept and correctly synthesize sub-aggregated results generated by each of the child grouping operators.

Abstract: A database system implementing a methodology or technique that can be used to optimize processing of nested loop joins of three or more tables (n-ary NLJs for n>2) more efficiently is described. In implementation, upon encountering a failure condition (i.e., a given join condition does not hold true) from a join operator (scan child), context information (about the failure) is returned (to the n-ary nested-loop join operator) for indicating exactly which condition (i.e., join condition) failed. This information is tracked in a scan descriptor, which includes a “fail sarg” data field indicating exactly which particular search argument (“sarg”) failed. Based on this information, the system (operating through the n-ary nested-loop join operator) knows exactly which scan child to return back to (i.e., how far back to go in the join order to fetch the next row).

Abstract: System and methods are described for improved indexing and processing (e.g., query processing) of date-based information. A date value may be viewed as comprising year (y), month (m), and day (d) components. A DateIndex index data structure is defined to comprise three distinct groups of bitmaps: one for years, another for months, and another for days, respectively. This index can be created on a column having a datatype of “date” (e.g., standard SQL datatype which stores date literals). A month group is defined to contain exactly 12 bitmaps (call them M[1], M[2], . . . , M[12]) to track rows meeting at a specified month range for a particular column. Each successive bitmap within the group stores a successively smaller subrange of possible month values (e.g., month 1-12, month 2-12, month 3-12, and so forth and so on). In a similar manner, bitmaps may be created for tracking day and year ranges.

Abstract: A database system providing a methodology for optimized page allocation is described. During page allocation in the system, once an allocation page with free space has been located in the system's global allocation map or GAM (i.e., using routine page allocation steps), the page identifier for that allocation page is stored in a hint array, as part of that object's (i.e., table's) object descriptor or des. For a table undergoing a lot of splits (i.e., insert-intensive object), the system may store an array of allocation page “hints” (allocation page identifiers) in the des for that object (e.g., table). The array itself comprises a cache of slots (e.g., eight slots), each of which stores an allocation page identifier (“hint”) obtained from the GAM (from a GAM traversal occurring during the page allocation process) or is empty (i.e., has not been filled from the GAM and is therefore set to the initial value of null).

Abstract: A database system with improved query execution performance is described. In order to achieve as much accuracy as possible by optimizing large block I/O related query plans, the database system implements a methodology employing two new statistical measures for use in a database server. The first statistical measure, called “Data Page Cluster Ratio” (DPCR), indicates the “packing” of data pages by measuring the “clustering” property of the data pages, i.e., how well clustered the consecutive data pages of a given relational table are in the extents. The ratio is measured for data pages accesses either via a scan of an index or via a scan of the table. The second statistical measure, called Index Page Cluster Ratio (IPCR), indicates the “packing” of index leaf pages by measuring the “clustering” property of index pages, i.e., how well clustered the consecutive index leaf pages of a given relational table's index are in the extents.

Abstract: A resource methodology is described which provides a framework for defining specific resource classes support for resource methods, and provides support for resource-independent commands, including those used in resource consolidation. A Resource Consolidation methodology of the present invention works by starting with an empty resource set, then iteratively extends the set in a minimal fashion. At the end of the iteration, the initial resource set comprises sufficient resources. An important component to this approach is a Minimal Extension method. This method provides a directed, acyclic graph methodology that takes two graphs as input and extends one (the “primary”) so as to include the other one (the “secondary”). For efficiency, resources in the secondary graph are shared with those in the primary graph wherever possible.

Abstract: A distributed (e.g., client/server) computing environment is described which implements protocol methodology improving the streaming of objects, such as for distributed applications. In particular, the methodology facilitates streaming of objects (e.g., Java objects) stored and managed remotely (e.g., objects stored and managed in relational databases) to clients in a highly efficient manner. The methodology may be implemented by extending an existing streaming methodology or protocol to include a class identifier approach for supporting object serialization. A Class ID (ACI) serialization is provided as a protocol for converting between a java object and a binary representation. ACI is intended for an environment in which all classes ever involved in any serialization are known by the environment (as is often the case). Each class known to the environment is represented by a compact numeric identifier, and it is this identifier alone that is used to represent the class description in the serialization.

Abstract: A database system implementing a methodology or technique that can be used to optimize processing of nested loop joins of three or more tables (n-ary NLJs for n>2) more efficiently is described. In implementation, upon encountering a failure condition (i.e., a given join condition does not hold true) from a join operator (scan child), context information (about the failure) is returned (to the n-ary nested-loop join operator) for indicating exactly which condition (i.e., join condition) failed. This information is tracked in a scan descriptor, which includes a “fail sarg” data field indicating exactly which particular search argument (“sarg”) failed. Based on this information, the system (operating through the n-ary nested-loop join operator) knows exactly which scan child to return back to (i.e., how far back to go in the join order to fetch the next row).