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);
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;
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;