Hi dave,

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.
I got your point but how about grouping the result set on 18 columns rather than 4 columns + join (with proper index)..may be the cost of both options are equal (what you say)? and through decode statement all required columns can be used.