Re: Performance of multiple separate tables better than that of multiple partitions?

[Mikael Ronström]

Hi,
Splitting very large tables has very positive impact on the
insert speed but this obviously benefits both solutions.
Partitioning has some special partition pruning support
which is probably possible for the SQL optimiser to also
discover using multiple tables in some cases, but I'm sure
there are lots of cases where it is better to use partitioning.

In the SELECT queries you provide I'm not sure how a non-partitioned
table is handled, will it be a join or will the application handle
the partitioning. So from what I understand you can probably achieve
all the benefits of partitioning at user-level but obviously it requires
less work on application level if you use the partitioning solution.

Rgrds Mikael


Thorsten Mueller Wrote:
-------------------------------------------------------
> Hi,
>
> I have a general question regarding performance:
> Are partitioned Tables equal to the same number of
> independent tables regarding performance?
>
> Maybe I can explain my problem by an example:
>
> I have a created a table "Hits" with more than
> 50.000.000 lines refering to 5 different
> "categories". (Referencing uses a secondary
> key=int in both cases).
>
> There are two possibilities:
>
> Partitioning the large "Hits" table in say 5
> partitions by using KEY partitioning on a
> secondary key in the Hits table.
>
> The other possibility would be to create 5
> different "Hits" tables for each kategory
> ("HitsA", "HitsB", ...).
>
> The usecases I am thinking of are thousands of
> INSERTS into the "Hits" tables or a multitute of
> Select queries like "SELECT h.* from Hits h,
> Kategory k where k.id = xy AND h.kategory_id =
> k.id "
>
>
> It would be great if anybody could help me with
> this problem.
>
> Thanks,
>
> Thorsten