We have created a Materialized with Complete Refresh method. But this is taking around 10 hours to get refreshed. The query in MV is having Millions of records.
Here are columns in the table where MV is created on the table.
EMP_ID,Meas_ID,date_salary_receiving ( here by weekly employee getting salary), sure_emp_id, create_dt, created_by,quantity.
we have created Index in EMP_ID (Unique index) , sure_emp_id,date_salary_receiving
this whole table contains around 10 millions of records. but we have around 60 employees.
we have executed the below queries : Select count(*) from EMP ; -- 10,90,887
select count(distinct(empid) ) -- 76
executed the same for for different columns. All remaining columns have 3 million & 4 millions of records.
is there any possible ways to use Partitions which will improve the performance while retrieving data from table? or is there any other way to increase the performance ?
Please share your thoughts.
btw 10,90,887 is one million Have you considered using fast refresh? Please see below thread if you can make a choice -
As far as I know Partitioning can help only if you are picking subset of records from the table.