Re: scalibility of Hierarchical Qeries

From: Oxnard Montalvo <>
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.


Received on Tue Feb 12 2008 - 06:01:31 CST

Original text of this message