The create table script is:

Create TABLE CX_GEO_DATA_TEMP
(
  NAME               VARCHAR2(255 CHAR),
  SOURCE_SYS_ID      VARCHAR2(32 CHAR),
  PAR_SOURCE_SYS_ID  NUMBER,
  TYPE               VARCHAR2(30 CHAR)
);

The insert statements are:

insert into cx_geo_data_temp values ('423-014', '13801437',13801432,'Zip');
 insert into cx_geo_data_temp values ('GWANGMYEONG-SI', '13801432',8828178,'City');
 insert into cx_geo_data_temp values ('GYEONGGI-DO', '8828178',2092611,'State');
 insert into cx_geo_data_temp values ('KR', '2092611',NULL,'Country');

insert into cx_geo_data_temp values ('S71 5RT', '7538234',7536653,'Zip');
 insert into cx_geo_data_temp values ('BARNSLEY', '7536653',6939044,'City');
 insert into cx_geo_data_temp values ('SOUTH YORKSHIRE', '6939044',1821815,'State');
 insert into cx_geo_data_temp values ('GB', '1821815',NULL,'Country');




Below is the query for 1 of the hierarchies in the table. 
The relation between parent and child is established via columns source_sys_id and par_source_sys_id.



SELECT a.source_sys_id,
         a.type,
         a.par_source_sys_id,
         a.name
   FROM cx_geo_data_temp a
 START WITH a.source_sys_id = 13801437
 CONNECT BY  PRIOR a.par_source_sys_id =  a.source_sys_id


I have used the below query to find the parent of type country:


Select * from
(SELECT a.source_sys_id,
         a.type,
         a.par_source_sys_id,
         a.name
   FROM cx_geo_data_temp a
 START WITH a.source_sys_id = 13801437
 CONNECT BY  PRIOR a.par_source_sys_id =  a.source_sys_id)hier_tab
 where hier_tab.type='Country'



This connect by query is taking a lot of time (approx 4 mins) on the actual table
to fetch the parent country record., which is not desirable., nor any indexes are being used.

So I wanted to create a materialized view to store the child and parent(country) records to avoid connect by query.

But  the above query is not displaying me the child record in the result set. I want the result set as below:

Source_Sys_id_Child   Child_Type   Source_Sys_id      Type             Name
13801437           Zip         2092611       Country    KR

Secondly I need to do this for all the rows and not a single row.