Morning Thomas,

> Partitioning in Oracle is top-notch, definitely. Using that in a project
> as well, but needless to say, it's pricey, as you need Enterprise and
> partitioning is an additional option you have to pay separately.

This much is true, and unfortunate. I think Oracle work on the principle
of getting as much out of you as possible, by up front means, or sneaky
back door ones. :-(

> Performance-wise, it depends. It might get faster, but it can get slower
> as well. It depends on the query patterns. Usually partitioning helps if
> you query (a vast amount of) records, which can be read in parallel.

We don't tend to use parallelism, but if the partitioning (or indeed
sub-partitioning) is on a particular column and that column is included
in the query, then the performance is much better. Assuming that the
partitioning is correct of course.

> Beside performance, we really like the fact, that largish table and
> index data can be administrated more efficiently, when it comes to e.g.
> rebuilding an partitioned index. A smaller index usually rebuilds faster
> than a larger one. Needless to say that purging/removing data from an
> entire partition is not a DELETE on the largish table, but simply an
> operation on the physical partition.

Aye, but beware, DELETE is protected by UNDO as it is DML. Dropping
partitions is DDL and isn't. That's why it's faster.

> But talking to an experienced Oracle DBA, that's nothing new. ;-)
>
> While one could dream having something similar in Firebird, I'm not sure
> if it currently makes sense at all, as long as one can't configure the
> underlaying physical location (aka tablespace in Oracle) of database
> objects on different disks etc. But I'm in favour of Firebird's
> simplicity than adding more and more "Enterprise-level" stuff.

Me too. I love Firebird. But I'm not sure about the merits of spreading
data over partiotions that are thmeselves spread over different
"spindles" in todays environment. All the databases I work with are on
EVAs or NAS storage and those have arrays of spindles. All our databases
appear to have their files in one directory, but that's actually spread
over hundreds of spindles in the array.

> Managing largish tables in Firebird can be tricky/annoying though. E.g.
> the need for an exclusiv lock on the table when managing indexes etc. ;-)

True, but Oracle had the same problem until recently when the ONLINE
option for index rebuilds came about.

Now it takes a lock at the start and at the end of the process, and
releases it in between. Any index updates are applied from REDO after
the initial build has finished.

Anyway, I think we are staying too far from the topic now, best we
quieten down (or go private) before Helen sees what we are up to! ;-)


Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767