RE: Enterprise Replication - Primary Key
Great explanation Andrew! Thank you very much.
I got 157 tables, some of them (10, the most important ones)
do have indexes (not defined as unique),
So I could verify these ones to see if i can
promote the indexes to unique and then I will verify
if there are null values, if I don't find null values i will define the
primary keys .
The rest of the tables do not have indexes, so I will
try to find the candidate-key fields.
I will do this on my development server (a third one) and I will
measure the performance .
About my replication purpose, we make a level 0 backup every night around
01:00 am and we restore it on the other server at 08:00 am, and a level 1
backup at 03:00 pm every day.
I want to replicate to the other server in order to have the
changed data of the day (since the level 0 or 1 backup) protected, and
for availability purposes, I got configured HACMP between both servers,
my informix data (of the master server) is on a RAID 10 hardware-array, so I
have some hardware protection.
I think that HDR would work for me and I would not have to work on primary
keys, but it is a good time for defining them.
So I got plenty of work !!!!
Thanks again
Regards
-----Mensaje original-----
De: Andrew Hamm [mailto:ahamm@mail.com]
Enviado el: Martes, 05 de Agosto de 2003 09:22 p.m.
Para: informix-list@iiug.org
Asunto: Re: Enterprise Replication - Primary Key
Francisco Roldan wrote:
> [SNIP] but i am not in charge of the software application that
> runs on my server, I am just the DBA, so the application
> administrator refuse to define primary keys and the effect of the
> primary keys on the software-application could be very bad ( What
> a great software right? ), at least in a short period of time.
This adminstrator needs to know that IF they have a unique index on a table,
then they already have a primary key. The only difference is that a PRIMARY
KEY definition also implicitly adds (in effect) a NOT NULL clause to all
fields of the primary key. However, "proper design" rarely allows NULL
fields in the key so it's highly likely that the data already satisfies this
rule.
Therefore:
1) If a table contains a unique index AND all the fields of the unique index
are also NOT NULL then it immediately qualifies for a primary key definition
on the same fields, and the performance will not be affected AT ALL. Nor
will a new index be created, since the PK will overlay itself on the unique
index.
2) If a table contains a unique index but there are fields in this index
that are not defined as NOT NULL, you can test these fields by counting how
many NULLS actually exist in the data. Often the lack of a NOT NULL clause
on one of these fields is just an oversight.
If you do find NULLs, beware that if the column definition is not "strong"
enough, you might actually be looking at a garbage row of data that has come
from a program failure; coupled with the database not being strong enough to
reject this rubbish row because of the failure to define NOT NULL on the
proper columns! If so, you will be simultaneously cleaning up your database,
thereby improving the quality of the data, and you will also be making the
database more capable of rejecting bad data in the future.
You will also be able to push the table into class 1 above.
3) If there are no UNIQUE indexes on the table, examine the existing indexes
to see if the designer / implementor has committed another oversight by not
realising that the index should be UNIQUE. You only need to perform
select count(*)
from mytable
group by A, B, C
having count(*) > 1
Nothing comes out, then the index is in fact covering a unique set of
fields. You can and must confirm this using human intelligence to consider
the data and confirm according to the business rules that these columns are
indeed unique. If true, you can promote the index to UNIQUE, then follow 2
and 1 above.
Following steps 1, 2, 3 above will not affect performance, query paths or
locking contention. Therefore steps 1, 2, and 3 are safe reliable actions to
take immediately (really, as your analysis time permits) After this, things
get a little uglier, but not too much.
4) any tables remaining will either have no indexes or no indexes covering
unique data. Perhaps there are some fields out there that could be uniquely
indexed? It's a fact of real-life that virtually all tables in an OLTP
system (note - I'm excluding a warehouse and possibly a crude data logging
system) will have some uniqueness. A table is virtually useless or clumsily
and carelessly designed if it doesn't.
Well, careless design is out there, so you could be unlucky. Assuming
there's no careless design, you can go looking for uniqueness candidates.
First-off, a serial field is a very strong candiate. Otherwise, think about
the table, apply the proper principles and decide which fields are likely to
be the PK. Test the theory with the SQL above.
When you discover the PK for each remaining table, *don't* rush in and add
an index! Any real change to the schema may affect a process!
Usually, adding a unique index will be an improvement for the better, but it
probably will change query plans (once again, usually for the better) but
you need to think about it table by table. Adding an index will nearly
always reduce locking contention too, but once again, that's a 99.9%
likelihood, not a guarantee. Apply intelligent, experienced thought.
If you or the other designer do not know the principles, then read about it
and/or ask.
None of this discussion has anything to do with foreign keys. Sometimes,
when you talk to people about primary keys they can't help assuming you are
also talking about foreign keys and relationships. This is not necessary
either for your schema or for replication. The way Informix FK's are
implemented, there can be a performance cost associated. For some tables and
some activities, this cost can be very high indeed. Therefore reassure your
collegue that no FK's are required and will not happen from this project.
As Madison pointed out, you need to know why you are replicating.
If you are replicating so that the 2nd machine can be an active SQL engine
in it's own right, then HDR will not allow this. The most you can do with
HDR is query-only; you need a non-logged temp space in the engine even to do
anything that requires temp tables to solve the query.
If you want to replicate some tables "to make sure they are safe" then you
really need to look at your backup ritual. That's the #1 choice for data
protection.
If you want to replicate some tables to ensure they are 100% available in
the event of a machine failure, then you probably need HDR and you also need
to make sure you can switch over the users to the secondary quickly enough
to keep the trains running.
If you want to replicate for any other reason (eg logical or business
reasons), then you probably do need ER which means you need PK's.
sending to informix-list