This section identifies the new features of the Oracle Database 11g that relate to the OLAP DML and lists the Oracle OLAP DML statements that were added, changed, renamed, or deleted in Oracle Database 9i and Oracle Database 10g.
In Oracle Database 11g, the following changes were made to the Oracle OLAP DML:
Table Privileges Needed When Working With Analytic Workspaces
Support for OLAP Cubes, Cube Dimensions, and Cube Materialized Views
Also, as outlined in "Changes to the SQL OLAP_TABLE Function", there are several changes in the OLAP_TABLE SQL function which you can use to create relational views of OLAP DML data structures.
In Oracle Database 11g, you must have the correct privileges to create and delete analytic workspaces. For more information, see "Privileges Needed to Create and Delete Analytic Workspaces".
In Oracle Database 11g, the following OLAP DML statements have been added or changed to offer more support for grouping ids:
A new GROUPINGID function
New ROLLUP and GROUPSET keywords in the GROUPINGID command
New $GID_DEPTH, $GID_LIST, and $GID_TYPE system properties
In Oracle Database 11g, the following changes have been made to support explicit looping:
New WHERE clause in the ACROSS command that you can use to specify if the DO loop in that command is executed for an iteration of the loop.
New looping $LOOP_AGGMAP, $LOOP_DENSE, and $LOOP_VAR properties which are used by the OLAP_TABLE SQL function.
Additionally, in the limitmap parameter of the OLAP_TABLE SQL function there is support for a more complex Loop clause and a new Loop optimized clause.
In Oracle Database 11g, the following changes have been made to enhance aggregation:
Aggregation by partition. See AGGREGATE command for details
Clearing variables based on different aggmap objects. See the CLEAR command for details.
Checking to see if there are any changes in an aggmap since the last time a variable was aggregated using that aggmap. See the CHANGEDRELATIONS function for details.
Checking to see any value in a variable has changed (or the number of values that have changed) since the last time a variable was aggregated. See the CHANGEDVALUES function for details.
Checking to see if a variable or one or more of its partitions has values. See the ISEMPTY function for details.
Identifying the depth of a hierarchy. See the HIERDEPTH command for details.
Identifying the shape of a hierarchy. See the HIERSHAPE function for details.
In Oracle Database 11g, the AW command has been modified to support caching which removes the time needed to open an analytic workspace for repeated queries
AW command has new PURGE CACHE keyword.
AW ATTACH command now uses the cached version of an analytic workspace if one is available rather than opening a current version.
AW DETACH command has a new CACHE keyword that specifies that an analytic workspace that is being detached is considered a candidate for caching.
AW TRUNCATE command has been improved
In Oracle Database 11g, the AW command has been modified as follows:
AW command has new FREEZE and THAW keywords
AW ATTACH command has a new THAW keyword.
Additionally, the AW function has been modified to include a FROZEN keyword.
In Oracle Database 11g, the following OLAP DML data types have been added that correspond to SQL built-in data types:
Datetime and interval data types.You can define dimensions and surrogates with datetime data types. You can define variables, formulas, and programs with both datetime and interval data types. For more information, see "Datetime and Interval Data Types", "Datetime and Interval Expressions", and the following OLAP DML statements:
RAW data type. For more information, see "RAW Data Type".
Row identifier data types. more information, see "Row Identifier Data Types".:
The addition of these data types impacted the following OLAP DML statements:
In Oracle11g, the following functions, which are based on SQL functions of the same name, were added to the OLAP DML.
MOD function)Additionally:
The OLAP DML LOG function was modified so that it corresponds more exactly to its SQL counterpart.
The new OLAP DML ISINFINITE and ISNAN functions provide information similar provided by the SQL floating point conditions.
With this release, "logical" OLAP objects (cube and cube dimensions) have been added as first-class Oracle data objects that are defined in the Oracle data dicitionary. These "logical" OLAP objects overlay a number of underlying "physical" OLAP objects that are defined in the analytic workspace itself.
Many of the Oracle Database OLAP features that are new in this release were added to support the use of OLAP cubes and cube materialized views:
The following OLAP DML programs are provided that let you work with OLAP cubes and cube dimensions:
Also, the OBJORG function has been added so that you can use OLAP DML statements that are not cube-aware with OLAP cubes and cube dimensions.
The following changes have been made to the OLAP DML so that it optimally handles OLAP data cells which correspond to relational null facts:
The DEFINE VARIABLE command now has a WITH NULLTRACKING phrase.
Two new functions, NA2 and NAFLAG , return values that Oracle OLAP uses to identify how an OLAP data value corresponds to a relational fact.
The CHGDFN command now includes syntax that adds or removes NA2 bits from a variable.
Typically, these OLAP DML statements are automatically generated during the process of creating a cube materialized view; you do not explicitly write DML code that uses these new features. These changes are documented in this manual so that you can understand the automatically-generated DML statements.
Additionally, the default behavior of the EXPORT (EIF) and IMPORT (EIF) commands is to export and import cube metadata. There is a new keyword (NOAPI) that you can specify when you do not want this behavior.
In earlier releases of the OLAP DML, when you defined a composite using a DEFINE COMPOSITE command, you could specify a composite as a base object of another composite. This functionality was rarely, if ever, used. Beginning, in Oracle Database 11g, the base object of a composite can only be a dimension. If you have any nested composites in an existing analytic workspace, when you convert that analytic workspace into When you import nested composites from earlier versions into an Oracle Database 11g analytic workspace, IMPORT (EIF) automatically unnests the composites.
In some cases there are multiple status lists for a dimension. You can use the new STATCURR function to retrieve a status list value based on from a status list based on the position of that list within the status list stack for the dimension.
OLAP_TABLE is a SQL function that extracts multidimensional data from an analytic workspace and presents it in the two-dimensional format of a relational table. As Oracle OLAP is more tightly integrated into Oracle Database 11g, you no longer must use a MODEL clause in a SELECT FROM OLAP_TABLE statement to enhance performance.
See also:
Appendix A, "OLAP_TABLE SQL Functions"This section contains listings of the OLAP DML statements that were added, changed, renamed, or deleted in Oracle Database 11g:
The following statements have been added to the OLAP DML in Oracle Database 11g. The number in parentheses indicates the specific release in which the statement was added.
The following statements were deleted from the OLAP DML in Oracle Database 11g. The number in parentheses indicates the specific release in which the statement was deleted.
The following statements have been changed in the OLAP DML in Oracle Database 11g. The number in parentheses indicates the most recent release in which the statement was changed.
This section contains listings of the OLAP DML statements that were added, changed, renamed, or deleted in Oracle Database 10g:
The following statements were added to the OLAP DML in Oracle Database 10g. The number in parentheses indicates the specific release in which the statement was added.
The following statements were deleted from the OLAP DML in Oracle Database 10g. The number in parentheses indicates the specific release in which the statement was deleted.
The following OLAP DML statements were significantly changed in Oracle Database 10g. Examples of significant changes are the addition of a new keyword or a change in a default value. The number in parentheses indicates the last release in which the statement was significantly changed.
This section contains listings of the OLAP DML statement changes in Oracle Database 9i.
The following statements were added to the OLAP DML in Oracle Database 9i. The number in parentheses indicates the specific release in which the statement was added.
The following statements were deleted from the OLAP DML in Oracle Database 9i. The number in parentheses indicates the specific release in which the statement was deleted.
The following OLAP DML statements were significantly changed in Oracle Database 9i and have not changed since then. Examples of significant changes are the addition of a new keyword or a change in a default value. The number in parentheses indicates the last release in which the statement was significantly changed. See also "Statements Renamed in Oracle Database 9i" for a list of renamed statements.
The following OLAP DML statements were renamed in Oracle Database 9i. The number in parentheses indicates the specific release in which the statement was renamed.