Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Best way to partition this data?

Re: Best way to partition this data?

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 7 Mar 2003 11:48:35 -0800
Message-ID: <130ba93a.0303071148.63ac03f8@posting.google.com>


Hi Al,

Remember that one implements partitioning for 3 main reasons: 1. Improved performance 2. High availability 3. Eaze of management

There are (in 9i) 3 types of partitioing: 1. Range 2. Hash 3. List

It seems that you are most interested in the performance benefits of partitioing. Performance benefits mainly come from the possibility of partition prunning, distributing IO and parallel processing. Mostly people talk about partition prunning. If partition prunning is what you are shooting for, you have to understand how and when partition prunning occur.

<partition prunning>
For single table query, it is easy. Look at your where clause. The predicates are the ones you want to use for your partition key. From your description, date would seem to be the best candidate. Range partition would be the preference.

For multi-table queries, partition prunning may or may not happen. It all depends on what's in the where clause. You can look up ORACLE DOC of go to Metalink for some examples.

If you don't have to worry about keeping dates in both tables in sync, and mvoing dates into the item table make the queries run faster, I don't see why not.

As to the speed of repartitioing you table, it has more to do with the data loading speed of your machine and less to with the partitioning itself. Although if you set it up right, you might be able to exchange a partition for a table and thereby eliminate the need to reload data.

Why not experiment a bit on some smaller tables first?

"Albert" <nothere_at_bigfoot.com> wrote in message news:<b4a8mn$ltc$1_at_newsg4.svr.pol.co.uk>...
> 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 - 13:48:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US