many to many select -view efficiently -the problem

My last post was describing a solution to problem that I did not explain beforehand. I also chose a bad example of two things to be localized. The original problem did not have relationship between the things to be localized. The example has the citycountry table there and it should be used in the citycountrynames view. It was yet again difficult to generate a good example. But the original problematic view was something like:

create or replace view citycountrynames_slow as 
select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
     , col.name countryname
  from city ci 
  cross join country co 
  left outer join citylang cl on cl.city=ci.city
  left outer join countrylang col on col.country=co.country and col.lang=cl.lang
;

Cross join there. And that is not the problem. The plan for the query was

select cn.cityname,cn.countryname,sh.shop_name
  from citycountrynames_slow cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;

-------------------------------------------------------------
| Id  | Operation                     | Name                |
-------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |
|   1 |  NESTED LOOPS OUTER           |                     |
|   2 |   NESTED LOOPS                |                     |
|   3 |    VIEW                       |                     |
|   4 |     NESTED LOOPS              |                     |
|   5 |      NESTED LOOPS             |                     |
|   6 |       INDEX UNIQUE SCAN       | CITYLANG_PK         |
|   7 |       INDEX UNIQUE SCAN       | CITY_PK             |
|   8 |      INDEX FAST FULL SCAN     | COUNTRY_PK          |
|   9 |    TABLE ACCESS BY INDEX ROWID| SHOP                |
|  10 |     INDEX RANGE SCAN          | SHOP_COUNTRY_FK_IDX |
|  11 |   INDEX UNIQUE SCAN           | COUNTRYLANG_PK      |
-------------------------------------------------------------

One problem there is INDEX FAST FULL SCAN COUNTRY_PK. And the country table was a big one. The view query has the problem of outer joining to several tables. That is why the optimizer is not able to merge the view to the main query.

select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
     , col.name countryname
  from city ci, country co, citylang cl, countrylang col 
 where cl.city(+)=ci.city
   and col.country(+)=co.country 
   and col.lang(+)=cl.lang
;

ORA-01417: a table may be outer joined to at most one other table

One can avoid ORA-01417 by rewriting the query

select xx.*, col.name countryname
 from (
select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
  from city ci, country co, citylang cl
 where cl.city(+)=ci.city) xx, countrylang col 
where  col.country(+)=xx.country 
   and col.lang(+)=xx.lang
;

Actually a bit similar structure may be seen in the plan line 3. A view without a name is appearing to the plan. The tuned previous post view is behaving much more efficiently for the query. No full scans. The tuning was to add the lang table to the view query. Move the many parts to select list sub query.

select cn.cityname,cn.countryname,sh.shop_name
  from citycountrynames cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;

----------------------------------------------------------
| Id  | Operation                     | Name             |
----------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |
|   1 |  INDEX UNIQUE SCAN            | CITYLANG_PK      |
|   2 |  INDEX UNIQUE SCAN            | COUNTRYLANG_PK   |
|   3 |  NESTED LOOPS                 |                  |
|   4 |   NESTED LOOPS                |                  |
|   5 |    NESTED LOOPS               |                  |
|   6 |     INDEX UNIQUE SCAN         | CITY_PK          |
|   7 |     INDEX UNIQUE SCAN         | LANG_PK          |
|   8 |    TABLE ACCESS BY INDEX ROWID| SHOP             |
|   9 |     INDEX RANGE SCAN          | SHOP_CITY_FK_IDX |
|  10 |   INDEX UNIQUE SCAN           | COUNTRY_PK       |
----------------------------------------------------------