More efficient way to write multi-tier sql statement

Question

I am looking to write an 8 tier query in a more efficient way. Currently I am using a with statement as such:

    with ffdepend as  (
    SELECT DISTINCT ATR1.PARENT_N_VALUE col1, ATR1.CHILD_N_VALUE col2, ATR2.CHILD_N_VALUE col3, 
    ATR3.CHILD_N_VALUE col4, ATR4.CHILD_N_VALUE col5, ATR5.CHILD_N_VALUE col6, ATR6.CHILD_N_VALUE 
    col7, ATR7.CHILD_N_VALUE col8
    FROM ADDTL_TYPE_REL ATR1, ADDTL_TYPE_REL ATR2, ADDTL_TYPE_REL ATR3, ADDTL_TYPE_REL ATR4, 
    ADDTL_TYPE_REL ATR5, ADDTL_TYPE_REL ATR6, ADDTL_TYPE_REL ATR7
    WHERE ATR1.CHILD_N_VALUE = ATR2.parent_n_value
    AND ATR2.CHILD_N_VALUE = ATR3.parent_n_value
    AND ATR3.CHILD_N_VALUE = ATR4.parent_n_value
    AND ATR4.CHILD_N_VALUE = ATR5.parent_n_value
    AND ATR5.CHILD_N_VALUE = ATR6.parent_n_value
    AND ATR6.CHILD_N_VALUE = ATR7.parent_n_value
    AND ATR1.PARENT_FIELD_ID = 3934--highest dependency
    AND ATR1.CHILD_FIELD_ID = 3935--one level down
    AND ATR2.CHILD_FIELD_ID = 3936--two levels down
    AND ATR3.CHILD_FIELD_ID = 3937--three levels down
    AND ATR4.CHILD_FIELD_ID = 3938--four levels down
    AND ATR5.CHILD_FIELD_ID = 3939--five levels down
    AND ATR6.CHILD_FIELD_ID = 3940--six levels down
    AND ATR7.CHILD_FIELD_ID = 3941--seven levels down
    Order by col1, col2, col3, col4, col5, col6, col7, col8
    )
    select distinct (select name from addtl_type  where id = f.col1 and addtl_type.is_active = 1) as col1, 
            (select name from addtl_type where id = f.col2 and addtl_type.is_active = 1) as col2,
            (select name from addtl_type where id = f.col3 and addtl_type.is_active = 1) as col3,
            (select name from addtl_type where id = f.col4 and addtl_type.is_active = 1) as col4,
            (select name from addtl_type where id = f.col5 and addtl_type.is_active = 1) as col5,
            (select name from addtl_type where id = f.col6 and addtl_type.is_active = 1) as col6,
            (select name from addtl_type where id = f.col7 and addtl_type.is_active = 1) as col7,
            (select name from addtl_type where id = f.col8 and addtl_type.is_active = 1) as col8
    from ffdepend f;

I know that there are many variables here in that based on the amount of values return will determine the runtime. Currently this is taking over an hour. Just wanted to see if someone knew a more efficient way of writing this. I am fairly new to sql and looking to get some input.

Please let me know if more information is needed also. Thank you all in advance. Sonny

Here is some sample data for 8 tier. just to show something:

 Product Quality Issue  Yes Yes Yes Yes Yes Yes No
 Product Quality Issue  Yes Yes Yes Yes No  Yes Yes
 Product Quality Issue  Yes Yes No  No  Yes Yes No
 Product Quality Issue  Yes No  Yes No  No  No Yes
 Product Quality Issue  Yes No  No  Yes Yes Yes No
 Product Quality Issue  No  Yes Yes No  No  No Yes
 Product Quality Issue  No  Yes No  No  Yes No No
 Product Quality Issue  No  No  Yes No  No  No Yes
 Product Quality Issue  No  No  No  Yes Yes Yes No

Here is the orignial query that will produce the same result as above:

 SELECT DISTINCT AT1.NAME col1, AT2.NAME col2, AT3.NAME col3, AT4.NAME col4, 
 AT5.NAME col5, AT6.NAME col6, AT7.NAME col7, AT8.NAME col8
 FROM ADDTL_TYPE_REL ATR1, ADDTL_TYPE_REL ATR2, ADDTL_TYPE_REL ATR3, 
 ADDTL_TYPE_REL ATR4, ADDTL_TYPE_REL ATR5, ADDTL_TYPE_REL ATR6, 
 ADDTL_TYPE_REL ATR7,
 ADDTL_TYPE AT1, ADDTL_TYPE AT2, ADDTL_TYPE AT3, ADDTL_TYPE AT4, ADDTL_TYPE 
 AT5, ADDTL_TYPE AT6, ADDTL_TYPE AT7, ADDTL_TYPE AT8
 WHERE ATR1.CHILD_FIELD_ID = ATR2.PARENT_FIELD_ID
 AND ATR2.CHILD_FIELD_ID = ATR3.PARENT_FIELD_ID
 AND ATR3.CHILD_FIELD_ID = ATR4.PARENT_FIELD_ID
 AND ATR4.CHILD_FIELD_ID = ATR5.PARENT_FIELD_ID
 AND ATR5.CHILD_FIELD_ID = ATR6.PARENT_FIELD_ID
 AND ATR6.CHILD_FIELD_ID = ATR7.PARENT_FIELD_ID
 AND ATR1.PARENT_N_VALUE = AT1.ID
 AND ATR1.CHILD_N_VALUE = AT2.ID
 AND ATR2.CHILD_N_VALUE = AT3.ID
 AND ATR3.CHILD_N_VALUE = AT4.ID
 AND ATR4.CHILD_N_VALUE = AT5.ID
 AND ATR5.CHILD_N_VALUE = AT6.ID
 AND ATR6.CHILD_N_VALUE = AT7.ID
 AND ATR7.CHILD_N_VALUE = AT8.ID
 AND AT1.IS_ACTIVE = 1
 AND AT2.IS_ACTIVE = 1
 AND AT3.IS_ACTIVE = 1
 AND AT4.IS_ACTIVE = 1
 AND AT5.IS_ACTIVE = 1
 AND AT6.IS_ACTIVE = 1
 AND AT7.IS_ACTIVE = 1
 AND AT8.IS_ACTIVE = 1
 AND ATR1.PARENT_FIELD_ID = 3934--highest dependency
 AND ATR1.CHILD_FIELD_ID = 3935--one level down
 AND ATR2.CHILD_FIELD_ID = 3936--two levels down
 AND ATR3.CHILD_FIELD_ID = 3937--three levels down
 AND ATR4.CHILD_FIELD_ID = 3938--four levels down
 AND ATR5.CHILD_FIELD_ID = 3939--five levels down
 AND ATR6.CHILD_FIELD_ID = 3940--six levels down
 AND ATR7.CHILD_FIELD_ID = 3941--seven levels down
 Order by col1, col2, col3, col4, col5, col6, col7, col8;

Answer 1

Interesting problem, and hard, at least for me :) You are looking for distinct combinations of lookup values from hierarchical data. I tried several ways and here is what I could do:

with t as (
  select parent_field_id pid, parent_n_value pnv, 
         child_field_id  cid, child_n_value cnv, name
    from addtl_type_rel 
    left join addtl_type on child_n_value = addtl_type.id and is_active = 1
    where child_field_id between 3935 and 3941)
select (select name from addtl_type where id = a.pnv) root, path
  from (
    select distinct connect_by_root(pnv) pnv, 
           sys_connect_by_path(rpad(nvl(name, '?'), 3, ' '), ' - ') path
      from t where connect_by_isleaf = 1 
      start with pid = 3934 
      connect by prior cnv = pnv and pid = prior pid + 1) a

^dbfiddle

It does not put values in different columns, but we can cut them with easily substring. The more important is if it is faster. At first I wanted to filter data from addtl_type_rel as much as possible, join once with addtl_type. Then is main part, hierarchical query, which filters only leaf nodes and finds distinct paths.

Index on (child_n_value, parent_field_id) would be useful for connect by. I assumed that we should base on this sequence 3934-3935-...-3941. I just tried to avoid all these subselects from two same tables, maybe this will help you.