Oracle | DBMS_CUBE

Oracle DBMS_CUBE_ADVISE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Cube Organized ADvise (COAD) table function mv_cube_advice produces a table of advice records containing constraint DDL to enhance query rewrite opportunities for a cube MV. In-line not null, primary/forign key, relational dimensions and mv logs can be generated. It also defines public table and record types that the mv_cube_advice function returns.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Advice Statements Types
DBMS_COAD_ADVTYP_NN	BINARY_INTEGER	1
DBMS_COAD_ADVTYP_PKT	BINARY_INTEGER	2
DBMS_COAD_ADVTYP_PKV	BINARY_INTEGER	3
DBMS_COAD_ADVTYP_FKT	BINARY_INTEGER	4
DBMS_COAD_ADVTYP_FKV	BINARY_INTEGER	5
DBMS_COAD_ADVTYP_RELDIM	BINARY_INTEGER	6
DBMS_COAD_ADVTYP_MVLOG	BINARY_INTEGER	7
DBMS_COAD_ADVTYP_MVCMP	BINARY_INTEGER	8
Trace Diagnostics Destinations
DBMS_COAD_DIAG_NOTRACE	BINARY_INTEGER	0
DBMS_COAD_DIAG_SRVROUT	BINARY_INTEGER	1
DBMS_COAD_DIAG_TRCFILE	BINARY_INTEGER	2
Trace Diagnostics Log Entry Types
DBMS_COAD_DIAG_NOTE	BINARY_INTEGER	0
DBMS_COAD_DIAG_BACKTRACE	BINARY_INTEGER	1
DBMS_COAD_DIAG_CKMVPRIV	BINARY_INTEGER	2
DBMS_COAD_DIAG_HANDLED	BINARY_INTEGER	3

Data Types SUBTYPE qref2_vc2 IS VARCHAR2(261); /* '"128"."128"' */ SUBTYPE qref3_vc2 IS VARCHAR2(392); /* '"128"."128"."128"' */ -- Record and ref cursor type for input to table function get_atr_expr_rc() TYPE lvlList_r IS RECORD ( dimOwner DBMS_ID, dimName DBMS_ID, lvlName DBMS_ID); TYPE lvlList_t IS REF CURSOR RETURN lvlList_r; -- Record and table type for output from table function get_atr_expr_rc() TYPE atrExprList_r IS RECORD ( dimOwner DBMS_ID, dimName DBMS_ID, lvlName DBMS_ID, atrExpr qref3_vc2); TYPE atrExprList_t IS TABLE OF atrExprList_r;

Dependencies

ALL_CONSTRAINTS	ALL_MVIEW_DETAIL_RELATIONS	DBMS_AW_LIB
ALL_CONS_COLUMNS	ALL_MVIEW_JOINS	DBMS_CUBE
ALL_CUBE_DIMENSIONALITY	ALL_MVIEW_KEYS	DBMS_CUBE_ADVISE_SEC
ALL_CUBE_DIMENSIONS	ALL_MVIEW_LOGS	DBMS_OUTPUT
ALL_CUBE_DIM_LEVELS	ALL_OBJECTS	DBMS_PRIV_CAPTURE
ALL_CUBE_DIM_VIEW_COLUMNS	ALL_TAB_COLS	DBMS_STANDARD
ALL_CUBE_HIER_LEVELS	COAD$CUBE_MVIEWS	DBMS_STATS_INTERNAL
ALL_DIMENSIONS	COAD$INLINE_NOTNULL_CONS	DBMS_SYS_ERROR
ALL_DIM_JOIN_KEY	COAD$MVIEWS_WITH_VIEWS	DBMS_UTILITY
ALL_DIM_LEVELS	COAD_ADVICE_RECT	DUAL
ALL_DIM_LEVEL_KEY	COAD_ADVICE_	PLITBLM
ALL_MVIEWS	DBMS_ASSERT	PRVT_ADVISOR
ALL_MVIEW_AGGREGATES

Documented Yes: Packages and Types Reference

First Available Not known

Pragma PRAGMA supplemental_log_data(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/olap/admin/prvtcoas.plb
{ORACLE_HOME}/olap/admin/dbmscoad.sql
{ORACLE_HOME}/olap/admin/prvtcoad.plb

Subprograms

CHECK_FOR_NESTING	GET_FK_NAME	IS_MD_CLASS
GET_ATR_EXPR_RC	GET_LVL_NAME	LOG
GET_COLDISTINCTCOUNT	GET_MEAS_COL	MV_CUBE_ADVICE
GET_DIMHIERJOIN_DISPOSITION	GET_MVNAME	SET_CNS_EXCEPTION_LOG
GET_DIMLVL_DISPOSITION	GET_NN_NAME	TRACE
GET_DIM_DISPOSITION	GET_PK_NAME

CHECK_FOR_NESTING

Tests if MV has non-merged nested views dbms_cube_advise.check_for_nesting( mvOwner IN VARCHAR2, mvName IN VARCHAR2) RETURN BOOLEAN;

TBD

GET_ATR_EXPR_RC

Returns a list of attribute expressions for each level when given a cursor of type lvlList_t i.e.dimension levels dbms_cube_advise.get_atr_expr_rc(lvlList in lvlList_t) RETURN atrExprList_t PIPELINED;

TBD

GET_COLDISTINCTCOUNT

Get count of distinct values in colName dbms_cube_advise.get_colDistinctCount( owner IN VARCHAR2, tabName IN VARCHAR2, colName IN VARCHAR2) RETURN NUMBER;

TBD

GET_DIMHIERJOIN_DISPOSITION

Gets conflicting object info for hierarchy snowflake joins, if any dbms_cube_advise.get_dimHierJoin_disposition( tabOwner IN VARCHAR2, tabName IN VARCHAR2, colName IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_DIMLVL_DISPOSITION

Gets conflicting object info for dimension level mappings, if any dbms_cube_advise.get_dimlvl_disposition( tabOwner IN VARCHAR2, tabName IN VARCHAR2, colName IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_DIM_DISPOSITION

Gets conflicting object info for dimension name, if any dbms_cube_advise.get_dim_disposition( dimOwner IN VARCHAR2, dimName IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_FK_NAME

Gets name of table column foreign key constraint, if any dbms_cube_advise.get_fk_name( tabOwner IN VARCHAR2, tabName IN VARCHAR2, colName IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_LVL_NAME

Gets a level name for a given dimension and column alias dbms_cube_advise.get_lvl_name( owner IN VARCHAR2, dimName IN VARCHAR2, colName IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_MEAS_COL

Get first measure column for given MV column alias dbms_cube_advise.get_meas_col( mvOwner IN VARCHAR2, mvName IN VARCHAR2, colAlias IN VARCHAR2 ) RETURN VARCHAR2;

TBD

GET_MVNAME

Function to get MV name for a given cube or cube dimension object dbms_cube_advise.get_MVName( owner IN VARCHAR2, objName IN VARCHAR2, objHier IN VARCHAR2 DEFAULT NULL, mvType IN VARCHAR2 DEFAULT 'REFRESH' ) RETURN VARCHAR2;

TBD

GET_NN_NAME

Gets name of table column in-line not null constraint, if any dbms_cube_advise.get_nn_name( tabOwner IN VARCHAR2, tabName IN VARCHAR2, colName IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_PK_NAME

Gets name of table column primary key constraint, if any dbms_cube_advise.get_pk_name( tabOwner IN VARCHAR2, tabName IN VARCHAR2, colName IN VARCHAR2) RETURN VARCHAR2;

TBD

IS_MD_CLASS

Returns true if API objName has a colName that matches and is then
* mdClass. MEASURE, UNIQUEKEYATTRIBTE, or ANY dbms_cube_advise.is_md_class( mdClass IN BINARY_INTEGER, owner IN VARCHAR2, objName IN VARCHAR2, colName IN VARCHAR2) RETURN BINARY_INTEGER ;

TBD

LOG

Produced dbms_output messages based on the msgids dbms_cube_advise.log( msgid IN BINARY_INTEGER DEFAULT 0, msgtxt IN VARCHAR2 DEFAULT '');

TBD

MV_CUBE_ADVICE

Generates records that include a CLOB containing DDL and DML that allows the broadest range of query rewrite transforms possible and mv log based fast refresh for the cube based MVs dbms_cube_advise.mv_cube_advise( owner IN VARCHAR2 DEFAULT sys_context('USERENV', 'CURRENT_USER'), objName IN VARCHAR2, reqType IN VARCHAR2 DEFAULT '0', validate IN NUMBER DEFAULT 0) RETURN coad_advice_t PIPELINED;

TBD

SET_CNS_EXCEPTION_LOG

Set the name of an EXCEPTIONS table

The table must be manually created first by running the DDL in utlxexcpt.sql dbms_cube_advise.set_cns_exception_log(exceptLogTab IN VARCHAR2 DEFAULT '"' || sys_context('USERENV', 'CURRENT_USER') || '"."EXCEPTIONS"');

SQL>@?/rdbms/admin/utlexcpt.sql

      

      Table created.

      

      desc exceptions

      Name         Null?  Type

      ------------ ------ ----------------

      ROW_ID              ROWID

      OWNER               VARCHAR2(128)

      TABLE_NAME          VARCHAR2(128)

      CONSTRAINT          VARCHAR2(128)

      

      exec dbms_cube_advise.set_cns_exception_log('SYS.EXCEPTIONS');

      

      PL/SQL procedure successfully completed.

TRACE

Sets dbms_coad_diag level flag. Allows diagnostics messages to go to serveroutput via dbms_output dbms_cube_advise.trace(diagLevel IN BINARY_INTEGER);

exec dbms_cube_advise.trace(1);

      DBMS_COAD_DIAG: Changing diagLevel from [0] to [1]

      

      PL/SQL procedure successfully completed.

      

      exec dbms_cube_advise.trace(0);

      DBMS_COAD_DIAG: Changing diagLevel from [1] to [0]

      

      PL/SQL procedure successfully completed.