Abstract: Methods, stored instructions, and computer systems are described for selecting an OR-expansion state of a query. A query processor accesses a certain query and a control parameter. The certain query comprises disjunctive predicates, and the control parameter indicates a certain procedure of a plurality of alternative stored procedures for expanding queries comprising disjunctive predicates into semantically equivalent candidate queries comprising combinations of subqueries. Based at least in part on the control parameter, the query processor selects the certain procedure to expand the certain query into one or more certain semantically equivalent candidate queries, each comprising a different combination of two or more subqueries. The query processor causes cost-based evaluation of subject queries, including the certain query and the one or more certain semantically equivalent candidate queries.

Abstract: Approaches, techniques, and mechanisms are disclosed for maintaining a set of baseline query plans for a database command. Except in rare circumstances, a database server may only execute a command according to a baseline plan, even if the database server predicts that a different plan has a lower cost. The set of baseline plans are plans that, for one reason or another, have been determined to provide acceptable actual performance in at least one execution context. When the database server receives a request to execute a particular command, the database server, if possible, always executes the command according to the lowest predicted cost baseline plan. The database server may evolve the plan baseline to include additional plans by generating and testing new plans in response to new requests to execute the database command, or as part of a query optimization or tuning process.

Abstract: An improved method, system, and computer program product is disclosed that provides a hybrid approach to optimization which allows different subsets of data accessed by a query to be optimized with different access paths and execution approaches. Transformations may be performed to re-write the query, which restructures the query in a way that facilitates the hybrid optimization process. Multiple transformations may be interleaved to produce an efficient re-written query.

Abstract: A method, apparatus, and stored instructions are provided for transforming an initial query by eliminating a subquery nested within the initial query. The initial query may include an outer query that references a first instance of a particular data object. The initial query may also include set operator(s). At least a particular set operator may include a particular subquery that references a different instance of the particular data object. A query processor may transform the initial query to a transformed query that excludes the particular set operator and the particular subquery. The transformed query may instead include an added predicate that is based at least in part on the particular subquery. The added predicate may reference the first instance of the particular data object without referencing the different instance of the particular data object. The transformed query may be used for query execution instead of the initial query.

Abstract: An improved method, system, and computer program product is disclosed that provides a hybrid approach to optimization which allows different subsets of data accessed by a query to be optimized with different access paths and execution approaches. Transformations may be performed to re-write the query, which restructures the query in a way that facilitates the hybrid optimization process. Multiple transformations may be interleaved to produce an efficient re-written query.

Abstract: A method, system, and computer program product for high-performance database systems. The method commences by estimating, during a compile phase, a statically-calculated system cost of performing parallel join operations over two relations, then selecting, during the compile phase, a first distribution method to perform the parallel join operations. Instructions (e.g., table partitioning) are communicated to two or more execution units for execution of the parallel join operations, and during execution, various measurements are taken pertaining to the performance of a first portion of the parallel join operations using the selected first distribution method. Then, responsive to the measurements a second distribution method is selected and corresponding instructions are communicated to the two or more execution units. The parallel join operations continue, now using the second distribution method.

Abstract: Approaches, techniques, and mechanisms are disclosed for ensuring that a database command is executed according to a query plan that has been verified to be actually optimal. Except in rare circumstances, a database server does not execute a query plan unless it is first verified by the database server. The database server receives a request to execute a database command. The database server determines an unverified plan is the best plan for satisfying the request. Rather than risk the unknown behavior of an unverified plan, the database server instead satisfies the request according to a verified plan. Subsequently—for example as part of a scheduled job—the database server executes the unverified plan to determine performance statistics. Based at least on the performance statistics, the database server determines whether or not to verify the unverified plan. Techniques for concurrent and optimistic verifications are also disclosed.

Abstract: Approaches, techniques, and mechanisms are disclosed for ensuring that a database command is executed according to a query plan that has been verified to be actually optimal. Except in rare circumstances, a database server does not execute a query plan unless it is first verified by the database server. The database server receives a request to execute a database command. The database server determines an unverified plan is the best plan for satisfying the request. Rather than risk the unknown behavior of an unverified plan, the database server instead satisfies the request according to a verified plan. Subsequently—for example as part of a scheduled job—the database server executes the unverified plan to determine performance statistics. Based at least on the performance statistics, the database server determines whether or not to verify the unverified plan. Techniques for concurrent and optimistic verifications are also disclosed.

Abstract: Techniques for processing queries are provided. In one approach, an execution plan for a query includes multiple sub-plans, one or more of which are selected at runtime while one or more other sub-plans are not executed during execution of the execution plan. In another approach, data about misestimate is generated and stored persistently for subsequent queries. In another approach, statistics for a database object are generated automatically and efficiently while the database object is created or data items are added thereto. In another approach, a hybrid histogram is created that includes a feature of frequency histograms and a feature of height-balanced histograms. In another approach, computer jobs are executed in such a way to avoid deadlock. In another approach, changes to a database object trigger a hard parse of a query even though an execution plan already exists for the query.

Abstract: Techniques for processing queries are provided. In one approach, an execution plan for a query includes multiple sub-plans, one or more of which are selected at runtime while one or more other sub-plans are not executed during execution of the execution plan. In another approach, data about misestimate is generated and stored persistently for subsequent queries. In another approach, statistics for a database object are generated automatically and efficiently while the database object is created or data items are added thereto. In another approach, a hybrid histogram is created that includes a feature of frequency histograms and a feature of height-balanced histograms. In another approach, computer jobs are executed in such a way to avoid deadlock. In another approach, changes to a database object trigger a hard parse of a query even though an execution plan already exists for the query.

Abstract: Techniques for processing queries are provided. In one approach, an execution plan for a query includes multiple sub-plans, one or more of which are selected at runtime while one or more other sub-plans are not executed during execution of the execution plan. In another approach, data about misestimate is generated and stored persistently for subsequent queries. In another approach, statistics for a database object are generated automatically and efficiently while the database object is created or data items are added thereto. In another approach, a hybrid histogram is created that includes a feature of frequency histograms and a feature of height-balanced histograms. In another approach, computer jobs are executed in such a way to avoid deadlock. In another approach, changes to a database object trigger a hard parse of a query even though an execution plan already exists for the query.

Abstract: A method, apparatus, and stored instructions are provided for transforming a query representation by unnesting a predicate condition that is based on whether or not a result exists for a subquery of the predicate condition. An initial query representation is received. The initial query representation represents an initial query that includes an EXISTS-equivalent predicate or a NOT-EXISTS-equivalent predicate and at least one other predicate in a disjunction. The initial query representation is transformed into a semantically equivalent transformed query representation that represents a transformed query. The transformed query includes, instead of the EXISTS-equivalent predicate or a NOT-EXISTS-equivalent predicate, a join operator that references the data object.

Abstract: An improved method, system, and computer program product is disclosed that provides a hybrid approach to optimization which allows different subsets of data accessed by a query to be optimized with different access paths and execution approaches. Transformations may be performed to re-write the query, which restructures the query in a way that facilitates the hybrid optimization process. Multiple transformations may be interleaved to produce an efficient re-written query.

Abstract: Techniques are provided for delaying evaluation of expensive expressions in a query. Expensive expressions in the query are established by cost information or by looking up a list of known expensive expressions for a match. After an execution plan is determined by using the early evaluation technique, one or more equivalent execution plans is established. The one or more equivalent execution plans may include both a type of execution plans that delay evaluation of expensive expressions and a type of execution plans that do not. In addition, the one or more equivalent execution plans may include both parallelized and non-parallelized alternatives to the execution plan identified by the early evaluation technique. Finally, based on a set of criteria, which may include comparing cost information among all the equivalent execution plans generated thus far, the best execution plan is chosen for the query.

Abstract: Under automated alternate plan analysis, a query optimizer generates candidate execution plans. The candidate execution plans are selected as alternate execution plans for the query and execution. Output describing characteristics of each alternate execution plan and/or its execution is generated and/or compared. From this information, it may be determined, for example, whether results returned by any of the alternate execution plans are the same and whether the least cost execution plan is actually the most efficiently executed.

Abstract: Under a type of query transformation referred to herein as join factorization, the branches of an UNION/UNION ALL query that join a common table are combined to reduce accesses to the common table. The transformation can be expressed as (T1 join T2) union all (T1 join T3)=T1 join (T2 union all T3), where T1, T2 and T3 are three tables. A given query may be rewritten in many alternate ways using join factorization. Evaluating each alternative can be expensive. Therefore, the alternatives are generated and evaluated in a way that minimizes the cost of evaluating the alternatives.

Abstract: Auto-tuning can be performed by receiving a database query language statement and performance information related to the statement, determining whether one or more performance statistics of the statement are available or missing in the performance information, and determining an auto-tuning hint for each missing statistic.

Abstract: Techniques are provided for performing a parallel aggregation operation on data that resides in a container, such as a relational table. During generation of the execution plan for the operation, it is determined whether partition-wise aggregation should be performed, based on the grouping keys involved in the aggregation and the partition keys used to partition the container. If partition-wise aggregation is to be performed, then the assignments given to the slave processes that are assigned to scan a container are made on a partition-wise basis. The scan slaves themselves may perform full or partial aggregation (depending on whether they are the only scan slaves assigned to the partition). If the scan slaves perform no aggregation, or only partial aggregation, then the scan slaves redistribute the data items to aggregation slaves that are local to the scan slaves.

Abstract: A method for receiving a database query language statement and performance information about the statement at an optimizer and generating one or more tuning actions for the statement with the performance information is disclosed.

Abstract: A method for determining a name for a query block of a database query language statement, and associating one or more tuning hints with the query block using the name.