Home » SQL & PL/SQL » SQL & PL/SQL » hierarchical data association with high volume table (oracle 9i)
hierarchical data association with high volume table [message #399362] Tue, 21 April 2009 16:19 Go to next message
techluver
Messages: 16
Registered: May 2006
Junior Member
Hello,
forgive me for lengthy post.
I have following table with ~30 million data each day(partitioned by date)
TABLE ORDERS
order_id integer
order_type varchar2(10)
replaced_order varchar2(25)
parent_order varchar2(25)
top_parent_order varchar2(25)
qty integer
link_id integer
date_id integer

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

Conditions:
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 o.*,
(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
Re: hierarchical data association with high volume table [message #399364 is a reply to message #399362] Tue, 21 April 2009 17:15 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Previous Topic: Addition date and varchar2
Next Topic: backing up data (merged 3)
Goto Forum:
  


Current Time: Sat Dec 10 10:52:16 CST 2016

Total time taken to generate the page: 0.05618 seconds