Re: scalibility of Hierarchical Qeries

Ox

I didn't see any answers to this posted over the weekend (sorry if I missed them).

Why a hierarchy? I can see you will have version N followed by version N+1 etc (or preceded by version N-1) - but are you expecting one row to have more than one direct child?

It seems to me that the probable issues for you will be

1) efficiently identifying the "current" row out of all your history - for any possible query on the table
2) Identifying predecessor / successor rows and so being able to reconstruct the changes
3) if this is a "master" table, deciding on the semantics for foreign keys from any detail

(3) is perhaps the nastiest. Let's suppose the master is Emp, and a detail is EmpDept.

Nigel joins the company on 1/1/2004

Nigel is a member of Sales from 1/1/2005 to 1/1/2007

Nigel gets married on 1/1/2006

Now, I assume there are two Emp rows for Nigel - the original, and the one with the updated "married" status. The primary key for Emp is probably (EmpId, VersionNr)

So does EmpDept "point to" (have a foreign key reference to) the first or second Nigel?

Answer: either you have a foreign key on (EmpId, VersionNr) and so you would have to version rows in EmpDept (and all other detail tables) whenever you version rows in Emp - or you have to use a weak key (EmpId) which can't be enforced in the database.

Or you go back to a more traditional scheme

Emp is identified by EmpId
EmpDept references Emp on EmpId
EmpHist contains the history (with or without the current row)

Then the reference to Emp from EmpDept can be enforced properly (as can the reference from EmpHist to Emp). "Current" queries are straightforward; historical queries are still possible. You trade simpler queries for an update (and storage) tax.

Hope that helps

Regards Nigel

• Original Message ---- From: Oxnard Montalvo <oxnard_at_carolina.rr.com> To: Post to FreeList Oracle-L <Oracle-L_at_FreeLists.org> Sent: Thursday, February 7, 2008 2:01:39 PM Subject: scalibility of Hierarchical Qeries

ver. 10.2.0.3

We are considering the use of a hierarchy to maintain a history of changes to rows in a table.

If the table gets large (1x10^7) and has (1x10^6) different hierachies (10 changes to the same table) I am wondering how it will scale, and any tips to help it scale.

Thanks
Ox

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 12 2008 - 03:10:13 CST