DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: OLAP_OLAP

  1. #1
    Join Date
    Nov 2000
    Posts
    178
    Dear ALL,

    Can anyone know how to call this package DBMS_OLAP.RECOMMEND_MV or detailed info on materialized and data warehousing?

    AC

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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


  3. #3
    Join Date
    Nov 2000
    Posts
    178
    Thanks Hrishy,

    Where is the link to the Oracle document you mentioned?

    AC

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    hi

    here is the doc link

    http://otn.oracle.com/docs/products/...l2.htm#1001435


    hope this helps
    hrishy

  5. #5
    Join Date
    Nov 2000
    Posts
    178
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width