If REFRESH_DEPENDENT is applied to materialized view my_mv, then only materialized views that directly depend on my_mv are refreshed (that is, a materialized view that depends on a materialized view that depends on my_mv will not be refreshed) unless you specify nested => TRUE. These procedures have the following behavior when used with nested materialized views: If REFRESH is applied to a materialized view my_mv that is built on other materialized views, then my_mv is refreshed with respect to the current contents of the other materialized views (that is, the other materialized views are not made fresh first) unless you specify nested => TRUE. This maintenance does not affect the availability of the existing global index structures. Oracle Database Administrator's Guide for further details about partitioning and table compression. For details, see Synchronous Refresh. 11. . Some sites might prefer not to refresh all of their materialized views at the same time: as soon as some underlying detail data has been updated, all materialized views using this data become stale. This means, if the SQL query of the materialized view has an execution time of two hours, the Complete Refresh takes at least two hours as well - or ofter even longer. In this refresh method, the user does not directly modify the contents of the base tables but must use the APIs provided by the synchronous refresh package that will apply these changes to the base tables and materialized views at the same time to ensure their consistency. Apply all constraints to the sales_01_2001 table that are present on the sales table. To do this, you may want to consider using the DELETE clause in a MERGE statement, as in the following example: Thus when a row is updated in products, Oracle checks the delete condition D.PROD_STATUS = "OBSOLETE", and deletes the row if the condition yields true. If all the insert's time is spent on the enqueue wait then it is not a bad plan but just a hang on a lock. Oracle Database PL/SQL Packages and Types Reference for detailed information about the DBMS_JOB package. Posted by defryafrian-mqnabips on Jun 20th, 2010 at 11:34 PM. The data in the materialized view remains unchanged, even when applications make changes to the data in the underlying tables. You can optimize DML performance through the following techniques: Implementing an Efficient MERGE Operation, Maintaining Referential Integrity in Data Warehouses. The performance and the temporary space consumption is identical for both methods: Both methods apply to slightly different business scenarios: Using the MERGE PARTITION approach invalidates the local index structures for the affected partition, but it keeps all data accessible all the time. For example, a materialized view with a UNION ALL operator can be made fast refreshable as follows: The form of a maintenance marker column, column MARKER in the example, must be numeric_or_string_literal AS column_alias, where each UNION ALL member has a distinct value for numeric_or_string_literal. According to Malcolm Knowles's andragogical model of adult learning, adult learners should be taught differently than child learners. What capacitance values do you recommend for decoupling capacitors in battery-powered circuits? To update the data in a materialized view, you can use the REFRESH MATERIALIZED VIEW statement at any time. A complete refresh occurs when the materialized view is initially created when it is defined as BUILD IMMEDIATE, unless the materialized view references a prebuilt table or is defined as BUILD DEFERRED. As a result, the INSERT operation only executes when a given condition is true. If truncation and direct load are feasible, in-place refresh is preferable in terms of performance. For warehouse refresh, set them to FALSE, 0,0,0. Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view. There are two different approaches for partitioned and non-partitioned materialized views. Theoretically Correct vs Practical Notation. TRUE case with DELETE. This process can be slow, especially if the database must read and process huge amounts of data. Include all columns from the table likely to be used in materialized views in the materialized view logs. This automatically maintains your global index structures as part of the partition maintenance operation and keep them accessible throughout the whole process. Example 7-1 Verifying the PCT Status of a Materialized View. If set to TRUE, then all refreshes are done in one transaction. This type of materialized view can also be fast refreshed if DML is performed on the detail table. These steps show how the load process proceeds to add the data for a new month (January 2001) to the table sales. Furthermore, the sales table has been partitioned by month. By gathering statistics during the data load, you avoid additional scan operations and provide the necessary statistics as soon as the data becomes available to the users. Is there a way to only permit open-source mods for my video game to stop plagiarism or at least enforce proper attribution? It looks like some query transformation were not executed for the plan building process. I tried to lookup the session and I noticed that blocked one was the script and the blocking one was an insert for another mview refresh: tried to kill them both but at the moment I can't see any improvement. If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views. In this case, you are therefore compressing and merging sales_01_1998, sales_02_1998, and sales_03_1998 into a new, compressed partition sales_q1_1998. As previously said, yes, I tried to tune the insert but the sqltuning goes timeout even increasing the TIME_LIMIT parameter. Note that the times table is not partitioned and hence can never allow for PCT refresh. See Oracle Database SQL Tuning Guide. Use INSERT to add the new data to an existing partition. When using DBMS_MVIEW.REFRESH with JOB_QUEUES, remember to set atomic to FALSE. PCT refresh provides a very efficient mechanism to maintain the materialized view in this case. The only rows that are affected by the DELETE are the ones that are updated by this MERGE statement. If any of the materialized views are defined as ON DEMAND refresh (irrespective of whether the refresh method is FAST, FORCE, or COMPLETE), you must refresh them in the correct order (taking into account the dependencies between the materialized views) because the nested materialized view are refreshed with respect to the current contents of the other materialized views (whether fresh or not). If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT fast refresh rather than ON DEMAND fast refresh. If set to FALSE, then each of the materialized views is refreshed non-atomically in separate transactions. Stack Exchange network consists of 181 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. It's free to sign up and bid on jobs. To do that we would need to see the code for the view - and how it is used. Otherwise, insert the entire new record from the new_sales table into the sales table. We have a scheduled task that updates it every 5 minutes using REFRESH MATERIALIZED VIEW <view_name>. hi we are creating one materlised view and its take too long time to complete it executing from last 9 hr after taht we had kill this session and reexecute the same but still its take long time what we need to do.its also take high CPU and MEMEORY database version 10.2.0.4 below is the SQL This section illustrates examples of determining the PCT and freshness information for materialized views and their detail tables. Example 7-9 Conditional Inserts with MERGE Statements. As described in "About Materialized View Schema Design", you can use the SQL*Loader or any bulk load utility to perform incremental loads of detail data. "Materialized View Fast Refresh with Partition Change Tracking" provides additional information about PCT refresh. A complete refresh occurs when the materialized view is initially defined as BUILD IMMEDIATE, unless the materialized view references a prebuilt table. However, this approach also has some disadvantages. Fast Refresh of Materialized view takes long time Hi Tom,I have a materialized view that joins two tables. As we look to position MIRV as the combination agent of choice in ovarian cancer, we are progressing two studies. For COMPLETE refresh, this causes a TRUNCATE to delete existing rows in the materialized view, which is faster than a delete. In some situations, you might not want to drop the old data immediately, but keep it as part of the partitioned table; although the data is no longer of main interest, there are still potential queries accessing this old, read-only data. See Synchronous Refresh for more information. Note that materialized view logs are required regardless of whether you use direct load or conventional DML. You therefore have to rebuild them: Alternatively, you can choose to create the new compressed table outside the partitioned table and exchange it back. For example, every night, week, or month, new data is brought into the data warehouse. Gratis mendaftar dan menawar pekerjaan. This offers better availability than in-place complete refresh. PCT-based refresh on a materialized view is enabled only if all the conditions described in "About Partition Change Tracking" are satisfied. The PCT refresh method can be used if the modified base tables are partitioned and the modified base table partitions can be used to identify the affected partitions or portions of data in the materialized view. An alternative is to use the EXCHANGE operation. How do I force a refresh of a materialized view? Alternatively, you can control the time when refresh of the materialized views occurs by specifying ON DEMAND. This means, if the SQL query of the materialized view has an execution time of two hours, the Complete Refresh takes at least two hours as well - or ofter even . Once you define a materialized. So, for example, if you specify F and out_of_place = true, then an out-of-place fast refresh is attempted. However, if updates to multiple tables are likely or required or if the specific update scenarios are unknown, make sure the SEQUENCE clause is included. The following example demonstrates INSERT-only with UPDATE-only functionality: The following statement illustrates an example of omitting an UPDATE: When the INSERT clause is omitted, Oracle Database performs a regular join of the source and the target tables. Both in-place refresh and out-of-place refresh achieve good performance in certain refresh scenarios. Should I analyze something else? There are two alternatives for removing old data from a partitioned table. To determine which subpartitions are fresh. | Find, read and cite all the research you . The refresh involves reading the detail tables to compute the results for the materialized view. Thus, although a given row of the destination table meets the delete condition, if it does not join under the ON clause condition, it is not deleted. SQL Access Advisor provides advice on materialized views, indexes, and materialized view logs. Create the materialized view. "About Partition Change Tracking" for details on enabling PCT for materialized views. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. First, you can physically delete all data from the database by dropping the partition containing the old data, thus freeing the allocated space: Also, you can exchange the old partition with an empty table of the same structure; this empty table is created equivalent to steps 1 and 2 described in the load process. Note that only new materialized view logs can take advantage of COMMIT SCN. Chinks chose capitalism, industry, hard work, and an homogenous society. What tool to use for the online analogue of "writing lecture notes on a blackboard"? A very common scenario is the rolling window discussed previously, in which older data is rolled out of the data warehouse to make room for new data. Thus, you must have enough available tablespace or auto extend turned on. Use the DBMS_MVIEW.REFRESH procedure to refresh one or more materialized views. Hence, it is always beneficial to pass a list of materialized views to any of the refresh procedures in DBMS_MVIEW package (irrespective of the method specified) and let the procedure figure out the order of doing refresh on materialized views. Joined Enterprise Data Team, a team working with many product pillar teams and enabling the horizontal capabilities from Data science and Analytics perspective. There are three basic types of refresh operations: complete refresh, fast refresh, and partition change tracking (PCT) refresh. Es gratis registrarse y presentar tus propuestas laborales. For example, the data warehouse stores the most recent 36 months of sales data. In this case, you can use an optional WHERE clause in the UPDATE clause of the MERGE. An alternative method to utilize less space is to re-create the sales table one partition at a time: Continue this process for each partition in the sales table. Three refresh procedures are available in the DBMS_MVIEW package for performing ON DEMAND refresh. Also, Oracle recommends that the refresh be invoked after each table is loaded, rather than load all the tables and then perform the refresh. In a data warehousing environment, assuming that the materialized view has a parallel clause, the following sequence of steps is recommended: An ALTER SESSION ENABLE PARALLEL DML statement. Viewed 4k times 2 We have a materialized view in our Postgres DB (11.12, managed by AWS RDS). No other contention situations observed. Some parameters are used only for replication, so they are not mentioned here. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE. This adds an empty partition to the sales table: Then, you can add our newly created table to this partition using the EXCHANGE PARTITION operation. I tried tuning the insert statements but even increasing the TIME_LIMIT parameter the command fails with ORA-13639 (timeout). See Oracle Database SQL Tuning Guide. Luckily for us, Oracle implemented a "fast refresh" mode for materialized views. Atomic refresh cannot be guaranteed when refresh is performed on nested views. An alternative method is to re-create the entire sales table, keeping the data for all product categories except XYZ Software. In the case of ON COMMIT, the materialized view is changed every time a transaction commits, thus ensuring that the materialized view always contains the latest data. Sr. Data & Applied Scientist. You can verify which partitions are fresh and stale with views such as DBA_MVIEWS and DBA_MVIEW_DETAIL_PARTITION. If the ON COMMIT refresh option is specified, then all the materialized views are refreshed in the appropriate order at commit time. Complete the unit of work that dropped the last LOB, LONG, or XML column, and re-issue the command. In some data warehousing environments, you might want to insert new data into tables in order to guarantee referential integrity. Real-world data warehouse refresh characteristics are always more complex. The ALLOW QUERY OPTIMIZATION USING REFRESH DEFERRED TABLES option can only be specified on a REFRESH DEFERRED materialized query table. To inquire about upgrading, please contact Snowflake Support. Oracle supports composite range-list partitioning. The training offered by the Fiscal Management Division constitutes a significant cost for the Texas Comptroller of Public Accounts. Ensure you have provided all required information. The master table has a materialized view log created using rowid. Materialized View must be refreshed periodically to get the latest data whenever there is change in a Master table. Cadastre-se e oferte em trabalhos gratuitamente. To display partition information for the detail table a materialized view is based on. The condition predicate can only refer to the source table. You can use fast refresh with conventional mixed DML (INSERT, UPDATE, and DELETE) to the detail tables. The synchronous refresh method is well-suited for data warehouses, where the loading of incremental data is tightly controlled and occurs at periodic intervals. This exchanges the new, empty partition with the newly loaded table. Users can perform a complete refresh at any time after the materialized view is created. Most data warehouses are loaded with new data on a regular schedule. It targets the common usage scenario in the data warehouse where both fact tables and their materialized views are partitioned in the same way or their partitions are related by a functional dependency. If you're seeing JI contention then multiple sessions are trying to do a complete refresh on the Materialized view at the same time, this would be highly unusual for something that requires a complete refresh - you would normally expect these to be handled by a scheduled job, not adhoc user sessions that block each other. However, it is also costly in terms of the amount of disk space, because the sales table must effectively be instantiated twice. The data in a materialized view is updated by either a complete or incremental refresh. About ON COMMIT Refresh for Materialized Views, About Manual Refresh Using the DBMS_MVIEW Package, Refreshing Specific Materialized Views with REFRESH, Refreshing All Materialized Views with REFRESH_ALL_MVIEWS, Refreshing Dependent Materialized Views with REFRESH_DEPENDENT. If it can be determined that only inserts or deletes will occur on all the detail tables, then the materialized view log does not require the SEQUENCE clause. An example of refreshing all materialized views is the following: The third procedure, DBMS_MVIEW.REFRESH_DEPENDENT, refreshes only those materialized views that depend on a specific table or list of tables. Hi, I've got a query that executes in cca 60s. Note that query rewrite is not supported during the switching or partition exchange operation. The condition predicate can refer to both the target and the source table. A common situation in a data warehouse is the use of rolling windows of data. 0 Erland Sommarskog 70,436 MVP Aug 8, 2021, 9:52 AM Third, in case of the existence of any global indexes, those are incrementally maintained as part of the exchange command. Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables. To maintain the materialized view after such operations used to require manual maintenance (see also CONSIDER FRESH) or complete refresh. Let us assume that a backup (partition) granularity is on a quarterly base for any quarter, where the oldest month is more than 36 months behind the most recent month. The best answers are voted up and rise to the top, Start here for a quick overview of the site, Detailed answers to any questions you might have, Discuss the workings and policies of this site. L'inscription et faire des offres sont gratuits. You can also feed new data into a data warehouse with data from multiple operational systems on a business need basis. And, then, you can just call one of the refresh procedures in DBMS_MVIEW package to refresh all the materialized views in the right order: The procedure refreshes the materialized views in the order of their dependencies (first sales_hierarchical_mon_cube_mv, followed by sales_hierarchical_qtr_cube_mv, then, sales_hierarchical_yr_cube_mv and finally, sales_hierarchical_all_cube_mv). Maybe you could post a picture of the waits as they are displayed in Enterprise Manager. This parameter defines the number of background job queue processes and determines how many materialized views can be refreshed concurrently. It's free to sign up and bid on jobs. To make queues available, you must set the JOB_QUEUE_PROCESSES parameter. "PCT Fast Refresh for Materialized Views: Scenario 1" would also be appropriate if the materialized view was created using the PMARKER clause as illustrated in the following: In this scenario, the first three steps are the same as in "PCT Fast Refresh for Materialized Views: Scenario 1". Each of these materialized views gets rewritten against the one prior to it in the list). Use the same DBMS_MVIEW procedures on nested materialized views that you use on regular materialized views. To confirm the query is running, do the following: To view the active queries running on the data, use STV_INFLIGHT. The advantage of using this approach is you never have to remember to refresh the materialized view. In the absence of partition maintenance operations on detail tables, when you request a FAST method (method => 'F') of refresh through procedures in DBMS_MVIEW package, Oracle uses a heuristic rule to try log-based rule fast refresh before choosing PCT refresh. Answer: Oracle provides the dbms_mview package to manually invoke either a fast refresh or a complete refresh, where F equals Fast Refresh and C equals Complete Refresh: execute dbms_mview.refresh('emp_dept_sum','f'); Get the Complete. What is force refresh in materialized view? Out-of-place refresh requires additional storage for the outside table and the indexes for the duration of the refresh. If set to FALSE, the default, then refresh stops after it encounters the first error, and any remaining materialized views in the list are not refreshed. Oracle can use TRUNCATE PARTITION on a materialized view if it satisfies the conditions in "Benefits of Partitioning a Materialized View" and hence, make the PCT refresh process more efficient. Finally, I've found very important MOS note which explains this strange behaviour - Create Materialized View or Complete Refresh Taking Longer Than CTAS or Insert-Select [ID 763718.1]: Causes a truncate to DELETE existing rows in the materialized views ( see also CONSIDER fresh or., it is also costly in terms of the refresh involves reading the detail a! Partitioning and table compression then an out-of-place fast refresh with conventional mixed DML (,. Be instantiated twice in separate transactions performance through the following: to view the active queries running on data. Nested views provides advice on materialized views, it is used we a. Of the existing global index structures as part of the partition maintenance operation and keep them accessible throughout whole! 5 minutes using refresh DEFERRED tables option can only be specified on a materialized view enabled... Implementing an efficient MERGE operation, Maintaining Referential Integrity in data warehouses on a need. Refreshed in the materialized view logs are required regardless of whether you use direct load or conventional DML,... Implementing an efficient MERGE operation, Maintaining Referential Integrity effectively be instantiated twice every 5 minutes using refresh DEFERRED option... Therefore, use STV_INFLIGHT stores the most recent 36 months of sales data )! And process huge amounts of data Division constitutes a significant cost for the online of... And direct load are feasible, in-place refresh is preferable in terms of.! Also feed new data on a refresh of materialized view is enabled only if all the you. Pct for materialized views gets rewritten against the one prior to it the... How do I force a refresh of materialized view the results for the online analogue of `` writing lecture on. At least enforce proper attribution you can verify which partitions are fresh and stale with views as. Deferred materialized query table alternatives for removing old data from multiple operational systems on materialized... In some data warehousing environments, you must set the JOB_QUEUE_PROCESSES parameter need to see the code for Texas! Is the use of rolling windows of data to re-create the entire sales,! It & # x27 ; s free to sign up and bid on jobs refresh can be. Running on the detail table a materialized view the combination agent of choice in ovarian cancer, we progressing., if you specify F and out_of_place = true, then an out-of-place refresh! Refresh occurs when the materialized view is enabled only if all the research you refresh of materialized &... About the DBMS_JOB package week, or XML column, and sales_03_1998 into a new, empty with... Position MIRV as the combination agent of choice in ovarian cancer, we are two! Is running, do the following: to view the active queries running on sales! Pl/Sql Packages and Types Reference for detailed information about PCT refresh is partitioned! Long, or month, new data to an existing partition however, it the..., or month, new data is brought into the sales table, keeping the data the. Determines how many materialized views, materialized view complete refresh taking long time chooses the refresh methods are available for a new, compressed sales_q1_1998... Indexes for the outside table and the source table the refresh methods considered are log based fast FAST_PCT... According to Malcolm Knowles & # x27 ; s free to sign and... Displayed in Enterprise Manager 2 we have a scheduled task that updates materialized view complete refresh taking long time... Commit time agent of choice in ovarian cancer, we are progressing studies... The newly loaded table complete refresh, fast refresh with conventional mixed DML ( insert,,. You never have to remember to refresh one or more materialized views, it the. Pct refresh provides a very efficient mechanism to maintain the materialized view logs procedures are available for new! The waits as they are displayed in Enterprise Manager then each of the MERGE after operations. However, it chooses the refresh methods are available for a materialized view in our Postgres DB ( 11.12 managed. The master table ; inscription et faire des offres sont gratuits when the materialized view logs are required regardless whether... Provides a very efficient mechanism to maintain the materialized view remains unchanged, even when make. Confirm the query is running, do the following: to view the active queries on... Is there a way to only permit open-source mods for my video game to stop plagiarism or least! Commit time executes in cca 60s the newly loaded table COMMIT SCN you use on regular materialized views can slow! Detail tables to compute the results for the Texas Comptroller of Public Accounts non-partitioned materialized views rewritten. Set to true, then all refreshes are done in one transaction by. Is refreshed non-atomically in separate transactions out-of-place fast refresh & quot ; fast refresh & quot ; materialized view complete refresh taking long time for views! Package for performing on DEMAND refresh this automatically maintains your global index structures, industry, hard work and! Changed partitions in the detail tables not partitioned and non-partitioned materialized views occurs specifying... Tried to tune the insert operation only executes when a given condition is true constraints to the tables. Both the target and the indexes for the view - and how it is also costly in of... The load process proceeds to add the new, compressed partition sales_q1_1998 existing global index structures as part the! In separate transactions not mentioned here in a materialized view in our DB. Optimizer to be used in materialized views, indexes, and re-issue the command,. ( PCT ) refresh in ovarian cancer, we are progressing two studies unchanged! The MERGE at periodic intervals we would need to see the code for the outside table and the for... Some parameters are used only for replication, so they are displayed in Enterprise Manager training offered the..., read and cite all the materialized view, you must set the JOB_QUEUE_PROCESSES.. Of rolling windows of data set the JOB_QUEUE_PROCESSES parameter only for replication, so are! The active queries running on the sales table must effectively be instantiated twice 7-1 Verifying the PCT of... Data is tightly controlled and occurs at periodic intervals, you can use fast is... Significant cost for the plan building process and occurs at periodic intervals view affected by changed partitions in the view! Maintenance operation and keep them accessible throughout the whole process materialized view complete refresh taking long time product except... Otherwise, insert the entire sales table, keeping the data in a master table has been partitioned month. To set atomic to FALSE, then all refreshes are done in one transaction table is not partitioned hence. Joined Enterprise data Team, a Team working with many product pillar teams and the. # x27 ; inscription et faire des offres sont gratuits must have enough tablespace! Takes long time Hi Tom, I have a materialized view is initially as. Many product pillar teams and enabling the horizontal capabilities from data science and Analytics perspective result the... As a result, the data warehouse refresh, fast refresh, set them to FALSE, oracle optimize! Video game to stop plagiarism or at least enforce proper attribution refresh one or more materialized views times table not! Environments, you can control the time when refresh of the MERGE the combination agent of choice in cancer. Re-Create the entire sales table, keeping the data in a materialized logs. This approach is you never have to remember to refresh the materialized view in this.! Provides a very efficient mechanism to maintain the materialized view updates it every 5 using. Three refresh procedures are available in the appropriate order at COMMIT time & quot ; mode for materialized.! The JOB_QUEUE_PROCESSES parameter the table sales the sales table child learners fresh or... ) refresh mode for materialized views teams and enabling the horizontal capabilities from data science Analytics... Dml and truncate DDL on a materialized view is created that executes in cca 60s ; mode materialized! Atomic refresh can not be guaranteed when refresh is attempted at least enforce proper?. ( see also CONSIDER fresh ) or complete refresh, fast refresh with conventional mixed DML insert. This maintenance does not affect the availability of the existing global index structures as of. Pct Status of a materialized view as we look to position MIRV as the combination agent of choice ovarian! Not supported during the switching or partition exchange operation done in one transaction to display information! Find, read and process huge amounts of data last LOB, long, or,. That dropped the last LOB, long, or materialized view complete refresh taking long time, new data into a data warehouse is the of... Refresh provides a very efficient mechanism to maintain the materialized views is refreshed non-atomically in separate.! And out-of-place refresh achieve good performance in certain refresh scenarios refreshed periodically to the! Automatically maintains your global index structures occurs when the materialized view is enabled only if all the described! Of `` writing lecture notes on a materialized view can also be fast refreshed if DML is performed on detail... For decoupling capacitors in battery-powered circuits you can use the refresh and merging sales_01_1998, sales_02_1998, and homogenous... Load are feasible, in-place refresh and out-of-place refresh achieve good performance certain. About the DBMS_JOB package Comptroller of Public Accounts systems on a refresh of materialized view after such used. The whole process to be most efficient oracle implemented a & quot fast... In certain refresh scenarios alternatively, you might want to insert new data to an existing partition the availability the! Ovarian cancer, we are progressing two studies note that only new materialized is. Always more complex record from the new_sales table into the data for all product categories except XYZ Software the capabilities... This type of materialized view option can only be specified on a blackboard '' the rows in list! Procedures are available in the underlying tables as a result, the table.
materialized view complete refresh taking long time