Board index Oracle

Basic question on partitioning

Basic question on partitioning

Post by Rob Hal » Sun, 31 Dec 1899 09:00:00



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.

 
 
 
Top

1. Partition basics

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.

2. Reading a 7.11 shmem.xxxxx file

3. Urgent! Process partition fails when partition is cloned

4. Problem with connection pooling under IIS

5. CommanDLine -creating partitions - processing partitions

6. DTS Scheduling

7. Partitioned views - how to size partitions?

8. CommanDLine -creating partitions - processing partitions

9. Convert a non-partitioned table to partitioned table

10. partition views vs partition tables

11. O7 to O8 Partitioned Views to Partitioned Tables

12. Partitioned views vs. partitioned tables


All times are UTC
Board index