Partition basics

Partition basics

Post by KB » Wed, 12 Mar 2003 09:43:28



Hello all -
I am working with a database with a fairly large number of tables. A master
table ("ClientDemog") tracks clients. A series of possible services is
tracked in tables (1:M with ClientDemog). The services are broken into
logical categories, and each category constitutes its own little universe of
data. As an example - a client may come in for service and an entry is made
for him in the "Portfolio" table (and all 14 of its related 1:M subtables).
The same client comes in at a later time regarding asset management, and
entries are generated in "Assets"  (and its 8 related 1:M subtables). Later
some entries are generated in "Capital" (and its 16 related 1:M subtables).
Still later the client comes is for more portfolio advice and an entry is
again made for him in the "Portfolio" table (and all 14 of its related 1:M
subtables). The database rotates around ClientDemog, and 'silos' of data get
stored in groupings held together by the services tables (Portfolio, Assets,
Capital, etc.)

We use MS Access as a front end for report writing, with an ODBC link to the
database.

I want to divide up the data for the users but still keep everything in one
database on one server (no $ for federating). I can use an integer field in
ClientDemog ("Location") as an easy way to divide things up at the
ClientDemog level. When a user logs in who should be seeing only clients in
location "101", I want him to be able to access any of the services tables
and subtables, but for the partition to be limited to ClientDemog records
with a location of "101".

A view on ClientDemog allows me to specify WHERE (Location = 101). But how
do I get that limitation to apply to the subtables? In other words, how can
I structure the view so that the condition that is checked is
ClientDemog.Location = 101, giving me selection of clients and any services
rendered to that pool of clients? How, in other words, do I partition my
data, using a value at the master table level, and have that partition work
its way down into every last related subtble?

Thanks.

 
 
 

Partition basics

Post by <n.. » Thu, 13 Mar 2003 00:17:17


You can create views on the sub tables with an appropriate filter - where
exists(select * from maintable where maintable.fk = subtable.pk)  and then
have the front end point to only views, no tables.

I've done this for horizontal partitioning for security purposes, and it
works fantastic. (make sure you have appropriate indexes to support the
where clauses!)


Quote:> Hello all -
> I am working with a database with a fairly large number of tables. A
master
> table ("ClientDemog") tracks clients. A series of possible services is
> tracked in tables (1:M with ClientDemog). The services are broken into
> logical categories, and each category constitutes its own little universe
of
> data. As an example - a client may come in for service and an entry is
made
> for him in the "Portfolio" table (and all 14 of its related 1:M
subtables).
> The same client comes in at a later time regarding asset management, and
> entries are generated in "Assets"  (and its 8 related 1:M subtables).
Later
> some entries are generated in "Capital" (and its 16 related 1:M
subtables).
> Still later the client comes is for more portfolio advice and an entry is
> again made for him in the "Portfolio" table (and all 14 of its related 1:M
> subtables). The database rotates around ClientDemog, and 'silos' of data
get
> stored in groupings held together by the services tables (Portfolio,
Assets,
> Capital, etc.)

> We use MS Access as a front end for report writing, with an ODBC link to
the
> database.

> I want to divide up the data for the users but still keep everything in
one
> database on one server (no $ for federating). I can use an integer field
in
> ClientDemog ("Location") as an easy way to divide things up at the
> ClientDemog level. When a user logs in who should be seeing only clients
in
> location "101", I want him to be able to access any of the services tables
> and subtables, but for the partition to be limited to ClientDemog records
> with a location of "101".

> A view on ClientDemog allows me to specify WHERE (Location = 101). But how
> do I get that limitation to apply to the subtables? In other words, how
can
> I structure the view so that the condition that is checked is
> ClientDemog.Location = 101, giving me selection of clients and any
services
> rendered to that pool of clients? How, in other words, do I partition my
> data, using a value at the master table level, and have that partition
work
> its way down into every last related subtble?

> Thanks.


 
 
 

1. Basic question on partitioning

Hi all,
  I've just taken over the architecture of an existing database that's
designed to work over six disks on Solaris systems.  To improve
efficiency, the original group striped the six disks into three and
placed all tablespaces on different disks, leaving the OS to place data
on different spindles. Partitions are used, but ony to keep track of
data by a particular value so that it can be dropped easily when it is
outdated. All partitions for a database table are currently stored in
the same tablespace and on the same spindle pairs.

I don't believe that this is the optimal configuration for performance.
I would like to partition each table across multiple spindles to
improve performance and ensure that the loss of one disk does not cause
the loss of all data in a table. Can anyone tell me if partitioning
tables across mulstiple spindles will improve performance?  If so, are
there available case studies that can provide me with benchmarks ofr the
percentage gain in performance?

Thanks in Advance,
Rob Hale
Nortel Networks.

2. Storing bitmap images

3. Urgent! Process partition fails when partition is cloned

4. database link problem in oracle 8

5. CommanDLine -creating partitions - processing partitions

6. Oracle 7.3.4 JDBC

7. Partitioned views - how to size partitions?

8. Here's a rose for Borland

9. CommanDLine -creating partitions - processing partitions

10. Convert a non-partitioned table to partitioned table

11. partition views vs partition tables

12. O7 to O8 Partitioned Views to Partitioned Tables

13. Partitioned views vs. partitioned tables