Re: scalibility of Hierarchical Qeries
Date: Tue, 12 Feb 2008 7:01:31 -0500
Message-ID: <30384566.1105521202817691317.JavaMail.root@cdptpa-web22-z02>
Running on 10gR2
No I am not expecting one row to have more than one direct child.
1 ... I fI am following you correctly the current row is identified by the build in Oracle's function in connect_by_isleaf
2... I am considering using Oracle's CDC package so I have access to the rowid. So far seems to work very well. Of course that implies I must make a procedure to deal with table loads and unloads. Unfortunately, the PK of a table can change. (It was there before I was).
3.. There are three tables one parent and two direct child tables connected by a fk. My 'snapshot' is and end of bussiness day outer join from the parent to the children. My history table is a denormalized view of the three tables + the rowid column and the last dml operation.
So far the trickest issue to deal with has been Oracle will reuse a rowid. Even that is not that bad since in my requirement is to use an end of day snapshot.
I had considered using a fast freshable MV to obtain the denormed view, but Oracle does some fancy footwork under the covers thus makes it very difficult to track changes.
Thanks for your reply.
Ox
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 12 2008 - 06:01:31 CST