Abstract: Described herein are optimizations and execution strategies for spreadsheet extensions to SQL. The partitioning of data, as specified in a spreadsheet clause, provides a way to parallelize the computation of spreadsheet and to provide and improve scalability. Even if the partitioning is not explicitly specified in the spreadsheet clause, the database optimizer can automatically infer the partitioning in some cases. Efficient hash based access structures on relations can be used for symbolic array addressing, enabling fast computation of formulas. When rewriting SQL statements, formulas whose results are not referenced in outer blocks can be removed from the spreadsheet clause, thus removing unnecessary computations. The predicates from other query blocks can be moved inside query blocks with spreadsheets clauses, thus considerably reducing the amount of data to be processed. Conditions for validity of this transformation are given.

Abstract: A method and mechanism for performing an operation based on multidimensional data in a relational database. A query is generated that includes a first set of instructions to select a portion of multidimensional data for inclusion in a relational cube and a second set of instructions to group the portion of multidimensional data in the relational cube using at least two or more levels of granularity of at least one dimension, thereby causing a relational cube to be generated. Based on a received request for an operation to be performed, the query is modified to include a third set of instructions that represent the requested operation; and the query is submitted to the relational database engine of the relational database management system. Thereafter, the operation is performed against the relational cube.

Abstract: Techniques are provided for improving efficiency of database systems, and in particular, to refreshing materialized views maintained by database systems and rewriting queries to access the materialized views. According to the approaches, a ranked materialized view is incrementally refreshed, and during the incremental refresh operation, rows in the partitions of the materialized view are ranked within the partitions.

Abstract: Techniques are provided for improving efficiency of database systems, and in particular, to refreshing materialized views maintained by database systems and rewriting queries to access the materialized views. According to the approaches, a ranked materialized view is incrementally refreshed, and during the incremental refresh operation, rows in the partitions of the materialized view are ranked within the partitions.

Abstract: Methods are provided for efficiently evaluating database queries including multiple rollup operators. With the computation of grouping identifiers to distinguish grouping levels of each rollup operator, evaluation of database queries that include concatenation of rollup operators includes nesting of the rollup operators and includes the grouping identifiers as sorting keys for subsequent processing. Furthermore, to optimize the query evaluation process, the order in which the rollup operators are computed can be determined based on cardinalities that estimate the number of records generated by each rollup operator, and parallel evaluation can be utilized by partitioning data records between rollup operator processing stages based on the grouping keys associated with the rollup operators that are not being processed at the next stage.

Abstract: In an embodiment, Partition Change Tracking (PCT) refresh is performed by using join dependencies on partitioning columns to derive and fill in identifiers of rows and/or partitions containing the rows in the refresh expressions. In an embodiment, the join dependencies that form the Materialized View's (MV's) query definition and the partitioning predicate of the table are used to automatically generate a statement which is run in order to derive and fill in identifiers of the rows and/or partitions that are being changed. The identifiers are then plugged into the refresh expression, and the refresh expression is run. Running the refresh expression causes the rows identified to be refreshed.

Abstract: 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.

Abstract: In an embodiment, estimated costs are used to pick the best refresh technique (e.g., method) for a given MV. In another embodiment, estimated costs are used to schedule the refresh of a set MVs in an order such that overall time to refresh is reduced, or in an order that uses the smallest overall time, for refreshing when compared to other alternative refresh schedules. In another embodiment, the estimated cost of refreshing each of a set of MVs is used to select the set of CPU resources to allocate to refreshing each MV. Based on the estimated costs, the allocation of CPU resources is chosen to be more optimal (e.g., use less CPU time) than other available sets of CPU resources. In an embodiment, when refreshing an MV, delayed index maintenance is chosen if a significant portion of the base table and/or the partitions of the table has changed.

Abstract: In an embodiment, estimated costs are used to pick the best refresh technique (e.g., method) for a given MV. In another embodiment, estimated costs are used to schedule the refresh of a set MVs in an order such that overall time to refresh is reduced, or in an order that uses the smallest overall time, for refreshing when compared to other alternative refresh schedules. In another embodiment, the estimated cost of refreshing each of a set of MVs is used to select the set of CPU resources to allocate to refreshing each MV. Based on the estimated costs, the allocation of CPU resources is chosen to be more optimal (e.g., use less CPU time) than other available sets of CPU resources. In an embodiment, when refreshing an MV, delayed index maintenance is chosen if a significant portion of the base table and/or the partitions of the table has changed.

Abstract: In an embodiment, estimated costs are used to pick the best refresh technique (e.g., method) for a given MV. In another embodiment, estimated costs are used to schedule the refresh of a set MVs in an order such that overall time to refresh is reduced, or in an order that uses the smallest overall time, for refreshing when compared to other alternative refresh schedules. In another embodiment, the estimated cost of refreshing each of a set of MVs is used to select the set of CPU resources to allocate to refreshing each MV. Based on the estimated costs, the allocation of CPU resources is chosen to be more optimal (e.g., use less CPU time) than other available sets of CPU resources. In an embodiment, when refreshing an MV, delayed index maintenance is chosen if a significant portion of the base table and/or the partitions of the table has changed.

Abstract: In an embodiment, estimated costs are used to pick the best refresh technique (e.g., method) for a given MV. In another embodiment, estimated costs are used to schedule the refresh of a set MVs in an order such that overall time to refresh is reduced, or in an order that uses the smallest overall time, for refreshing when compared to other alternative refresh schedules. In another embodiment, the estimated cost of refreshing each of a set of MVs is used to select the set of CPU resources to allocate to refreshing each MV. Based on the estimated costs, the allocation of CPU resources is chosen to be more optimal (e.g., use less CPU time) than other available sets of CPU resources. In an embodiment, when refreshing an MV, delayed index maintenance is chosen if a significant portion of the base table and/or the partitions of the table has changed.

Abstract: Users can create computational models in a spreadsheet application and automatically apply the model to data stored in a relational database. By importing a sample of the data from a database table into the spreadsheet application, users can build spreadsheet models that perform analysis and computations on the sample data. Once the model is complete, the spreadsheet model is translated into an SQL format model understood by the database. The SQL model can operate on the entire data set in the database, rather than just the sample data used to construct the model. The SQL model and its associated data are stored in the database, and the model can be executed on a different sets of data. A web browser based front-end allows model users to access the SQL model via a web browser, eliminating the need for model users to have a spreadsheet application.

Abstract: Approaches are disclosed for using a multidimensional database as a surrogate for a run-time hash table in the context of processing a relational query. In an embodiment, an analytic workspace is used to store data, wherein the data is to be evaluated using a by a function in the processing of a MODEL clause. The approaches eliminate the need to use a hash table to store the data that is to be evaluated. The data may initially reside in the analytic workspace, or it may be loaded into the analytic workspace.

Abstract: A system rewrites queries so that they may be executed more efficiently. Queries that reference the result set of an aggregate query are rewritten to reference another aggregate query in the form of an inner query that omits groupings that can not possibly satisfy the criteria imposed by the predicates of the outer query. Thus, when the inner query is computed, only rows for groupings that satisfy the criteria are generated, conserving resources that would otherwise be wasted generating rows that could not possibly satisfy the criteria.

Abstract: Methods are provided for efficiently evaluating database queries that include a grouping sets specification, by reducing the grouping sets specification to one or more combinations of groupings that are computable with simple GROUP BY clauses, with or without a rollup operator, wherein the GROUP BY clauses and rollup operators are efficient operations that can be computed with a single data sort. Computational costs are further minimized by computing a grouping from records generated from computation of a previously computed grouping. An optimization algorithm is described which determines the respective grouping combinations to which the grouping sets specification is reduced and the respective GROUP BY clauses, to minimize computational costs. Furthermore, an intermediate grouping, that is, a grouping that is not specified by the query, is included in a query evaluation plan in some instances, to compute specified groupings when doing so reduces computational costs.

Abstract: Methods are provided for efficiently evaluating database queries including a rollup operator and a distinct aggregate function. Using a sequence of sorts, duplicate record elimination performed on previous sorts at lower, or finer, levels of the rollup operator is taken advantage of by performing subsequent sorts on the preceding sort. Hence, when moving from one rollup level to the next higher level, there are fewer data records to sort with respect to the relevant grouping columns for that level, and thus also fewer duplicate data records to eliminate for purposes of computing the distinct aggregate. Using parallel evaluation, processing of aggregate functions is split among different processing slaves, and the measure of an aggregate function is included as a partitioning key when sending data from one data flow operation to the next data flow operation of a query execution plan.

Abstract: Described herein are optimizations and execution strategies for spreadsheet extensions to SQL. The partitioning of data, as specified in a spreadsheet clause, provides a way to parallelize the computation of spreadsheet and to provide and improve scalability. Even if the partitioning is not explicitly specified in the spreadsheet clause, the database optimizer can automatically infer the partitioning in some cases. Efficient hash based access structures on relations can be used for symbolic array addressing, enabling fast computation of formulas. When rewriting SQL statements, formulas whose results are not referenced in outer blocks can be removed from the spreadsheet clause, thus removing unnecessary computations. The predicates from other query blocks can be moved inside query blocks with spreadsheets clauses, thus considerably reducing the amount of data to be processed. Conditions for validity of this transformation are given.

Abstract: Described herein are optimizations and execution strategies for spreadsheet extensions to SQL. The partitioning of data, as specified in a spreadsheet clause, provides a way to parallelize the computation of spreadsheet and to provide and improve scalability. Even if the partitioning is not explicitly specified in the spreadsheet clause, the database optimizer can automatically infer the partitioning in some cases. Efficient hash based access structures on relations can be used for symbolic array addressing, enabling fast computation of formulas. When rewriting SQL statements, formulas whose results are not referenced in outer blocks can be removed from the spreadsheet clause, thus removing unnecessary computations. The predicates from other query blocks can be moved inside query blocks with spreadsheets clauses, thus considerably reducing the amount of data to be processed. Conditions for validity of this transformation are given.

Abstract: A method and mechanism for performing an operation based on multidimensional data in a relational database. A first query is submitted to a relational database engine of the relational database management system, thereby causing a relational cube to be generated. The first query includes instructions to select a portion of multidimensional data for inclusion in the relational cube. The first query includes instructions to group the selected portion of multidimensional data using at least two or more levels of granularity of at least one dimension. The first query also includes instructions to generate a materialized view. Based on a received request for an operation, a second query is submitted to the relational database engine for execution against the relational cube, thereby causing the operation to be performed.

Abstract: A method and mechanism for performing an operation based on multidimensional data in a relational database. A query is generated that includes a first set of instructions to select a portion of multidimensional data for inclusion in a relational cube and a second set of instructions to group the portion of multidimensional data in the relational cube using at least two or more levels of granularity of at least one dimension, thereby causing a relational cube to be generated. Based on a received request for an operation to be performed, the query is modified to include a third set of instructions that represent the requested operation; and the query is submitted to the relational database engine of the relational database management system. Thereafter, the operation is performed against the relational cube.