Re: Best way to partition this data?

Jusung,
Thanks for your help.

Generally, the database will be for 'reports' after a fashion ie data analysis.
I will need to interrogate the database to produce numerous aggregate tables.
The use of each query will be dependent on the value of the aggregate data - which will
not be known until the data has been produced - so its a bit of a chicken and egg situation.

However the query most popular entry points are likely to be Order date and Item Id/Type.

I did mention that an obvious candidate for denormalisation in the Order date down to the Item table. Otherwise
it would only make sense to denormalise everything and produce an Order-Item table which I think is overkill as they both have many attributes, most of which would be redundant for any given report.

I was just looking for some general instinctive pointers/observations for a 'good' starting point for partitioning.

My instinct would be to denormalise the date, and partition the Items by date range - the only other contenders are order number or item number. The Items contain ids to other tables for which I can produce partioned indexes.

I can try trial and error - but how long with it take to repartition this data on a medium range PC?

I haven't tried it yet and I am loath to in case it takes a couple of days! - Maybe it just takes a couple of hours or less?

Cheers, Al

"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0303061851.69658022_at_posting.google.com...
> 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 Fri Mar 07 2003 - 08:02:49 CST