0

In our oracle query, I used materialized view to improve the performance and saw a drastic change in performance time of query, response time is in .17 seconds now rather 30 seconds so its working in case we are working on historical data set and need to have one time refresh of materialized view but now requirement is if base table of materialized view would be updating frequently in that case it would be working as the refresh activity of materialized view would be consuming time as well, currently the base table has around 170000 records but in future definitely going to increase so is there any solution or option to resolve this. As i have tried to apply Indexes as well on the table but not able to achieve response time within .17 sec or around the last response time based on index creation was 18 sec.

Harsh
  • 125
  • 1
  • 2
  • 6
  • 1
    30 seconds for "only" 1700'00 records sounds a lot - even without any index. – Wernfried Domscheit Oct 20 '21 at 13:29
  • 1
    You can create a "FAST REFRESH" materialized view, you can refresh it even after every commit on the base table. However, there a several restrictions. see https://stackoverflow.com/questions/41465445/what-is-the-difference-between-complete-refresh-and-fast-refresh-in-materialized/41465650#41465650 or https://stackoverflow.com/questions/49578932/materialized-view-in-oracle-with-fast-refresh-instead-of-complete-dosnt-work/49579378#49579378 – Wernfried Domscheit Oct 20 '21 at 13:31
  • can you provide the code of the materialized view ?? – Roberto Hernandez Oct 20 '21 at 15:16
  • @WernfriedDomscheit - I am still thinking what another way which I can use instead this martialized concept to handle live data or live change to send the alert .... – Harsh Oct 20 '21 at 16:59
  • Yeah, Agreed fast refersh is the option which can help to do the activity, thanks @Wernfried Domscheit – Harsh Dec 17 '21 at 23:06

0 Answers0