SALES_PRICE_LOCAL : Acual data in the source in local currency value
ART_ID WEEK_AND_YEAR SALES_PRICE_LOCAL SALES_PRICE_EURO PREV_WEEK_AND_YEAR PREV_SALES_PRICE PREV_SALES_PRICE_EURO 100001 201240 2000 200 201140 1800 180
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
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 )