Oracle International Corporation

Cetin Ozbutun

Hakan Jakobsson

Michael Charles Depledge

Jeffrey Ira Cohen

Quoc Tai Tran

Mark Kremer

Dmitry Mikhailovich Potapov

Alexander C Ho

Julian Hyde

Juan R Loaiza

William H Waddington

Kirk A Bradley

Mohamed Zait

Mohamed Ziauddin

Angela Amor

Andrew Witkowski

Garret Frederick Swart

Ashok Shivarudraiah

Khaled Yagoub

Ari Wolfe Mozes

Dinesh Das

Sunil Parrokkaren Chakkappen

Hong Su

Peter Clayton Belknap

Martin Roth

Gregory Dorman

Yali Zhu

Shivani Gupta

Zhan Li

John Leo

Yiqiong Zhang

Fred Zemke

Palash Sharma

Techniques for automatically preventing execution plan regressions are provided. In one technique, in a first user database session, in response to receiving a first database statement, a first execution plan is generated and, while executing the first execution plan, first performance data that indicates one or more first performance metrics of executing the first execution plan is recorded. In response to receiving a second database statement, where the first execution plan may be used to generate a result for the second database statement, a second execution plan is generated and second performance data that indicates one or more second performance metrics of executing the second execution plan is recorded. A comparison between the first performance data and the second performance data is performed. Based on the comparison, it is determined whether the second execution plan will be stored for future use to process a database statement.

Automatic prevention of plan regressions

A system and method is described for database split generation in a massively parallel or distributed database environment including a plurality of databases and a data warehouse layer providing data summarization and querying functionality. A database table accessor of the system obtains, from an associated client application, a query for data in a table of the data warehouse layer, wherein the query includes a user preference. The system obtains table data representative of properties of the table, and determines a splits generator in accordance with one or more of the user preference or the properties of the table. The system generates, by the selected splits generator, table splits dividing the user query into a plurality of query splits, and outputs the plurality of query splits to an associated plurality of mappers for execution by the associated plurality of mappers of each of the plurality of query splits against the table.

System and method for generating partition-based splits in a massively parallel or distributed database environment

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.

SQL execution plan baselines

A method, device, and computer readable medium for striping rows of data across logical units of storage with an affinity for columns is provided. Alternately, a method, device, and computer readable medium for striping columns of data across logical units of storage with an affinity for rows is provided. When data of a logical slice is requested, a mapping may provide information for determining which logical unit is likely to store the logical slice. In one embodiment, data is retrieved from logical units that are predicted to store the logical slice. In another embodiment, data is retrieved from several logical units, and the data not mapped to the logical unit is removed from the retrieved data.

Storing row-major data with an affinity for columns

Database operation-aware striping technique

When a query is performed an execution plan is formed, which is made up of a plurality of plan operators. Before executing any given plan operator, a storage area is checked. If a results set has been stored corresponding to the plan operator, then the cached results set is used instead of the executing the plan operator. If no results set exists that corresponds to the particular plan operator, the plan operator is executed, and the results set that is output may be stored for use by another plan operator of another execution plan.

Partial query caching

A method and mechanism of improving performance of database query language statements using data duplication information are described. Information describing data duplication within a data block is maintained. In one embodiment, the data duplication information is used to reduce number of predicate evaluations on data in the data block when a database query language statement is received against the data in the data block. In another embodiment, the data duplication information is used to reduce amount of data accessed by a database query language statement.

Method and mechanism of improving performance of database query language statements using data duplication information

A method and system of collecting execution statistics of query statements is disclosed. An execution plan is generated for a query statement in one embodiment of the invention. The execution plan includes one or more operations. One of the one or more operations is selected. The selected execution plan is then executed and a plurality of execution statistics of the selected operation is collected.

Method and system of collecting execution statistics of query statements

A method and mechanism is disclosed for implementing storage and retrieval of data in a computing system. Data compression is performed on stored data by reducing or eliminating duplicate values in a database block. Duplicated values are eliminated within the set of data that is to be stored within a particular data storage unit. Rather than writing the duplicated data values to the data storage unit, the on-disk data is configured to reference a symbol table a single copy of each duplicated data value. Column reordering may be performed in an embodiment to further improve compression efficiency. The column reordering may be performed to allow efficient removal of trailing NULL values from on-disk storage.

Method and mechanism for storing and accessing data

A method and system for performing spreadsheet-like operations in a database system is described herein. As explained, a database language is extended to introduce new clauses ('spreadsheet-like clauses'), for defining and executing spreadsheet-like operations on data contained within a RDBMS. According to one aspect of the invention, in response to receiving a database query that specifies an operation for manipulating data, data from a relational structure is retrieved and stored in a non-relational structure that can be addressed as a multi-dimensional array. The operation specified in the database query is the performed on then data.

Performing spreadsheet-like calculations in a database system

A technique and mechanism for transforming a query is described. A given query is transformed into a transformed query that references a global temporary table. Specifically, the given query contains a join between a given table and one or more other tables referenced by constraints (e.g. predicates). References to one or more of the constrained tables are replaced by a reference to a global temporary table. Before executing the transformed query, data that satisfies the constraints placed on the constrained table is inserted into the global temporary table.

Using global temporary tables to transform queries

A method and mechanism is described for indexing a body of records. An index associates ranges with records that hold key field values that fall within those ranges. Such an index may be implemented as a bitmap index. The bitmap index may contain entries that associate a range with a bitmap. The bitmap of an index entry identifies which records have a key field value that falls within the range associated with the entry. The index may be a native index maintained by a database system. The database system uses the index to efficiently process queries that specify range criteria.

Indexing key ranges

A method and mechanism is provided for executing a query that includes a subquery, where the subquery includes an ORDER BY clause. The results generated by executing the subquery are ordered as specified by the ORDER BY clause. When the results of the subquery are referenced by the outer query, the results generated for the subquery satisfy the restriction. For example, the restriction may specify that the row number pseudo-column be less than a threshold. In response, a TOP N operation is performed to return the rows that are TOP N in order. The order is based on the ORDER BY clause.

Finding the TOP N values through the execution of a query

Techniques are provided for executing distinct aggregation operations in a manner that is more scalable and efficient than prior techniques. A three-stage technique is provided to parallelize aggregation operations that involve both grouping and multiple distinct-key columns. Such queries are handled by splitting rows into as many pieces as there are distinct aggregates in the query, and processing the row pieces. During the first-stage, a set of slave processes scans the rows of the base tables and performs partial duplicate elimination. During the second-stage, a set of slave processes completes the duplicate elimination and performs partial set function aggregation. During the third-stage, a third set of slave processes completes the set aggregation to produce the results of the distinct aggregation operation. In addition, two-stage parallelization techniques are provided for parallelizing single-distinct aggregations, and for parallelizing distinct aggregation operations that involve multiple distinct-key columns, but do not require grouping.

Growing community of inventors

San Carlos, CA, United States of America

Cetin Ozbutun