Table partitioning

David Johnson  writes:
> 
> I have a problem in which I need to partition a large table
> (100 million rows) over a number of disks so that it can
> be stored and queried efficiently in an OpenIngres 1.2 environment.
> 
> I would like to partition the table either in a "round-robin" fashion
> or more likely using a "partition id" column which would be derived from
> the primary key of the table.
> 
> I know Informix  and other vendors provide this kind of functionality,
> but I'm a newbie to Ingres and couldn't find any mention of this in the
> DBA guide. All I found was the concept of multiple "locations" for a
> table but I don't think this is what I need (there doesn't seem to be
> any control of how the locations are populated.
> 
> All help appreciated.
> Dave

Unfortunately, there is no way, other than round-robin, to 
populate a multi-location table.  At least not in 6.4 or 1.2.

The tables are extended round robin, so a number of rows (depending
upon their width) is written to location A, the next X are written to
location B ...

Cheers,

Michael Leo            mleo@cariboulake.com        mal@visi.com
Caribou Lake Software  http://www.cariboulake.com  Java/RDBMS Solutions
 



Ingres/OpenIngres will use a round-robin method to populate the locations,
but it is based on a number of pages (16) and not a user-specified value.

About the closet thing that you could do is to cluster the data on
partition_id using a BTREE structure (you may need to use an index
(possibly unique) as an efficient means of accessing the table) .  On
large tables (4-6 GB) I have had a lot of luck doing this.  Data is
grouped by how it is used (e.g., Branch, division, state, etc.) and I/O is
minimized.  The biggest concern here is not having too many secondary
indexes, which will have a negative impact on performance (increased time
in the optimizer).

>
>I know Informix  and other vendors provide this kind of functionality,
>but I'm a newbie to Ingres and couldn't find any mention of this in the
>DBA guide. All I found was the concept of multiple "locations" for a
>table but I don't think this is what I need (there doesn't seem to be
>any control of how the locations are populated.
>
>All help appreciated.
>Dave

Chip Nickolett           ChipN@Comp-Soln.com
Comprehensive Consulting Solutions, Inc.   (www.Comp-Soln.com)
Phone:  262-544-9954     Fax:  262-544-1236
Ingres Q & A
To William's Home Page
Email William