Can anyone know how to call this package DBMS_OLAP.RECOMMEND_MV or detailed info on materialized and data warehousing?
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..........
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.
fact_table_filter IN VARCHAR2,
storage_in_bytes IN NUMBER,
retention_list IN VARCHAR2,
retention_pct IN NUMBER := 50);
Table 23-6 RECOMMEND_MV Procedure Parameters
Comma-separated list of fact table names to analyze, or NULL to analyze all fact tables.
Maximum storage, in bytes, that can be used for storing materialized views.
This number must be non-negative.
Comma-separated list of materialized view table names.
A drop recommendation is not made for any materialized view that appears in this list.
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.
Where is the link to the Oracle document you mentioned?
Click Here to Expand Forum to Full Width