[Home] [Help]
PACKAGE: SYS.DBMS_CUBE_ADVISE
Source
1 package dbms_cube_advise AUTHID CURRENT_USER is
2 ---------------------------------------------------------------------------
3 -- PUBLIC GLOBAL VARIABLES, TYPES AND CONSTANTS
4 ---------------------------------------------------------------------------
5
6 -- Advice statement types
7 DBMS_COAD_ADVTYP_NN CONSTANT BINARY_INTEGER := 1; -- NOT NULL
8 DBMS_COAD_ADVTYP_PKT CONSTANT BINARY_INTEGER := 2; -- Primary Key on Tab
9 DBMS_COAD_ADVTYP_PKV CONSTANT BINARY_INTEGER := 3; -- Primary Key on View
10 DBMS_COAD_ADVTYP_FKT CONSTANT BINARY_INTEGER := 4; -- Foriegn Key on Tab
11 DBMS_COAD_ADVTYP_FKV CONSTANT BINARY_INTEGER := 5; -- Foriegn Key on View
12 DBMS_COAD_ADVTYP_RELDIM CONSTANT BINARY_INTEGER := 6; -- Relational Dimension
13 DBMS_COAD_ADVTYP_MVLOG CONSTANT BINARY_INTEGER := 7; -- MView Log
14 DBMS_COAD_ADVTYP_MVCMP CONSTANT BINARY_INTEGER := 8; -- MView compile
15 -- Default name of constraint exception log table
16 DBMS_COAD_EXCEPTLOGTAB VARCHAR2(65) :=
17 '"'||sys_context('USERENV', 'SESSION_USER')||'"."EXCEPTIONS"';
18
19 -- Trace diagnostics destinations
20 DBMS_COAD_DIAG_NOTRACE CONSTANT BINARY_INTEGER := 0; -- no trace messages
21 DBMS_COAD_DIAG_SRVROUT CONSTANT BINARY_INTEGER := 1; -- trace to serveroutput
22 DBMS_COAD_DIAG_TRCFILE CONSTANT BINARY_INTEGER := 2; -- trace to tracefile
23
24 -- Trace diagnostics flag
25 DBMS_COAD_DIAG BINARY_INTEGER := DBMS_COAD_DIAG_NOTRACE; -- Default no trace
26
27 -- Trace diagnostics log entry types
28 DBMS_COAD_DIAG_NOTE CONSTANT BINARY_INTEGER := 0; -- Note
29 DBMS_COAD_DIAG_BACKTRACE CONSTANT BINARY_INTEGER := 1; -- BACKTRACE
30 DBMS_COAD_DIAG_CKMVPRIV CONSTANT BINARY_INTEGER := 2; -- SQLERRM
31 DBMS_COAD_DIAG_HANDLED CONSTANT BINARY_INTEGER := 3; -- ERROR_STACK
32
33 -- Record and ref cursor type for input to table function get_atr_expr_rc()
34 TYPE lvlList_r IS RECORD (
35 dimOwner VARCHAR2(30),
36 dimName VARCHAR2(30),
37 lvlName VARCHAR2(30));
38
39 TYPE lvlList_t IS REF CURSOR RETURN lvlList_r;
40
41 -- Record and table type for output from table function get_atr_expr_rc()
42 type atrExprList_r is RECORD (
43 dimOwner VARCHAR2(30),
44 dimName VARCHAR2(30),
45 lvlName VARCHAR2(30),
46 atrExpr VARCHAR2(100));
47
48 type atrExprList_t is TABLE of atrExprList_r;
49
50 ---------------------------------------------------------------------------
51 -- PUBLIC PROCEDURES AND FUNCTIONS DECLARATIONS
52 ---------------------------------------------------------------------------
53
54
55 -------------------------------- mv_cube_advice ---------------------------
56 -- NAME:
57 -- mv_cube_advice
58 --
59 -- DESCRIPTION:
60 -- This table function generates records that include a clob containing
61 -- sql ddl/dml that helps allow the broadest range of query rewrite
62 -- transforms possible and mv log based fast refresh for the cube based
63 -- MVs
64 --
65 -- PARAMETERS:
66 -- owner (IN) - Owner of the cube MV
67 -- mvName (IN) - Name of cube organized materialized view
68 -- reqType (IN) - List of advice elements to generate 0-5
69 -- validate (IN) - 1-validate constraint, 0[DEFAULT]-novalidate
70 -- coad_advice_t returning - Record that includes advice sql statments
71 --
72 -- REQTYPEs:
73 -- 0 [DEFAULT] - Generate all advice types that apply
74 -- 1 - column in-line not null constraints
75 -- 2 - primary key constraints
76 -- 3 - foriegn key constraints
77 -- 4 - relational dimension objects
78 -- 5 - mv logs, having 'with primary key'
79 --
80 -- TABLE FUNCTION RECORD FORMAT:
81 -- owner varchar2(30) - Owner of apiObject
82 -- apiObject varchar2(30) - Name of top apiObject
83 -- sqlObjOwn varchar2(30) - Owner of primary subject object of sqlText
84 -- sqlObject varchar2(30) - Name of primary subject object of sqlText
85 -- adviceType number(38,0) - Type of advice statement
86 -- disposition varchar2(2000) - Notes of pre-existing conditions
87 -- sqlText clob - Advice sql statment
88 -- dropText clob - Anti-sqlText statement
89 --
90 -- adviceTypes are declared in package dbms_cube_advise_int as follows
91 -- 1 -- NOT NULL, DBMS_COAD_ADVTYP_NN
92 -- 2 -- Primary Key on Tab, DBMS_COAD_ADVTYP_PKT
93 -- 3 -- Primary Key on View, DBMS_COAD_ADVTYP_PKV
94 -- 4 -- Foriegn Key on Tab, DBMS_COAD_ADVTYP_FKT
95 -- 5 -- Foriegn Key on View, DBMS_COAD_ADVTYP_FKV
96 -- 6 -- Relational Dimension, DBMS_COAD_ADVTYP_RELDIM
97 -- 7 -- MView Log, DBMS_COAD_ADVTYP_MVLOG
98 -- 8 -- MView compile, DBMS_COAD_ADVTYP_MVCMP
99 --
100 -- NOTES:
101 -- This function used metadata collected from the MV itself and additional
102 -- related metadata defined via the OLAP API.
103
104 function mv_cube_advice
105 (
106 owner in varchar2 DEFAULT USER,
107 objName in varchar2,
108 reqType in varchar2 DEFAULT '0',
109 validate in number DEFAULT 0
110 ) return coad_advice_t pipelined;
111
112 /* Sets dbms_coad_diag level flag. Allows diagnostics messages to go to
113 * serveroutput via dbsm_output.
114 * 0 - No trace,
115 * 1 - Trace */
116 procedure trace
117 (
118 diagLevel BINARY_INTEGER
119 );
120
121 /* Produced dbms_output messages based on msgids shown here */
122 procedure log
123 (
124 msgid BINARY_INTEGER DEFAULT 0,
125 msgtxt varchar2 DEFAULT ''
126 );
127
128 /* Set the name of an EXCEPTIONS table. See utlxexcpt.sql */
129 procedure set_cns_exception_log
130 (
131 exceptLogTab varchar2 DEFAULT '"'|| user ||'"."EXCEPTIONS"'
132 );
133
134 /* Table function that returns list of attribute expressions for each
135 * level when given a cursor of type lvlList_t i.e.dimension levels */
136 function get_atr_expr_rc
137 (
138 lvlList in lvlList_t
139 ) return atrExprList_t pipelined;
140
141 /* Returns true if API objName has a colName that matches and is then
142 * mdClass. MEASURE, UNIQUEKEYATTRIBTE, or ANY. */
143 function is_md_class (
144 mdClass in BINARY_INTEGER,
145 owner in varchar2,
146 objName in varchar2,
147 colName in varchar2) return BINARY_INTEGER ;
148
149 /* Gets name of table column primary key constraint, if any */
150 function get_pk_name (
151 tabOwner in varchar2,
152 tabName in varchar2,
153 colName in varchar2) return varchar2;
154
155 /* Gets name of table column foriegn key constraint, if any */
156 function get_fk_name (
157 tabOwner in varchar2,
158 tabName in varchar2,
159 colName in varchar2) return varchar2;
160
161 /* Gets conflicting object info for dimension level mappings, if any */
162 function get_dimlvl_disposition (
163 tabOwner in varchar2,
164 tabName in varchar2,
165 colName in varchar2) return varchar2;
166
167 /* Gets conflicting object info for dimension name, if any */
168 function get_dim_disposition (
169 dimOwner in varchar2,
170 dimName in varchar2) return varchar2;
171
172 /* Gets conflicting object info for hierarchy snowflake joins, if any */
173 function get_dimHierJoin_disposition (
174 tabOwner in varchar2,
175 tabName in varchar2,
176 colName in varchar2) return varchar2;
177
178 /* Gets a level name for a given dimension and column alias */
179 function get_lvl_name (
180 owner in varchar2,
181 dimName in varchar2,
182 colName in varchar2) return varchar2;
183
184 /* Get count of distinct values in colName */
185 function get_colDistinctCount
186 (owner varchar2,
187 tabName varchar2,
188 colName varchar2) return number;
189
190 /* Get first measure column for given MV column alias. */
191 function get_meas_col
192 (mvOwner in varchar2,
193 mvName in varchar2,
194 colAlias in varchar2 ) return varchar2;
195
196 END dbms_cube_advise; /* package spec */