Hi Norman,

>> yes i understand, but now with my 50 millions rows table i start to meet the limit of firebird where a simple prepare can take around 1 s to 1 min dependantly the charge of the server (see my previous post). next year it's will be around 100 millions rows and i will have no solutions ... this why i start to thing about sharding in an easy way, in a way out in fact
> Hmm. I've not really heard of "sharding" as such, but what you propose
> as a solution to your huge table problem, may not be the best one.
>
> As an Oracle DBA, I work with tables holding hundreds of millions of
> rows. Now I'm not 100% sure what preparing a statement on Firebird
> should take so long on bigger tables and I can see how, with the present
> state of things, that that will be a problem for you.
>
> However, where I have these huge tables I can use Oracle Partitioning to
> split them up into logical units based on the value in (a) specific
> column(s) of the table. As long as this partitioning column is included
> in a query, then a full table scan turns into a scan of one or two
> partitions.
>
> Instead of searching hundreds of millions of rows, I search a few
> thousand instead.
>
> Obviously, that assumes that an index cannot be used for that particular
> query. However, the indexes can be partitioned to match the partitioning
> of the table, so an index scan is then reduced to a few partitions
> rather than a complete index lookup.
>
> Performance is far better when partitioned, and there's no need for
> cross database communications and synchronisation.

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.

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.

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.

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.

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. ;-)

Just my €0.02.


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/