This content has been marked as final. Show 5 replies
How are you going to use the table? For example, do you need to compare and current and last week price for all articles?Yes I have to compare all the articles from current to previous year-week.
And what's the biggest concern of your current design?Existing design contains only original value, rest of the columns are calculated in the select query and its taking a long time ( not even minutes) to execute
ART_ID WEEK_AND_YEAR SALES_PRICE_LOCAL 100001 201240 2000
So you need to join 2 very large rowsets (this year-week price and last year-week price), this can be slow anyway. Using a MV just shift the time of the job around.
Note that retrieving a single very large rowset (i.e., the MV you considered) can also be slow.
If you have partitioning option, it seems WEEK_AND_YEAR is a good candidate. Or is it partitioned already?
If you have partitioning option, it seems WEEK_AND_YEAR is a good candidate. Or is it partitioned already?Yes it is already partitioned by this column, single year data is of 14 GB in size, is it required sub partitions as well?(like store, article at each store needs to be compared )
Query Response time should not be in minutes, that what the business is concerned as quicker as possible. Its very challenging but also interesting, worrying and testing :)
Edited by: Hesh on Dec 7, 2012 3:17 AM