Re: scalibility of Hierarchical Qeries

  • From: Oxnard Montalvo <oxnard@xxxxxxxxxxxxxxx>
  • To: nigel_cl_thomas@xxxxxxxxx
  • Date: Tue, 12 Feb 2008 7:01:31 -0500

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.



Other related posts: