You've left out one key advantage - availability i.e. if you lose a disk
with some partitions on it you may still be able to get to the other
partitions (depending on the query of course). And then there's the simpler
(and faster) backup for tables that have lots of read-only partitions (which
can be the case fairly often).
--
HTH. Additions and corrections welcome.
Pete
Author of "Oracle8i: Architecture and Administration Exam Cram"
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not. It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
"Keith Boulton" <kboulton_at_ntlworld.com> wrote in message
news:No2d8.1990$hM6.129212_at_news6-win.server.ntlworld.com...
>
> Bricklen <bricklenREMOVETHIS_at_shaw.ca> wrote in message
> news:3C7471A6.3B720E42_at_shaw.ca...
> > about this? I have several ideas in mind, but I'd like to hear from
> > someone more knowledgeable about possible ways it could be done, and if
>
> I wouldn't claim to be especially knowledgable (and I'm not sure about my
> spelling either!).
>
> But, it seems to me to depend on what you're doing.
>
> The advantages of a partitioned table are
>
> performance, especially on table scans and especially if parallel
query
> is used
>
> administration, you can create and drop partitions easily.
>
> In my case, I've developed an end-user query program which builds the
query
> at run time. One of the parameters is present in >90% of queries and was
the
> partition column. It's a trivial matter for me to create N tables (just
like
> I had N partitions) and modify the code to point at a given table based on
> this key. I can easily build a union all view for queries against all
> partitions.
>
> I also need to modify the data load code to load data into the N tables.
>
> This will all take me less than 1 day.
>
> Compared with using Oracle partitioning, I lose the ability to do some
admin
> activities without interruption of service which is not a big issue
because
> the database is small enough so that I actually have two copies of the
> schema for the current run of the data update job and the last run. This
> means I can run the update jobs without worrying about e.g. consistency
> between different tables.
>
> I also don't get a performance advantage where I could have used more than
> one partition of the partitioned table.
>
> I'm not saying that there would not be times when partitioning would be
> useful or even essential, I'm merely questioning the costs and benefits.
>
>
>
>
>
>
Received on Thu Feb 21 2002 - 10:49:33 CST
