Abstract: A latent auxiliary database structure constructed for executing a query is automatically saved for reuse in subsequent query execution instances. The latent auxiliary database structure is not maintained concurrently with changes to records in the database tables. If a query execution strategy is subsequently invoked which uses the latent auxiliary database structure, a determination is made dynamically whether to update the previously saved latent structure from database changes recorded in one or more change logs, or to rebuild the latent structure from scratch. Where the cost of updating from the change log is deemed less than the cost of a rebuild from scratch, the previously saved latent structure is updated from the log.

Abstract: In an embodiment, a query tree is created that represents a query expression, where the query tree includes a union of nodes. Each of the nodes represents a respective partition of a table, and the query expression specifies a key value and the table. A determination is made whether a result set exists that includes the key value, where the result set was previously retrieved from the table. If the determination is true, a decision is made based on the result set whether the key value is stored in the respective partition represented by each of the nodes. The nodes for which the key value is not stored in the respective partition are removed from the query tree to create an optimization tree, and the optimization tree is used to retrieve data from the table. In this way, nodes that are not necessary to be searched may be removed from query trees, in order to increase the performance of the query.

Abstract: A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.

Abstract: A database management system maintains a record of queries which can be executed against the database. For a given auxiliary database structure, an advisory function automatically reviews the query record and identifies those queries which might execute with greater efficiency using the auxiliary database structure, presenting analytical information to the user. Preferably, the auxiliary database structure is an index, and the information is used for determining whether to include the index in the database definition. Preferably, a query optimizer records potentially useful indexes when considering multiple alternative database optimizations, and this information is later retrieved to identify queries which would benefit from an index.

Abstract: In one aspect, a database analytical tool or function analyzes join queries to detect queries which could be reduced if a referential integrity relationship were known. In another aspect, a probable latent referential integrity relationship is detected using one or more heuristics. Preferably, a join query is analyzed to detect a joined table which has no other conditions placed on it, and no referential integrity relationship already defined. For any such query, the analysis attempts to verify a probable latent referential integrity relationship using at least one heuristic, such as comparing cardinality of the potential primary key with the size of the table and/or with the cardinality of the foreign key. It is further possible to execute the join for some sample of records, and determine whether any records were found which did not conform to referential integrity constraints.

Abstract: A database application re-uses one or more query execution strategies for a given logical query, and saves historical data concerning query execution performance under differing execution parameters. The historical data is analyzed to identify environmental variables and/or imported variables which significantly affect execution performance. Preferably, an auxiliary data structure includes, for each of multiple execution strategies, a respective set of imported and environmental variables and respective average cost measure, such as execution time. An analytical tool compares multiple different strategies to identify imported and/or environmental variables which caused a different strategy to be used, and resultant average cost. Preferably, the tool can also compare variation within the same strategy.

Abstract: A stream data node receives real-time streamed data from one or more input devices, dynamically filters the streamed data to reduce the streamed data, and delivers the reduced data when requested. By providing real-time filtering of the data, the amount of data that must be stored in a database may be substantially reduced. The stream data node can perform aggregation functions, group functions, and select functions, thereby also significantly reducing the amount of data that must be stored in a database. The stream data node may also be part of a query execution data structure, where it delivers its data when requested by another node in the query execution data structure.

Abstract: Continual generation of index advice that includes generating an index advice for an index of a table in a computer database and recording values of statistical attributes of the index advice accumulated across repeated generations of the index advice. The generating and recording typically are carried out continually without user intervention. Embodiments include recording values of attributes of the index advice that specify an index. Typical embodiments also include, continually and without user intervention, recording values of attributes of the index advice that characterize usefulness of an index and recording values of attributes of the index advice that characterize cost of creating an index.

Abstract: A database facility supports database join queries in a database environment having at least one database table divided into multiple partitions based on a partition key value. The facility determines whether the values in a table joined to the partitioned table place an implicit limitation on the partition key, and eliminates from query evaluation any partitions which do not satisfy the implicit limitation. Preferably, the database uses a star schema organization, in which implicit limitations in a relatively small dimension table are used to eliminate partitions in a relatively large fact table.

Abstract: A query facility for database queries dynamically determines whether selective portions of a database table are likely to benefit from separate query execution strategies, and constructs an appropriate separate execution strategies accordingly. Preferably, the database contains at least one relatively large table comprising multiple partitions, each sharing the definitional structure of the table and containing a different respective discrete subset of the table records. The query facility compares metadata for different partitions to determine whether sufficiently large differences exist among the partitions, and in appropriate cases selects one or more partitions for separate execution strategies. Preferably, partitions are ranked for separate evaluation using a weighting formula which takes into account: (a) the number of indexes for the partition, (b) recency of change activity, and (c) the size of the partition.

Abstract: A method, apparatus, system, and signal-bearing medium that, in an embodiment, determine performance data for query optimization of a query directed to a database table having multiple partitions. The performance data for the query optimization is presented in a user interface, and an option is provided in the user interface to create a non-spanning index for any of the partitions mentioned in the performance data.

Abstract: A query facility for database queries saves and re-uses query execution strategies, and automatically determines whether a strategy can be re-used when an imported variable changes. The query facility automatically saves one or more logical conditions associated with the one or more imported variables in the query. When the query is reused, the logical conditions are evaluated using the imported variable values, and the previously saved execution strategy is used only if the condition or conditions are met. Preferably, the logical conditions express a range of imported variable values which are included in a database subset data structure used by the saved query execution strategy, such as a materialized query table or partitioned table. Logical conditions are preferably generated by negating the predicates from a logical expression defining the applicability of the execution strategy, inserting the negated predicates into the query, and performing a transitive closure.

Abstract: A visual query explain mechanism displays a query to the user in a graphical tree format. A user may execute a query, and the actual performance from executing the query is imported into the visual query explain mechanism. The visual query explain mechanism adds the actual performance to the nodes in the query tree, and compares the actual performance to the estimates of performance that were generated prior to executing the query. The visual query explain mechanism then looks at a predefined threshold value, and determines whether actual performance exceeds the estimated performance by the predefined threshold value. If so, the corresponding node in the query graph is highlighted in some way, thereby providing a visual indication to the user of problem areas in the query.

Abstract: Enabling a user to obtain and view graphic explain data with no need to activate a monitor and no need to wait for a job to complete before the explain data for a query may be viewed. Displaying explain data for an SQL query of a database that include executing an SQL query and displaying explain data for the SQL query while executing the SQL query. Displaying explain data includes identifying an access plan associated with the SQL query and generating explain data in dependence upon the access plan. Identifying an access plan may be carried out by retrieving an access plan identification from a cursor of a job control block and retrieving an access plan from an access plan cache in dependence upon the access plan identification.

Abstract: A relational database system analyzes each potential join in a query, to determine whether a relation involved in the join is subject to a selection criterion, and evaluate whether that selection criterion or the join per se effects a join reduction. The computational expense of generating a look-ahead predicate comprising the tuples of the second relation matching any applicable selection criterion, is compared to the computational savings that result from the join reduction. The most beneficial look-ahead predicate among all potential joins of relations in the query is identified through iterative analysis of all possible joins.

Abstract: An apparatus, program product and method utilize a query optimizer that identifies Join types in a query plan that can be replaced to facilitate optimization of the plan. If the query plan is considered to have a tree-graph representation, then the optimizer locates Join nodes to be replaced and substitutes an Inner Join node and additional nodes that do not inhibit optimization strategies and algorithms. As a result, all Outer Joins, whether left or right, and all Exception Joins, whether left or right, can be removed from the query plan before performing optimizations such as predicate movement, GROUP BY movement, ORDER BY movement, and Join enumeration.