Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

B28126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

What's New in the OLAP DML?

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 Oracle9i and Oracle10g.

New Features in the OLAP DML

In Oracle11g, the following changes were made to the Oracle OLAP DML:

Also, as outlined in "Changes to the SQL OLAP_TABLE Function", there are a number of changes in the OLAP_TABLE SQL function which you can use to create relational views of OLAP DML data structures.

Table Privileges Needed When Working With Analytic Workspaces

In Oracle11g, you must have the correct privileges to create and delete analytic workspaces. For more information, see "Privileges Needed to Create and Delete Analytic Workspaces".

Additional Support for Grouping Ids

In Oracle11g, the following OLAP DML statements have been added or changed to offer more support for grouping ids:

Increased Support for Explicit Looping

In Oracle11g, there are 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.

Aggregation Improvements

In Oracle11g, the following changes have been made to enhance aggregation:

Support for Analytic Workspace Caching

In Oracle11g, the AW command has been modified to support caching which removes the time needed to open an analytic workspace for repeated queries

Support for Multi-Step Analytic Workspace Build Process

In Oracle11g, the AW command has been modified as follows:

Additionally, the AW function has been modified to include a FROZEN keyword.

Additional Data Types

In Oracle11g, the following OLAP DML data types have been added that correspond to SQL built-in data types:

The addition of these data types impacted the following OLAP DML statements:

CHARTOROWID function
CONVERT function
CURRENT_DATE function
CURRENT_TIMESTAMP function
DATE_FORMAT command
DBTIMEZONE function
DEFINE DIMENSION command
DEFINE VARIABLE command
EXTRACT function
FROM_TZ function
NUMTODSINTERVAL function
NUMTOYMINTERVAL function
LENGTH functions
LOCALTIMESTAMP function
ROUND (datetime) function
ROWIDTOCHAR function
ROWIDTONCHAR function
SESSIONTIMEZONE function
SYS_CONTEXT function
TO_DSINTERVAL function
TO_TIMESTAMP function
TO_TIMESTAMP_TZ function
TO_YMINTERVAL function
TRUNCATE (datetime) function
TZ_OFFSET function (11.0.0.0)

New SQL-Like Functions

In Oracle11g, the following functions, which are based on SQL functions of the same name, were added to the OLAP DML.


BIN_TO_NUM
INSTR functions
LNNVL
MODULO (based on the SQL MOD function)
NLSSORT
REMAINDER

Additionally, the LOG function was modified so that it corresponds more exactly to its SQL counterpart.

Support for Materialized Views

Many of the Oracle OLAP features that are new in this release were added so that analytic workspaces can be deployed as materialized views. Most particularly in the OLAP DML the following statements which have been added and changed that Oracle OLAP uses to insure that it optimally handles OLAP data cells which correspond to relational null facts:

Typically, these OLAP DML statements are automatically generated during the process of creating a materialized view using the Analytic Workspace Manager; you will 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.

Nested Composites Are No Longer Supported

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 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 Oracle11g analytic workspace, IMPORT (EIF) automatically unnests the composites.

Changes to the SQL OLAP_TABLE Function

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 in Oracle11g, no longer need to use a MODEL clause in a SELECT FROM OLAP_TABLE statement to enhance performance.

See also:

Appendix A, "OLAP_TABLE SQL Functions"

OLAP DML Statement Changes for Oracle11g

This section contains listings of the OLAP DML statements that were added, changed, renamed, or deleted in Oracle11g:

Statements Added in Oracle 11g

The following statements have been added to the OLAP DML in Oracle11g. The number in parentheses indicates the specific release in which the statement was added.


$GID_DEPTH system property (11.0.0.0)
$GID_LIST system property (11.0.0.0)
$GID_TYPE system property (11.0.0.0)
$LOOP_AGGMAP system property (11.0.0.0)
$LOOP_DENSE system property (11.0.0.0)
$LOOP_VAR system property (11.0.0.0)
AW FREEZE command (11.0.0.0)
AW PURGE CACHE command (11.0.0.0)
AW THAW command (11.0.0.0)
BIN_TO_NUM function (11.0.0.0)
CHANGEDRELATIONS function (11.0.0.0)
CHANGEDVALUES function (11.0.0.0)
CHARTOROWID function (11.0.0.0)
CURRENT_DATE function (11.0.0.0)
CURRENT_TIMESTAMP function (11.0.0.0)
DATE_FORMAT command (11.0.0.0)
DBTIMEZONE function (11.0.0.0)
EXTRACT function (11.0.0.0)
FROM_TZ function (11.0.0.0)
GROUPINGID function (11.0.0.0)
HIERDEPTH command (11.0.0.0)
INSTR functions (11.0.0.0)
ISEMPTY function (11.0.0.0)
LENGTH functions (11.0.0.0)
LNNVL function (11.0.0.0)
LOCALTIMESTAMP function (11.0.0.0)
NLSSORT function (11.0.0.0)
MODULO function (11.0.0.0)
NA2 function (11.0.0.0)
NAFLAG function (11.0.0.0)
NUMTODSINTERVAL function
NUMTOYMINTERVAL function
PARTITION function (11.0.0.0)
REMAINDER function (11.0.0.0)
ROWIDTOCHAR function (11.0.0.0)
ROWIDTONCHAR function (11.0.0.0)
SESSIONTIMEZONE function (11.0.0.0)
SYS_CONTEXT function (11.0.0.0)
SYSTIMESTAMP function (11.0.0.0)
TO_DSINTERVAL function (11.0.0.0)
TO_TIMESTAMP function (11.0.0.0)
TO_TIMESTAMP_TZ function (11.0.0.0)
TO_YMINTERVAL function (11.0.0.0)
TZ_OFFSET function (11.0.0.0)

Statements Deleted in Oracle 11g

No statements have been deleted from the OLAP DML in Oracle11g.

Statements Changed in Oracle 11g

The following statements have been changed in the OLAP DML in Oracle11g. The number in parentheses indicates the specific release in which the statement was added.


AGGREGATE command (11.0.0.0)
AW function (11.0.0.0)
AW ATTACH command (11.0.0.0)
AW DETACH command (11.0.0.0)
AW LIST command (11.0.0.0)
AW TRUNCATE command (11.0.0.0)
CHGDFN command (11.0.0.0)
CLEAR command (11.0.0.0)
CONVERT function (11.0.0.0)
DEFINE COMPOSITE (11.0.0.0)
DEFINE DIMENSION command (11.0.0.0)
DEFINE PARTITION TEMPLATE (11.0.0.0)
DEFINE VARIABLE command (11.0.0.0)
GROUPINGID command (11.0.0.0)
LOG function (11.0.0.0)
OBJ function (11.0.0.0)

Statements Renamed in Oracle 11g

No statements have been renamed in the OLAP DML in Oracle11g.

OLAP DML Statement Changes for Oracle10g

This section contains listings of the OLAP DML statements that were added, changed, renamed, or deleted in Oracle10g:

Statements Added in Oracle10g

The following statements were added to the OLAP DML in Oracle10g. The number in parentheses indicates the specific release in which the statement was added.


$AGGMAP property (10.1.0.0)
$AGGREGATE_FORCECALC property (10.2.0.0)
$AGGREGATE_FORCEORDER property (10.2.0.0)
$AGGREGATE_FROM property (10.1.0.0)
$AGGREGATE_FROMVAR property (10.1.0.0)
$ALLOCMAP property (10.1.0.0)
$COUNTVAR property (10.1.0.0)
$DEFAULT_LANGUAGE property (10.2.0.0)
ACQUIRE command (10.1.0.0)
AGGCOUNT function (10.2.0.0)
AGGMAP command, DROP DIMENSION statement (10.1.0.0)
AGGMAP command, PRECOMPUTE statement (10.2.0.0)
AGGROPS function (10.2.0.0)
ALLOCMAP command, VALUESET statement (10.1.0.0)
ALLOCOPS function (10.2.0.0)
ARCTAN function (10.1.0.0)
ASCII function (10.1.0.0)
AW TRUNCATE command (10.1.0.3)
BITAND function (10.1.0.0)
CHR function (10.1.0.0)
COALESCE function (10.1.0.0)
DECODE function (10.1.0.0)
DEFINE PARTITION TEMPLATE command (10.1.0.0)
DROP DIMENSION statement of the AGGMAP command (10.1.0.0)
EXP function (10.1.0.0)
GREATEST function (10.1.0.0)
INF_STOP_ON_ERROR option (10.1.0.0)
INITCAP function (10.1.0.0)
INSTR functions (INSTR and INSTRB) (10.1.0.0)
LEAST function (10.1.0.0)
LPAD function (10.1.0.0)
LIMIT BASEDIMS command (10.2.0.0)
LOCK_LANGUAGE_DIMS option (10.2.0.0)
LTRIM function (10.1.0.0)
MAXFETCH option (10.1.0.0)
NULLIF function (10.1.0.0)
NVL function (10.1.0.0)
NVL2 function (10.1.0.0)
ONATTACH program (10.1.0.0)
PARTITIONCHECK function (10.1.0.0)
PRECOMPUTE statement in AGGMAP command (10.2.0.0)
RANK_CALLS option (10.2.0.0)
RANK_CELLS option (10.2.0.0)
RANK_SORTS option (10.2.0.0)
RELATION command (10.2.0.0)
RELEASE command (10.1.0.0)
RESYNC command (10.1.0.0)
REVERT command (10.1.0.0)
RPAD function (10.1.0.0)
RTRIM function (10.1.0.0)
SESSION_NLS_LANGUAGE option (10.2.0.0)
SET1 command (10.1.0.0)
SIGN function (10.1.0.0)
SORT function (10.2.0.0)
SQLFETCH function (10.2.0.0)
STATDEPTH function (10.2.0.0)
STATEQUAL function (10.2.0.2)
STATIC_SESSION_LANGUAGE option (10.2.0.0)
SUBSTR functions, SUBSTR and SUBTRB (10.1.0.0)
SUBSTR functions, SUBSTRC, SUBSTR2, and SUBSTR4 (10.2.0.4)
TRANSLATE function (10.2.0.4)
TRIGGER command (10.1.0.0)
TRIGGER function (10.1.0.0)
TRIGGER_DEFINE program (10.1.0.0)
TRIGGER_AFTER_UPDATE program (10.1.0.0)
TRIGGER_BEFORE_UPDATE program (10.1.0.0)
TRIGGERASSIGN command (10.1.0.0)
TRIM function (10.1.0.0)
USETRIGGERS option(10.1.0.0)
VALUESET statement in ALLOCMAP command (10.1.0.0)
WIDTH_BUCKET function (10.1.0.0)
WRAPERRORS option (10.2.0.0)
WRITABLE function (10.2.0.0)

Statements Deleted in Oracle10g

The following statements were deleted from the OLAP DML in Oracle10g. The number in parentheses indicates the specific release in which the statement was deleted.


AW ALLOCATE (10.1.0.0)
ROLLUP (10.2.0.0)

Statements Changed in Oracle10g

The following OLAP DML statements were significantly changed in Oracle10g. 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.


AGGMAPINFO (10.1.0.0)
AGGREGATE command (10.2.0.0)
AGGREGATE function (10.2.0.0)
ARGUMENT (10.1.0.0)
ANY (10.2.0.0)
AVERAGE (10.2.0.0)
AW function (10.2.0.0)
AW ATTACH (10.1.0.0)
CACHE (10.2.0.0)
CHGDFN (10.2.0.0)
CHGDIMS (10.1.0.3)
COUNT (10.2.0.0)
DEFINE COMPOSITE (10.1.0.0)
DEFINE VARIABLE (10.2.0.0)
DEPRDECL (10.2.0.0)
DEPRDECLSW (10.2.0.0)
DEPRSL (10.2.0.0)
DEPRSOYD (10.2.0.0)
EVERY (10.2.0.0)
FINTSCHED (10.2.0.0)
FPMTSCHED (10.2.0.0)
HIERCHECK (10.2.0.2)
LARGEST (10.2.0.0)
LIMIT command (10.2.0.0)
LIMIT function (10.2.0.0)
MAINTAIN ADD SESSION (10.1.0.0)
MAINTAIN ADD TO PARTITION (10.1.0.0)
NONE (10.2.0.0)
OBJ (10.2.0.3)
RANK (10.2.0.0)
RELATION (for aggregation) (10.2.0.0)
SMALLEST (10.2.0.0)
SORT command (10.1.0.3)
SQL (10.1.0.0)
STDDEV (10.2.0.0)
TALLY (10.2.0.0)
TOTAL (10.2.0.0)
UPDATE (10.1.0.0)
VARIABLE (10.1.0.0)
VALSPERPAGE (10.1.0.0)
VINTSCHED (10.2.0.0)
VPMTSCHED (10.2.0.0)

Statements Renamed in Oracle10g

No OLAP DML statements have been renamed in Oracle10g.

OLAP DML Statement Changes for Oracle9i

This section contains listings of the OLAP DML statement changes in Oracle9i.

Statements Added in Oracle9i

The following statements were added to the OLAP DML in Oracle9i. The number in parentheses indicates the specific release in which the statement was added.


ADD_MONTHS (9.0.0.0)
ALLOCATE (9.2.0.0)
ALLOCERRLOGFORMAT (9.2.0.0)
ALLOCERRLOCHEADER (9.2.0.0)
ALLOCMAP (9.2.0.0)
BASEDIM (9.2.0.0)
BASEVAL (9.2.0.0)
CDA (9.2.0.0)
CEIL (9.0.0.0)
CHANGEBYTES (9.0.0.0)
CHGDIMS (9.2.0.0)
CHILDLOCK (9.2.0.0)
COMMIT (9.2.0.0)
DEADLOCK (9.2.0.0)
ERRORLOG (9.2.0.0)
ERRORMASK (9.2.0.0)
EXTBYTES (9.0.0.0)
FETCH (9.2.0.0)
FINDBYTES (9.0.0.0)
FLOOR (9.0.0.0)
GROUPINGID (9.2.0.0)
HIERHEIGHT command (9.2.0.0)
HIERHEIGHT function (9.2.0.0)
INSBYTES (9.0.0.0)
JOINBYTES (9.0.0.0)
LAST_DAY (9.0.0.0)
LIMITMAPINFO (9.2.0.2)
LIMITSTRICT (9.2.0.2)
MAXBYTES (9.0.0.0)
MAXFETCH (9.0.0.0)
MONTHS_BETWEEN (9.0.0.0)
MULTIPATHHIER (9.0.0.0)
NEW_TIME (9.0.0.0)
NEXT_DAY (9.0.0.0)
NLS Options, specifically:
NLS_CALENDAR (9.0.0.0)
NLS_CURRENCY (9.0.0.0)
NLS_DATE_FORMAT (9.0.0.0)
NLS_DATE_LANGUAGE (9.0.0.0)
NLS_DUAL_CURRENCY (9.0.0.0)
NLS_ISO_CURRENCY (9.0.0.0)
NLS_LANG (9.0.0.0)
NLS_LANGUAGE (9.0.0.0)
NLS_NUMERIC_CHARACTERS (9.0.0.0)
NLS_SORT (9.0.0.0)
NLS_TERRITORY (9.0.0.0)

NULLIF (9.0.0.0)
POUTFILEUNIT (9.2.0.0)
REMBYTES (9.0.0.0)
REPLBYTES (9.0.0.0)
ROLE (9.0.0.0)
SOURCEVAL (9.2.0.0)
SYSDATE (9.0.0.0)
TO_CHAR (9.0.0.0)
TO_DATE (9.0.0.0)
TO_NCHAR (9.2.0.0)
TO_NUMBER (9.0.0.0)
TRACEFILEUNIT (9.2.0.0)
TRIM (9.0.0.0)
USERID (9.0.0.0)

Statements Deleted in Oracle9i

The following statements were deleted from the OLAP DML in Oracle9i. The number in parentheses indicates the specific release in which the statement was deleted.


_UPDATEOLDVERS (9.2.0.0)
_XCALONGTIME (9.0.0.0)
_XCARETRIES (9.0.0.0)
_XCASHORTIME (9.0.0.0)
ALLOWQONS (9.2.0.0)
AW ALLOCATE (10.1.0.0)
CACHEHITS (9.2.0.0)
CACHEMISSES (9.2.0.0)
CACHETRIES (9.2.0.0)
CHARSET (9.0.0.0)
CHDIR (9.2.0.0)
CHDRIVE (9.2.0.0)
COMQUERY (9.0.0.0)
COMSET (9.0.0.0)
COMUNIT (9.0.0.0)
CONNECT (9.0.0.0)
DBEXTENDPATH (9.2.0.0)
DBGSESSION (9.2.0.0)
DBREPORT (9.2.0.0)
DBSEARCHPATH (9.2.0.0)
DBTEMPPATH (9.2.0.0)
DEFINE EXTCALL (9.0.0.0)
DGCART (9.2.0.0)
DIR (9.2.0.0)
DISCONNECT (9.0.0.0)
EPRODUCT (9.2.0.0)
ERELEASE (9.2.0.0)
EXECBREAK (9.0.0.0)
EXECSTART (9.0.0.0)
EXECSTATUS (9.0.0.0)
EXECUTE (9.0.0.0)
EXECWAIT (9.0.0.0)
EXTARGS (9.0.0.0)
FETCH (9.0.0.0) -- SNAPI
FILEMODEMASK (9.2.0.0)
IFCOPY (9.2.0.0)
LONGOBJNAMES (9.0.0.0)
MAXFETCH (9.0.0.0)
MKDIR (9.0.0.0)
NAPAGEFREE (9.2.0.0)
ODBC.CONNECTION (9.0.0.0)
ODBC.CONNLIST (9.0.0.0)
ODBC.DISCONN (9.0.0.0)
ODBC.SOURCE (9.0.0.0)
ODBC.SOURCELIST (9.0.0.0)
PGCACHEHITS (9.2.0.0)
PGCACHEMISSES (9.2.0.0)
PAGEPAUSE (9.2.0.0)
PAGEPROMPT (9.2.0.0)
PAUSE (9.2.0.0)
RETRIEVE (9.0.0.0)
RMDIR (9.0.0.0)
SESSIONQUERY (9.0.0.0)
SHARESESSION (9.0.0.0)
SHELL (9.0.0.0)
SQL CONNECT (9.0.0.0)
SQL DISCONNECT (9.0.0.0)
SQL.DMBS (9.0.0.0)
SQL.DMBSLIST (9.0.0.0)
STRIP (9.2.0.0)
THREADEXTCALL (9.0.0.0)
TRACE (9.2.0.0)
TRANSLATE (9.0.0.0)
TRANSPORT (9.0.0.0)
WATCH (9.2.0.0)
XABORT (9.0.0.0)
XCAPORTNUMBER (9.0.0.0)
XCLOSE (9.0.0.0)
XOPEN (9.0.0.0)

Statements Changed in Oracle9i

The following OLAP DML statements were significantly changed in Oracle9i 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 Oracle9i" for a list of renamed statements.


CONVERT (9.2.0.0)
DECIMALCHAR (9.2.0.0)
EXPORT (9.2.0.0)
FCQUERY (9.2.0.0)
FCSET (9.2.0.0)
FILEOPEN (9.0.0.0)
FILEQUERY (9.0.0.0)
FILEREAD (9.2.0.0)
HIERHEIGHT command (9.2.0.0)
IMPORT (9.0.0.0)
INFILE (9.0.0.0)
LAG (9.2.0.2)
LAGABSPCT (9.2.0.2)
LAGDIF (9.2.0.2)
LAGPCT (9.2.0.2)
LEAD (9.2.0.2)
MODEL (9.2.0.2)
MOVINGAVERAGE (9.2.0.2)
MOVINGMAX (9.2.0.2)
MOVINGMIN (9.2.0.2)
MOVINGTOTAL (9.2.0.2)
NOSPELL (9.2.0.0)
OUTFILE (9.0.0.0)
PROGRAM (9.2.0.0)
PROPERTY (9.0.0.0)
RECURSIVE (9.0.0.0)
RELATION (for aggregation) (9.2.0.2)
RELATION (for allocation) (9.2.0.2)
ROUND (9.0.0.0)
SYSDATE (9.2.0.0)
SYSINFO (9.2.0.2)
SYSTEM (9.2.0.0)
TALLY (10.2.0.0)
THOUSANDSCHAR (9.2.0.0)
YESSPELL (9.2.0.0)

Statements Renamed in Oracle9i

The following OLAP DML statements were renamed in Oracle9i. The number in parentheses indicates the specific release in which the statement was renamed.


DATABASE command to AW command (9.2.0.0)
DATABASE function to AW function (9.2.0.0)
DBDESCRIBE to AWDESCRIBE (9.2.0.0)
DBWAITTIME to AWWAITTIME (9.2.0.0)
DEFAULTDBFSIZE t o DEFAULTAWSEGSIZE (9.2.0.0)
OESEIFVERSION to EIFVERSION (9.2.0.0)