0

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
  • Will you ever search to more than 3 levels up? – ypercubeᵀᴹ Dec 14 '15 at 20:05
  • Your query references equip_no, but that is not a column in either table. Is this an alias for equipment.id? – Leigh Riffel Dec 14 '15 at 22:23
1

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.

  • Thanks for the response. In answer to your questions. 1) It's for a report and runs nightly so there's no impact on user experience. It's working now in the verbose format, I'm just looking to improve. 2) Result does have to be returned as a single row. 3) The live environment has more types/levels but only the top 3 are being reported on. They exist for all items regardless of absolute depth. The data is sourced from a materialized view. The reporting requirements have changed and necessitated the new fields. I'll be adding it to this view but hoped for more efficiency. – Darren Nov 4 '14 at 3:29
  • Thank you for the response to my questions. Based on what you have stated, to my knowledge, I am not sure what you could do to improve what you have. My only other suggestion would be to look into 3rd party apps or BI tools that may assit with report generation. – theSqlGuy Nov 4 '14 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.