CREATE OR REPLACE VIEW V1
AS
SELECT a.id,max(b.phone),maX(c.loc_code)
FROM    person a, PHONE b,JOB_TYPE c,location d
WHERE a.id = b.id(+)
      AND a.id = c.id(+)
      AND c.loc_code = d.loc_code(+)
      AND b.Dir_flag = 'Y'
      AND a.no_flag='N' 	
      GROUP BY a.id



      
Explian Plan:


Description                     Object Owner		Object Name	Cost	Cardinality	Bytes
SELECT STATEMENT, GOAL = CHOOSE						8285	43726		3716710
 SORT GROUP BY								8285	43726		3716710
  MERGE JOIN OUTER							1036	444702		37799670
   SORT JOIN								1013	10989		835164
    MERGE JOIN OUTER							871	10989		835164
     MERGE JOIN								654	10989		736263
      SORT JOIN								254	10989		428571
       TABLE ACCESS FULL	ABC			PHONE		173	10989		428571
      SORT JOIN								400	43726		1224328
       TABLE ACCESS FULL	ABC			PERSON		154	43726		1224328
     SORT JOIN								218	43726		393534
      TABLE ACCESS FULL		ABC			JOB_TYPE	96	43726		393534
   SORT JOIN								23	4411		39699
    TABLE ACCESS FULL		ABC			LOCATION	10	4411		39699