Tuning Oracle SQL (Temporary Table, Collections)

Question

There are 6 level tables which contains the parent child relationship and one table contains details of all names of objects for each level. I have to move the data into new table which will contains the relationship of one level object to another level object. Example the relationshop between district, town, city, state,country. So the table will have details the mappings of all district to cities, district to state etc..

Below query is for only district level to all other levels. I have to prepare similar queries for all other levels.

WITH LEVEL1 AS 
(SELECT NAME, CHILDNAME
  FROM S_LEVEL1 P),
  LEVEL2 AS 
  (SELECT NAME, CHILDNAME FROM S_LEVEL2 ),
  LEVEL3 AS
  (SELECT NAME, CHILDNAME FROM S_LEVEL3 ),
  LEVEL4 AS
  (SELECT NAME, CHILDNAME FROM S_LEVEL4 ),
    LEVEL5 AS
  (SELECT NAME, CHILDNAME FROM S_LEVEL5 ),
  LEVEL6 AS
  (SELECT NAME, CHILDNAME FROM S_LEVEL6 ),
  REL_LEVEL AS
  (SELECT DISTINCT NAME
  FROM S_RELATIONSHIP
  WHERE TYPE ='LEVEL0'  )   ,
  REL_ALL_LEVEL AS
  (SELECT DISTINCT NAME,TYPE
  FROM S_RELATIONSHIP
  WHERE TYPE in ('LEVEL1','LEVEL2','LEVEL3','LEVEL4','LEVEL5','LEVEL6'  )   ,

 Select distinct REL_LEVEL.NAME,'LEVEL0',LEVEL1.NAME,'LEVEL1'
 from REL_LEVEL, LEVEL1
 where REL_LEVEL.NAME=LEVEL1.CHILDNAME
 and exists (Select 1 from REL_ALL_LEVEL where REL_ALL_LEVEL.NAME= LEVEL1.NAME and REL_ALL_LEVEL.TYPE ='LEVEL1')

 union all

  Select distinct REL_LEVEL.NAME,'LEVEL0',LEVEL2.NAME,'LEVEL2'
 from REL_LEVEL, LEVEL1,LEVEL2
 where REL_LEVEL.NAME=LEVEL1.CHILDNAME
 and LEVEL1.NAME= LEVEL2.CHILDNAME
 and exists (Select 1 from REL_ALL_LEVEL where REL_ALL_LEVEL.NAME= LEVEL2.NAME and REL_ALL_LEVEL.TYPE ='LEVEL2')

 union all

   Select distinct REL_LEVEL.NAME,'LEVEL0',LEVEL3.NAME,'LEVEL3'
 from REL_LEVEL, LEVEL1,LEVEL2,LEVEL3
 where REL_LEVEL.NAME=LEVEL1.CHILDNAME
 and LEVEL1.NAME= LEVEL2.CHILDNAME
  and LEVEL2.NAME= LEVEL3.CHILDNAME
   and exists (Select 1 from REL_ALL_LEVEL where REL_ALL_LEVEL.NAME= LEVEL3.NAME and REL_ALL_LEVEL.TYPE ='LEVEL3')
   union all

   Select distinct REL_LEVEL.NAME,'LEVEL0',LEVEL4.NAME,'LEVEL4'
 from REL_LEVEL, LEVEL1,LEVEL2,LEVEL3,LEVEL4
 where REL_LEVEL.NAME=LEVEL1.CHILDNAME
 and LEVEL1.NAME= LEVEL2.CHILDNAME
  and LEVEL2.NAME= LEVEL3.CHILDNAME
  and LEVEL3.NAME= LEVEL4.CHILDNAME
  and exists (Select 1 from REL_ALL_LEVEL where REL_ALL_LEVEL.NAME=LEVEL4.NAME and REL_ALL_LEVEL.TYPE ='LEVEL4')

   union all

   Select distinct REL_LEVEL.NAME,'LEVEL0',LEVEL5.NAME,'LEVEL5'
 from REL_LEVEL, LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5
 where REL_LEVEL.NAME=LEVEL1.CHILDNAME
 and LEVEL1.NAME= LEVEL2.CHILDNAME
  and LEVEL2.NAME= LEVEL3.CHILDNAME
  and LEVEL3.NAME= LEVEL4.CHILDNAME
  and LEVEL4.NAME= LEVEL5.CHILDNAME
  and exists (Select 1 from REL_ALL_LEVEL where REL_ALL_LEVEL.NAME=LEVEL5.NAME and REL_ALL_LEVEL.TYPE ='LEVEL5')

To simplify i used WITH clause but for each level i will have to write the same query 6 number of times(the below query is for one level, for 6 levels i will have to write same query 6 times). My question is ..is there any way i can store the data into some temporary tables for each level and then I can play with sql to get required data.
I have worked on MSSQL and for this i would have created a separate temp table for each level and then created index on top of that and in child proc then used those temp tables to get required data. However I am not sure how to implement this in Oracle.

One way which i googled is use separate nested table for each level and then use BULK Collect to move data into those nested tables and then re-use those nested tables in the same package multiple times. Does this make sense or is there any better way.

Can someone give example how this can be implemented in Oracle.

Answer 1

If you find the SQL difficult to read you can create views for the different levels.

If you have actual performance problems you can make them materialized views.