1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Materialized view

Discussion in 'SQL PL/SQL' started by siddig, Jul 22, 2009.

  1. siddig

    siddig Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I Am using materialized view to pull huge data from remote site...
    This is the first time using materialized view. For the first it takes a long time due to poor speed. I would like to know if there are any options available to specify the time out.
    Please guide me..

    Regards

    Siddiq
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi Siddiq,

    Go through this, maybe it will help

    When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. To use the fast warehouse refresh facility, the ON DEMAND mode must be specified, then the materialized view can be refreshed by calling one of the procedures in DBMS_MVIEW.

    The DBMS_MVIEW package provides three different types of refresh operations.

    * DBMS_MVIEW.REFRESH

    Refresh one or more materialized views.
    * DBMS_MVIEW.REFRESH_ALL_MVIEWS

    Refresh all materialized views.
    * DBMS_MVIEW.REFRESH_DEPENDENT

    Manual Refresh Using the DBMS_MVIEW Package

    Three different refresh procedures are available in the DBMS_MVIEW package for performing ON DEMAND refresh and they each have their own unique set of parameters. To use this package, Oracle8 queues must be available, which means that the following parameters must be set in the initialization parameter file. If queues are unavailable, refresh will fail with an appropriate message.
    Required Initialization Parameters for Warehouse Refresh

    * JOB_QUEUE_PROCESSES

    The number of background processes. Determines how many materialized views can be refreshed concurrently.
    * JOB_QUEUE_INTERVAL

    In seconds, the interval between which the job queue scheduler checks to see if a new job has been submitted to the job queue.
    * UTL_FILE_DIR

    Determines the directory where the refresh log is written. If unspecified, no refresh log will be created.

    These packages also create a log which, by default, is called refresh.log and is useful in helping to diagnose problems during the refresh process. This log file can be renamed by calling the procedure DBMS_OLAP.SET_LOGFILE_NAME ('log filename').

    Refresh Specific Materialized Views

    The DBMS_MVIEW.REFRESH procedure is used to refresh one or more materialized views that are explicitly defined in the FROM list. This refresh procedure can also be used to refresh materialized views used by replication, so not all of the parameters are required. The required parameters to use this procedure are:

    * The list of materialized views to refresh, delimited by a comma

    * The refresh method: A-Always, F-Fast, ?-Force, C-Complete

    * Rollback segment to use

    * Continue after errors

    When refreshing multiple materialized views, if one of them has an error while being refreshed, the entire job will continue if set to TRUE.
    * The following four parameters should be set to FALSE, 0,0,0

    These are the values required by warehouse refresh, since these parameters are used by the replication process.
    * Atomic refresh

    If set to TRUE, then warehouse refresh is not used. It uses the snapshot/replication refresh instead. If set to FALSE, the warehouse refresh method is used and each refresh operation is performed within its own transaction.

    Therefore, to perform a fast refresh on the materialized view store_mv, the package would be called as follows:

    DBMS_MVIEW.REFRESH('STORE_MV', 'A', '', TRUE, FALSE, 0,0,0, FALSE);

    Multiple materialized views can be refreshed at the same time and they don't all have to use the same refresh method. To give them different refresh methods, specify multiple method codes in the same order as the list of materialized views (without commas). For example, the following specifies that store_mv will be completely refreshed and product_mv will receive a fast refresh.

    DBMS_MVIEW.REFRESH('STORE_MV,PRODUCT_MV', 'AF', '', TRUE, FALSE, 0,0,0, FALSE);

    Refresh All Materialized Views

    An alternative to specifying the materialized views to refresh is to use the procedure DBMS_MVIEW.REFRESH_ALL_MVIEWS. This will result in all materialized views being refreshed. If any of the materialized views fails to refresh, then the number of failures is reported.

    The parameters for this procedure are:

    * The number of failures

    * The datatype number

    * The refresh method: A-Always, F-Fast, ?-Force, C-Complete

    * Rollback segment to use

    * Continue after errors

    An example of refreshing all materialized views is:

    DBMS_MVIEW.REFRESH_ALL_MVIEWS ( failures,'A','',FALSE,FALSE);

    Refresh Dependent

    The third option is the ability to refresh only those materialized views that depend on a specific table using the procedure DBMS_MVIEW. REFRESH_DEPENDENT. For example, suppose the changes have been received for the orders table but not customer payments. The refresh dependent procedure can be called to refresh only those materialized views that reference the ORDER table.

    The parameters for this procedure are:

    * The number of failures

    * The dependent table

    * The refresh method: A-Always, F-Fast, ?-Force, C-Complete

    * Rollback segment to use

    * Continue after errors

    A Boolean parameter. If set to TRUE, the number_of_failures output parameter will be set to the number of refreshes that failed, and a generic error message will indicate that failures occurred. The refresh log will give details of each of the errors, as will the alert log for the instance. If set to FALSE, the default, then refresh, will stop after it encounters the first error, and any remaining materialized views in the list will not be refreshed.
    * Atomic refresh

    A Boolean parameter.

    In order to perform a full refresh on all materialized views that reference the ORDERS table, use:

    DBMS_mview.refresh_dependent (failures, 'ORDERS', 'A', '', FALSE, FALSE );

    Tips for Refreshing Using Warehouse Refresh

    If the process that is executing DBMS_MVIEW.REFRESH is interrupted or the instance is shut down, any refresh jobs that were executing in job queue processes will be requeued and will continue running. To remove these jobs, use the DBMS_JOB.REMOVE procedure.
    Materialized Views with Joins and Aggregates

    Here are some guidelines for using the refresh mechanism for materialized views with joins and aggregates.

    1. The warehouse refresh facility only operates on materialized views containing aggregates.

    2. Always load new data using the direct-path option if possible. Avoid deletes and updates because a complete refresh will be necessary. However, you can drop a partition on a materialized view and do a fast refresh.

    3. Place fixed key constraints on the fact table, and primary key constraints from the fact table to the dimension table. Doing this enables refresh to identify the fact table, which helps fast refresh.

    4. During loading, disable all constraints and re-enable when finished loading.

    5. Index the materialized view on the foreign key columns using a concatenated index.

    6. To speed up fast refresh, make the number of job queue processes greater than the number of processors.

    7. If there are many materialized views to refresh, it is faster to refresh all in a single command than to call them individually.

    8. Make use of the "?" refresh method to ensure getting a refreshed materialized view that can be used to query rewrite. If a fast refresh cannot be done, a complete refresh will be performed. Whereas, if a fast refresh had been requested and there was nothing to do, the materialized view would not be refreshed at all.

    9. Try to create materialized views that are fast refreshable because it is quick.

    10. If a summary contains data that is based on data which is no longer in the fact table, maintain the materialized view using fast refresh. If no job queues are started, two job queue processes will be started by the refresh. This can be modified by:

    ALTER SYSTEM SET JOB_QUEUE_PROCESSES = value

    11. In general, the more processors there are, the more job queue processes should be created. Also, if you are doing mostly complete refreshes, reduce the number of job queue processes, since each refresh consumes more system resources than a fast refresh. The number of job queue processes limits the number of materialized views that can be refreshed concurrently. In contrast, if you perform mostly fast refreshes, increase the number of job queue processes.

    Recommended Initialization Parameters for Parallelism

    The following parameters

    * PARALLEL_MAX_SERVERS should be set high enough to take care of parallelism.

    * SORT_AREA_SIZE should be less than HASH_AREA_SIZE.

    * OPTIMIZER_MODE should equal CHOOSE (cost based optimization).

    * OPTIMIZER_PERCENT_PARALLEL should equal 100.

    Monitoring a Refresh

    While a job is running, a SELECT * FROM V$SESSION_LONGOPS statement will tell you the progress of each materialized view being refreshed.

    To look at the progress of which jobs are on which queue, use a SELECT * FROM DBA_JOBS_RUNNING statement.

    The table ALL_MVIEW_ANALYSIS contains the values, as a moving average, for the time most recently refreshed and the average time to refresh using both full and incremental methods.

    Refresh will schedule the long running jobs first. Use the refresh log to check what each refresh did.
    Tips after Refreshing Materialized Views

    After you have performed a load or incremental load and rebuilt the detail table indexes, you need to re-enable integrity constraints (if any) and refresh the materialized views and materialized view indexes that are derived from that detail data. In a data warehouse environment, referential integrity constraints are normally enabled with the NOVALIDATE or RELY options. An important decision to make before performing a refresh operation is whether the refresh needs to be recoverable. Because materialized view data is redundant and can always be reconstructed from the detail tables, it may be preferable to disable logging on the materialized view. To disable logging and run incremental refresh non-recoverably, use the ALTER MATERIALIZED VIEW...NOLOGGING statement prior to REFRESH.

    If the materialized view is being refreshed using the ON COMMIT method, then, following refresh operations, the alert log (alert_ <SID>.log) and the trace file (ora_<SID>_number.trc) should be consulted to check that no errors have occurred.
     
  3. siddig

    siddig Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    Thanks very much for your corporation. I able to create materialized view except one table name tbl_items_serials, i cannot create materialized view. It is not showing any error. but in the task manager showing the sql not responding. I deleted all the record for the table and i tried to create materialized view but still it is not created.
    =====
    Is there something prevent to create materialzed view?

    Regard,