>> We have vldb, which has 10 tables that need to be purged on daily
bases (1 million records [sic] per table). This process HAS TO BE partly
overlapped with regular working hours and it should be implemented as a
daily job... How can we do this and at the same time avoid locking as
much as possible? <<
I hate to tell you this, but you are not a VLDB yet; when you are doing
several million rows per hour or have to worry about petabytes of
storage on more than one physical unit, then you are getting to be an
VLDB. Look up the term in the literature. Also, rows are not records.
Your best bet is a database with optimistic concurrency control, that
could mean switching to InterBase or Red Brick in particular. The
generational concurrency control in InterBase lets you see a snapshot of
the data at several points in time, so while changing data, you can be
querying the immediately prior version simultaneously. The Red Brick
model is a star schema with the fact table organized by time; new stuff
is attached to one end of the physical file as old stuff is archived off
the other end simultaneously.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!