Re: Best way to partition this data?

I suspect that you are building a DSS and the modeling part of the job is not quite done yet - otherwise you would not have mentioned denormalization. I would firm up on the logical design first before tackling the physical design. If this is a data warehouse application and you have followed the dimensional modeling approach, you might end up with something called "degenerate dimensions" - which usually occurr in line-item oriented fact table design (refer to p.188 "The Data Warehouse Lifecycle Toolkit" by Ralph Kimball). If this is your case, then the order ID should go to the fact table.

If this is not the case, and you are trying to generate reports from the production system, then that's another story... I hate generate reports from the production system. It is difficult and usually slow.

In any case, I would write down top 10 queries that are likely to run most frequently against the tables and then take a look at the where clause and go from there.

• Jusung Yang

"Albert" <nothere_at_bigfoot.com> wrote in message news:<b48adv$l7a$1_at_newsg2.svr.pol.co.uk>...
> Hi,
> I'm using 8.1.7 on a standalone database.
> I have around 15,000 'header' records per year indexed on date and a unique
> id (not sequential with the date) - lets say they are 'Orders'
> 10 'detail' records per header indexed on the header id and its own 'item'
> id - lets say they are 'Items'.
> Together they form a unique index for the item row.
>
> I have around 15 years worth of data.
>
> Now the natural way to partion the Order data is on date, say 1 per year.
> However, I am not sure of the best way to partition the Items table.
> It will be SQL accessed via the Order table or by its own id in equal
> measure.
> Also a query on range of transactions for the Items across dates will be
> required.
>
> Generally the methods of access with be as you would expect for a sort of
> 'Orders' database.
>
> Should I partition the Items table on the Order reference number, or perhaps
> on a range of its own Item id?
> Or should I denormalise the Order date down to the Item table and partition
> on this so the partion 'matches' the Order partion?
>
> I appreciate you cannot give hard and fast opinions without knowing the
> application specifically. But being a form of the ubiquitous 'Order
> database' any insights would be helpfull.
>
> Thanks
Received on Thu Mar 06 2003 - 20:51:54 CST