If there is benefit to your users in including the additional columns then go ahead with them, and use the USING NO INDEX clause to allow you to define your own index using a permitted number of columns.
The GROUP BY limitation is a different matter, though. If you have to use a larger block size to include those additional columns then that might be more hassle than I would be prepared to suffer.
The two restrictions are independent of each other.
Here my point its not about the number of columns in group by clause or with materalized view. Oracle creates a Unique Index on group by columns which has a restriction of total length of BLOCK SIZE.
HR_Financial_Hierarchy
ID (HR_BASELINE.PrimaryKey...if any such column for join),
FH_ID Number, (to hold 1,2,3,..10)
FH_Val Number or Varchar2 (wat ever be)
)
HR_EC(
ID (HR_BASELINE.PrimaryKey...if any such column for join),
EC_ID Number (1..7)
EC_Val Number)
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
And regarding the Block Size, a Single record of HR_BASELINE consumes more than 1 block, that means lot of block overhead with rowchaining. If having a block size which can fit 1 or 2 records in a single block then it will reduce I/O.
If you are accessing 4 rows from HR_BASELINE which means almost
reading 6-8 blocks if u increase the block size you can reduce the
number of blocks need for accessing those same 4 rows.
Hope its a valid point to both normalize and increasing block size of
the tablespace which holds HR_BASELINE table.
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.
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.
It's the sort of thing that would be amenable to testing.
My first instinct, coming from a DW background, would be to materialize all the required values myself. There are a great many optimizations that could be applied to the refresh of a summary table like this, and some of them would allow materialization of all values without grouping by them all.
For example:
Code:
with agg_data as
(select
date_of_month,
sum(sales_amt) s_amt
from
fct_sales
group by
date_of_month)
select
ad.date_of_month,
d.date_of_quarter,
d.date_of_year,
ad.s_amt
from
agg_data ad,
dim_date_of_month d
where
ad.date_of_month = d.date_of_month
You couldn't define your MV like that, but you could certainly use such code as the basis for a faster refresh than the native MV method.
Reply With Quote
Originally Posted by tabreaz
Bookmarks