-
Dear ALL,
Can anyone know how to call this package DBMS_OLAP.RECOMMEND_MV or detailed info on materialized and data warehousing?
AC
-
hi
if you acesses to oracle docs you can refer to them as thsi is a lengthy topic to discuss..........i am posting here the contents of the docs..........
RECOMMEND_MV procedure
This procedure generates a set of recommendations about which materialized views should be created, retained, or dropped, based on an analysis of table and column cardinality statistics gathered by ANALYZE.
The recommendations are based on a hypothetical workload in which all possible queries in the data warehouse are weighted equally. This procedure does not require or use the workload statistics tables collected by Oracle Trace, but it works even if those tables are present.
Dimensions must have been created, and there must be foreign key constraints that link the dimensions to fact tables.
Recommending materialized views with a hypothetical workload is appropriate in a DBA-less environment where ease of use is the primary consideration; however, if a workload is available in the default schema, it should be used.
DBMS_OLAP.RECOMMEND_MV (
fact_table_filter IN VARCHAR2,
storage_in_bytes IN NUMBER,
retention_list IN VARCHAR2,
retention_pct IN NUMBER := 50);
Parameters
Table 23-6 RECOMMEND_MV Procedure Parameters
Parameter Description
fact_table_filter
Comma-separated list of fact table names to analyze, or NULL to analyze all fact tables.
storage_in_bytes
Maximum storage, in bytes, that can be used for storing materialized views.
This number must be non-negative.
retention_list
Comma-separated list of materialized view table names.
A drop recommendation is not made for any materialized view that appears in this list.
retention_pct
Number between 0 and 100 that specifies the percent of existing materialized view storage that must be retained, based on utilization on the actual or hypothetical workload.
A materialized view is retained if the cumulative space, ranked by utilization, is within the retention threshold specified (or if it is explicitly listed in retention_list). Materialized views that have a NULL utilization (e.g., non-dimensional materialized views) are always retained.
hth
hrishy
-
Thanks Hrishy,
Where is the link to the Oracle document you mentioned?
AC
-
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|