Den 2012-07-22 21:59 skrev Kjell Rilbe s�h�r:

>
> Trying to figure out a way to do a search/query with decent performance.
>
> Table "Master" contains roughly 100 million records and table "Detail"
> contains roughly 200 million records.
>
> Both have a bigint "Id" primary key and "Detail"."Master" links to
> "Master"."Id" in a regular master-detail relationship. "Detail"."Master"
> is indexed, but there's no FK constraint (never mind why).
>
> "Detail"."Removed" TIMESTAMP is nullable and NOT indexed.
> "Detail"."RemovedBy" bigint is nullable (FK to a third table) and IS
> indexed.
>
> If a detail has been removed, "Removed" is set to the time of removal
> and "RemovedBy" is set to the id of a record containing data about the
> removal, e.g. who did it and so forth.
>
> Business logic says that if a master has any details at all, at least
> one of them should be current, i.e. NOT be removed. So, at least one
> detail should have "Removed" and "RemovedBy" set to null.
>
> I've found a breech of this rule in my data and I want to find if there
> are more.
>

Having solved that (after 9 hours query execution), I'm thinking "How
can I prevent this from happening again?"

So, could this have been prevented using some kind of DB constraint?
I.e. Make it impossible to remove a details without also adding a
current one...?

The O/R mapper probably can't be guaranteed to issue the addition of a
current detail (insert) before issuing the removal of the old one
(update...set "Removed" and "RemovedBy" to non-null).

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



[Non-text portions of this message have been removed]