Recently I worked on an interesting use case from auditing which requires to roll up status of lower level tables into higher level. We already have a tree hierarchical design in our data model.
There are two options: one is to handle the roll-up pair by pair; another is to design a context table which presents the tree structure and use Oracle hierarchical query to go through tree traversal based on depth first search. The second option is obviously an optimized approach to go. Since it may easily handle larger size model – as long as we have the context table ready. Let’s dive in and see how we handled this.
First, I created a context table which presents the context id and parent context id relationship. It’s a self-reference table.
create table ls_context_info ( context_id number not null, parent_context_id number, table_name varchar2(30 byte), primary_key varchar2(200 byte), foreign_key varchar2(200 byte), constraint ls_context_info_pk primary key (context_id) )
Insert some data:
Second, I constructed a hierarchical view based on this table:
create or replace view v_ls_context_info
as
select context_id,
rpad ('+', level - 1, '-') || context_id as tree,
level node_level,
connect_by_root context_id as root_id,
sys_connect_by_path (context_id, '/') as context_id_path,
reverse (sys_connect_by_path (context_id, '/')) as reversed_path,
sys_connect_by_path (table_name, '/') as table_name_path,
connect_by_isleaf as leaf
from ls_context_info
start with parent_context_id = 0
connect by parent_context_id = prior context_id
order siblings by context_id;
- LEVEL : The position in the hierarchy of the current row in relation to the root node.
- CONNECT_BY_ROOT : Returns the root node(s) associated with the current row.
- SYS_CONNECT_BY_PATH : Returns a delimited breadcrumb from root to the current row.
- CONNECT_BY_ISLEAF : Indicates if the current row is a leaf node.
- ORDER SIBLINGS BY : Applies an order to siblings, without altering the basic hierarchical structure of the data returned by the query.
The result set is:
According to this view, it’s easy to construct a code snippet to complete the roll-up function based on depth first tree traverse outlined in above reversed_path or table_name_path. The hierarchical query resolves recursive issue for us. The rest job is straightforward.
Let’s review what we have done here. Many data model consist of hierarchical relationship based on tree structure. Thinking about we have a table as the root node, plus a few other tables as its children and grandchildren. This solution is to resolve the tree traverse issue using Oracle hierarchical query instead of developing recursive function. If we want to pass the status from leaf to upper level, eventually to root level, this is one of the best solution.
Lionsgatesoft.com consultants have rich experience in enterprise application, database design and development. Should you have any questions, please feel free to contact us.
