Quote Originally Posted by tabreaz
This query will have some performance overhead becoz of joins. But I think grouping on 10 columns of Financial_Hierarchy_1..10 and & 8 columns of EC1..Ec8 consumes time than grouping 4 columns HR_Financial_Hierarchy.ID, Financial_Hierarchy_Val,HR_EC.EC_ID, HR_EC.EC_VAL
Whether you want to do this or not depends on the trade-off between time spent on maintaining the MV and time spent on querying the result. If the emphasis is to get results for the end user as fast as possible then it makes sense to include the full hierarchy of values and take the hit on the MV maintenance, especially if the MV is extensively used.