Oracle International Corporation

International Business Machines Corporation

Mohamed Zait

Benoit Dageville

Sunil Parrokkaren Chakkappen

Hong Su

Dinesh Das

Allison Waingold Lee

Mohamed Ziauddin

Thierry Cruanes

Jiaqi Yan

Rafi Ahmed

Khaled Yagoub

Peter Clayton Belknap

Edward Leo Wimmers

Nirav Vyas

Christoforus Widodo

Giuseppe Psaila

Rakesh Agrawal

Uri Shaft

Karl Dias

Cetin Ozbutun

Niloy Mukherjee

Bhaskar Ghosh

Yali Zhu

Zhan Li

Hari Sankar Sivarama Subramaniyan

Yuying Zhang

Jiakun Li

Ali Cakmak

Juan R Loaiza

Tirthankar Lahiri

Vineet Marwah

Andrew Witkowski

Shasank Kisan Chavan

Abhinav Gupta

Hakan Jakobsson

Mark Charles Ramacher

David Vengerov

Unmesh Jagtap

Ari Wolfe Mozes

Aurosish Mishra

Gianfranco Putzolu

Leng Leng Tan

Kartik Kulkarni

Andre Kruglikov

Suratna Budalakoti

Rushan Chen

Allison Waingold

Chun-Chieh Lin

Sambavi Muthukrishnan

Beniot Dageville

Rakesh Agrawai

Can Tuzla

Andre Menck

Hari Sankar Sivarama Subramanyam

Automated Index Management entails automated monitoring of query workload in a DBMS to determine a set of higher load queries to use to evaluate new potential indexes. Without the need of user approval or action, the potential indexes are automatically created, evaluated and tested, and then made available for system wide use for executing queries issued by end users. Indexes created by Automated Index Management are referred to herein as auto indexes.

Automated real-time index management

Techniques are provided for gathering statistics in a database system. The techniques involve gathering some statistics using an 'on-the-fly' technique, some statistics through a “high-frequency” technique, and yet other statistics using a “prediction” technique. The technique used to gather each statistic is based, at least in part, on the overhead required to gather the statistic. For example, low-overhead statistics may be gathered “on-the-fly” using the same process that is performing the operation that affects the statistic, while statistics whose gathering incurs greater overhead may be gathered in the background, while the database is live, using the high-frequency technique. The prediction technique may be used for relatively-high overhead statistics that can be predicted based on historical data and the current value of predictor statistics.

Techniques for maintaining statistics in a database system

Techniques are described for responding to aggregate queries using optimizer statistics already available in the data dictionary of the database in which the database object targeting by the aggregate query resides, without the user creating any additional objects (e.g. materialized views) and without requiring the objects to be loaded into volatile memory in a columnar fashion. The user query is rewritten to produce a transformed query that targets the dictionary tables to form the aggregate result without scanning the user tables. 'Accuracy indicators' may be maintained to indicate whether those statistics are accurate. Only accurate statistics are used to answer queries that require accurate answers. The accuracy check can be made during runtime, allowing the query plan of the transformed query to be used regardless of the accuracy of the statistics. For queries that request approximations, inaccurate statistics may be used so long as the statistics are 'accurate enough'.

Statistics based query transformation

Techniques are provided for gathering statistics in a database system. The techniques involve gathering some statistics using an 'on-the-fly' technique, some statistics through a 'high-frequency' technique, and yet other statistics using a &#x201c;prediction&#x201d; technique. The technique used to gather each statistic is based, at least in part, on the overhead required to gather the statistic. For example, low-overhead statistics may be gathered &#x201c;on-the-fly&#x201d; using the same process that is performing the operation that affects the statistic, while statistics whose gathering incurs greater overhead may be gathered in the background, while the database is live, using the high-frequency technique. The prediction technique may be used for relatively-high overhead statistics that can be predicted based on historical data and the current value of predictor statistics.

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.

Creating hybrid and top-frequency histograms

Leveraging query-specific statistics in non-equivalent queries

Techniques are provided for merging (a) statistics associated with data added to a table in a bulk load operation with (b) statistics associated with data that existed in the table before the bulk load operation. The statistics associated with the bulk load data are generated on-the-fly during the bulk load, and are merged with the pre-existing statistics as part of the same transaction that is used to perform the bulk load operation. Consequently, execution plans for queries that are assigned snapshot times after the commit time of the bulk load transaction will be selected based on the new statistics, while execution plans for queries that are assigned snapshot times before the commit time of the bulk load transaction will be selected based on the pre-existing statistics.

Online optimizer statistics maintenance during load

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. The transformed query representation, when used for execution, causes the at least one other predicate to be applied separately from a join operation caused by the join operator such that execution of the initial representation is semantically equivalent to execution of the transformed representation.

Optimization technique of generalized disjunctive semi/anti join

Systems for database system management. A method embodiment commences upon intercepting a feature or command that is issued from a software application that interfaces with a database management system. Rules are used to identify that the feature or command is of a first type (e.g., an older type). The feature or command is analyzed to determine if it is a candidate to be modified or upgraded to implement a newer feature or command of a second type (e.g., a newer type). Modifications are made to the old feature or command of the first type to implement a new feature or command of a second type. The new feature or command of the second type is sent to the database management system for processing.

Database system feature management

Techniques are described for maintaining an expression statistics store that stores and updates metadata values for query expressions based on the occurrence of those query expressions within queries. In an embodiment, a database server instance receives a database query. In response, the database server instance identifies expressions within the database queries. The database server instance then determines whether an expression statistics store includes an entry for the particular expression. Responsive to determining that the expression statistics store includes an entry for the particular expression, the database server instance updates at least one metadata value in the entry based on the occurrence of the particular expression. Responsive to determining that the expression statistics store does not include an entry for the particular expression, the database server instance adds an entry for the particular expression.

Tracking and maintaining expression statistics across database queries

A system performs database management. The system receives a request for a query of a plurality of tables under a join condition on an attribute and determines a uniform mapping that maps a domain of the attribute into a set of numbers, where the domain of the attribute includes all attribute values taken by the attribute in the tables. Then, for a row in a table, the system includes the row into a synopsis of the table if the row includes an attribute value that is mapped by the uniform mapping to a number less than an inclusion probability associated with the table. The system obtains a plurality of synopsis for the tables by repeating the including for all rows in the table and for all tables, and determines, based on the plurality of synopsis, an estimate join size of the tables under the join condition.

One-pass join size estimation with correlated sampling

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&#x2014;for example as part of a scheduled job&#x2014;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.

SQL execution plan verification

Techniques are described herein for filtering data from a table during an in-memory scan. Predicates are pushed to in-memory scan to avoid scanning unnecessary columnar units and reduce the overhead of decompressing, row stitching and distributing data during evaluation. Techniques are described herein for generating implied predicates that have conditions on single columns from complex predicates that have multiple conditions on the same column, which can be evaluated during an in-memory scan. Techniques are also described herein to reduce the overhead of a table scan for processing a join query. When redistributing a first table for performing a hash-join, the nodes performing an in-memory scan of the first table may create a filter that tracks unique values from the join key. Data from the second table is only processed and transferred to other nodes in the cluster if the values from the join key pass through the filter.

Techniques for evaluating query predicates during in-memory table scans

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.

Transforming a query by eliminating a subquery

Growing community of inventors

San Jose, CA, United States of America

Mohamed Zait