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

Abstract: Techniques are described for optimizing the computation of OLAP ranking functions. The techniques involve push-down of the filtering operation into the window sort operation corresponding to a target ranking function. The push-down technique may be employed when a predetermined set of push-down conditions are met.

Abstract: Techniques for processing, in a database management system, a query referencing a plurality of tables of a database include determining whether a join should be performed between a first table and a second table. The determination is based on a particular set of one or more columns of the second table referenced in the query and a join index on the first table for an index key column from the second table. If it is determined that the join should not be performed, the query is processed without performing the join between the first table and the second table. By avoiding one or more joins, substantial savings in the consumption of computational resources are achieved.

Abstract: A method and system is provided for processing queries. Specifically, techniques are provided for handling a query that does not reference a particular materialized view, where the query requires access to values from a particular column not contained in the materialized view. A technique is also provided for processing a query that does not reference a particular materialized aggregate view, where the materialized aggregate view specifies an outer join between a child table and a parent table and the query specifies a particular type of join between the child table and the parent table, where the particular type of join is one of an inner join, an anti-join and a semi-join. The query is rewritten to produce a rewritten query that accesses the materialized aggregate view to produce data required by the query.

Abstract: Techniques are described which allow function-defined hierarchies to be registered with a database server. The information provided to the server during the registration process is used by the server to determine how to roll up data that has been aggregated at one level of a function-defined hierarchy to another level of the function-defined hierarchy. Techniques are also provided to perform rollup from one level of a function-defined hierarchy to another level of the function-defined hierarchy on data stored in a materialized view. Further, techniques are provided for rewriting queries that require aggregation at one level of a function-defined hierarchy to cause them to access data from a materialized view that stores data at a different level of the function-defined hierarchy.

Abstract: An aggregate index is used for accessing an aggregate value associated with one or more rows of a database table. The aggregate values are stored in the index entries of the index, thus allowing determination of aggregate values without accessing the underlying database table. The aggregate index is created by determining an aggregate value associated with an index entry, and storing the aggregate value with the index entry. The aggregate value is determined by accessing information corresponding to the index entry and processing the information. In the case of an index entry in the form of a branch node, the information might be aggregate values from other nodes, or values of items in the database pointed to by the index entries that are leaf nodes. The aggregate values may be stored in index entries of any type of index.

Abstract: A method and system are provided for processing queries. According to one aspect of the invention, a query that does not reference a particular materialized view is rewritten to reference the materialized view. In particular, upon receiving the query, it is determined whether the particular materialized view satisfies each condition in a set of conditions, where the set of conditions at least includes a condition that the materialized view reflects all rows that exist in a common section. The common section is a section of the query that is common to both the materialized view and the query. If the materialized view satisfies each condition in the set of conditions, then the query is rewritten to produce a rewritten query that references the materialized view. The materialized view may be a summary table that includes a summary column. The summary column contains values generated by aggregating values contained in rows produced by a one-to-many lossless join.

Abstract: A method and apparatus is disclosed for allowing access to one or more previously generated rows of data. A server receives a statement that requires delivery of one or more rows of data to a client, and designates a particular row of data as a current row within a generation sequence. As the rows of data are being delivered to the client, the server accesses select previously generated rows of data that has already been delivered to the client without designating a new particular row of data as the current row. Once the previously generated row is accessed, data is retrieved from predetermined columns. The data retrieved may be used to perform various calculations that will insert values into the current row prior to delivery of the current row to the client. The statement transmitted by the client may include various parameters, including a default parameter, that specifies a default value to be used by the server.

Abstract: A method and apparatus for transforming queries is described. According to an aspect of the present invention, a select-project-join query Q that specifies at least one semijoin is rewritten into another query that references the materialized view M. A common section shared by the select-project-join query and the materialized view includes the tables being semijoined.