Re: Finding busy tables
Actually, I should have used "spread" instead of fragment, but now
that you ask, I think there can be a solution.
1) Let's say for example, we have the customer table, and would
like to fragment it by expression , say the West customers in
filesystem1 and the rest in filesystem2.
You can create table (customer1) and table (customer2) with
the same structure but specifying different locations, such as
create table customer1 IN "/filesystem1".
.;
create table customer2 IN "/filesystem2"
2) Once we have created the two tables in their correspoding filesystems,
we create a VIEW based on a join of both tables.
create view customer(field1,field2...) asselect a.*,b.*
from customer1 a , customer2 b
(Please read the sql-manual for the correct syntax, now, I am just taking
my breakfast, so I do not have any manual handy :-)
3) Keep in mind that SE doesn't have this feature (fragmentation) in the
core.
4) Keep in mind that views based on a join cannot be used by insert,delete
or
update operations.
5) When our application SELECTs information from table we can use the view
customer, since it will contain all the rows, whenever we instruct the
select
to return some rows, the corresponding filesystem will be read, thus, we
have achieved fragmentation , at least for the select statement now.
6) For inserts, deletes and updates, you will have to hard-code it in your
application,
because you will have to specify the real table , whenever one of this
operations
occur.
say:
switch (location)
{
case "WEST":
$insert into customer1 ...
break;
default:
$insert into customer2 ...
break;
}
7) Instead of step 6 you can create a dummy table , and associate a
store-procedure,
with trigger events (for updates, deletes, inserts) from that table. So,
your store procedure
will have to check every row, and will direct that row to its
corresponding real table.
Performance will decrease of course.
So, it is possible to have fragmentation in INFORMIX-SE, but you will have
to manage it
by your own, since the database does not have this functionality internally.
Regards,
Mario Estrada
---------------------------------------Reply
Separator--------------------------
-----Original Message-----
From: Jonathan Leffler <jleffler@earthlink.net>
To: informix-list@iiug.org <informix-list@iiug.org>
Date: Lunes 21 de Septiembre de 1998 12:36 AM
Subject: Re: Finding busy tables
>Mario Estrada wrote:
>> You can fragment your tables in Informix-SE and then watch
>> their activity using the sar command in unix.
>
>You can fragment tables in SE?
>
>How?
>
>--
>Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
>Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
>#include <disclaimer.h>
>