|hierarchical data association with high volume table [message #399362]
||Tue, 21 April 2009 16:19
Registered: May 2006
forgive me for lengthy post.
I have following table with ~30 million data each day(partitioned by date)
1. Order type can be NW(new order) or CR(CANCEL-REPLACE)
2. For top most parent order, parent_order=top_parent_order.
3. Child orders for the same parent will have its parent's link_id and top_parent_order
4. CR order which replaced NW order, will have replaced_order = parent_order
5. parent_order and order_id is unique for each order
1.Here I would get to fetch child orders with it parent order_id and qty.
2.If parent NW order is replaced by CR order, then associte CR parent order's order id and qty with the child.
If I don't have 2 condition, then I can fetch child orders following way:
(select order_id from ORDERS ido where ido.link_id = o.link and o.top_parent_order = o.parent_order )as parent_order_id,
(select qty from ORDERS ido where ido.link_id = o.link and o.top_parent_order = o.parent_order )as parent_qty
from ORDERS o
WHERE o.top_parent_order != o.parent_order -- to get only child orders and ignore parent order
A. Is above efficient way for table with 30M data each day?. I'm also thinking to store all child orders temp table and join again with ORDERS to find parent.
B. I'm sure how to fetch parent order,qty if 2 condition is also involved. Is CONNECT BY useful here?
Please help. thanks