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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_CUBE
DBMS_CUBE_ADVISE_SEC
DBMS_CUBE_EXP
DBMS_CUBE_LOG
DBMS_CUBE_UTIL
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx