Oracle | DBMS_CUBE

Oracle DBMS_CUBE_ADVISE Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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 No

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.