Re: conceptual question regarding partitioning

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/06/14
Message-ID: <961020544.9690.0.pluto.d4ee154e@news.demon.nl>#1/1

The RDBMS model falls nicely into what I learned to describe as three schema architecture

- conceptual schema
- logical schema
- physical schema

Your approach tends to blur the distinction between the two layers, making the logical design dependent of physical considerations. IMHO, this should be avoided at *ALL* cost.

Also, I'm not too sure whether the account_id is too good a partition column. You could potentially have 1000s account ids, resulting in 1000s partitions. This will become unmanageable. When partitions were originally introduced, an Oracle instructor advised to use them for date ranges *only* IMHO this is a sensible approach, and I'm not looking forward to use partitions on floating data like ids. I'm also sure from the performance perspective you don't gain much.

Regards,

Sybrand Bakker, Oracle DBA

"gdas" <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote in message news:02128984.3ebb7478_at_usw-ex0108-063.remarq.com...
> Does it make sense to logically design the database schema to
> accomodate partitioning better?
>
> For example:
>
> Table: Account
>
> Table: Run (Has run_id and account_id)
>
> Table: Results (Has run_id and some other columns)
>
>
> Logically the tables would join: Account > Run > Results with
> each step of the way down being a one-to-many relationship.
>
> Each account will have a handful of runs, but each run will
> have hundreds or even thousands of result entries. The Results
> table is quite huge and growing quite rapidly (50-80 megs per
> day).
>
> The queries that come in are for a single account. (Show data
> for "my account" only). I don't want to create duplicate
> schemas/tables for each account and then use dynamic sql because
> that would just cause the maintenance to explode.
>
> I want to partition the results table to improve performance.
>
> I've zero experience with table partitioning, but I've been
> reading up on it. I think HASH partitioning fits our model best.
>
> However, it appears that you need to partition on a column and
> right now the best "column" to partition on is the account_id
> (I'm assuming that if can encapsulate all the data for a single
> account into one partition that queries that come in will be
> faster since all queries will be constrained to a single
> account) However account_id doesn't exist in the results table.
>
> I can denormalize my schema and put the account_id into the
> results table for the sole purpose of table partitioning...
>
> but I'm hesitant since it sort of goes against everything I've
> been taught over the years...
>
> Just thought I'd put this out there if anyone has any opinions
> or advice to share...
>
> Thanks,
>
> G
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network
 *
> The fastest and easiest way to search and participate in Usenet - Free!
>
Received on Wed Jun 14 2000 - 00:00:00 CDT