Connect by prior – multiple fields and query efficiency

oracle

I have 2 tables one of which defines the hierarchy for the other

equipment

id    desc  
1     item1  
2     item2  
3     item3  
4     item4  
5     item5  
6     item6  
7     item7  
8     item8  
9     item9  

hierarchy
equipId    parentEquipId    type    attrib_1    attrib_2    attrib3  
1          3                D       blue        red         green
2          4                B       green       blue        red
3          2                C       orange      green       purple
4          null             A       pink        yellow      orange
5          3                D       yellow      red         purple
6          3                D       purple      green       red
7          2                C       grey        brown       tan
8          7                D       red         grey        yellow
9          7                D       blue        green       orange

My query is against the items of type D. These can be easily determined through a join on the second table to obtain the type.

select * 
from equipment 
left join on hierarchy.equipId = equipment.id
where hierarchy.type = 'D'

The tricky part is I also need to obtain the 3 attributes for the various parent types.
Each type D item has an A,B,C parent hierarchy based on the types of the parents. So for the piece of equipment with the id of 1 the ids of the parents are 3(C),2(B),4(A).

I've been using multiple CONNECT BY PRIOR statements to get the attributes of the parent hierarchy. I have only figured out how to get each attribute individually though and this adds up to a lot of connect by prior statements. Even for each of the parent types I still need to do 3 connect by priors, one for each attribute. It would be nice to be able to do a connect by prior once to each of the parent types and pull all 3 attributes at the same time.

Does anyone know if/how it can be done?

Here's my current SQL. It works but I'd like to make it more efficient and less verbose.

Any help is appreciated.

with
e as  (select equipment.*
       from equipment 
       left join on hierarchy.equipId = equipment.id
       where hierarchy.type = 'D')  

select e.*
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_3
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_3
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_3
from e

Best Answer

Here are some additional questions that would help me answer this question better: (1) how often is this query executed; (2) does the result set have to be returned as a single row per ID; (3) in the actual environment can one item have more than 3 levels of depth?

I am going to assume that data sample is not representative of your actual environment and that the depth of this hierarchy may go deeper than 3 levels. In order for a single-row query to be successful in each case there must be a lot of potentially redundant SQL to account for all possible levels of depth. If your query accounts for an item having up to 5 levels of depth, but only one of your items has that many levels then you are putting unnecessary load on the database and potentially face performance delays with long running queries. A multi-row query could get much better performance, but that probably isn’t how you need the data returned (like below).

SELECT H.* FROM HIERARCHY_TBL H START WITH H.EQUIP_ID = '1'
CONNECT BY PRIOR H.PARENT_EQUIP_ID = H.EQUIP_ID

If this is an ad hoc query occurring infrequently then dealing with performance delay may be an acceptable option so you could stick with what you have. If this query is executed very frequently then performance delay is going to be unacceptable by the users.

As far as suggestions, if this SQL is being executed from within an application then you could look into using dynamic SQL. You could also check into creating a SQL profile to see if that would improve performance. You could also create a view that does most of this leg work for you and then hit that view with your query. The view option won’t reduce execution time, but it may help with clarity. If the data in these tables doesn’t change very often you could also look into using a materialized view that is refreshed from time to time – this will give you a lot of performance gain, but materialized views have their own challenges and depending on the environment some DBAs may discourage their usage.

I hope this helps.

Related Question