many to many select -view efficiently

The problem to be solved here is described in the next post. A while ago I wrote about creating a view on top of many to many relation. In that article there was no need to select any columns from the many to many relation table. A localization view might be something that you want to have columns received from. Here is an example of such. A bit of preparation first. We have localized names for country and city names first. Create a view of those to be used from several usages. And a query example that joins a shop table to the view.

drop table shop;

drop table countrylang;

drop table citycountry;

drop table citylang;

drop table country;

drop table city;

drop table lang;



create table lang (lang varchar2(2) constraint lang_pk primary key);

insert into lang values ('fi');

insert into lang values ('en');

insert into lang values ('sv');



create table country(country varchar2(3) constraint country_pk primary key);

insert into country values ('FI');

insert into country values ('SV');


create table countrylang(country constraint countrylang_country_fk references country
                       , lang constraint countrylang_lang_fk references lang
                       , name varchar2(200) not null
                       , constraint countrylang_pk primary key (country,lang)
                       ) organization index compress 1
;

insert into countrylang values ('FI','fi','Suomi');

insert into countrylang values ('FI','sv','Finland');

insert into countrylang values ('FI','en','Finland');

insert into countrylang values ('SV','fi','Ruotsi');

insert into countrylang values ('SV','sv','Sweden');

insert into countrylang values ('SV','en','Sweden');


create table city(city varchar2(3) constraint city_pk primary key);

insert into city values ('tpe');

insert into city values ('hki');

insert into city values ('sto');

create table citylang(city constraint citylang_city_fk references city
                    , lang constraint citylang_lang_fk references lang
                    , name varchar2(200) not null
                    , constraint citylang_pk primary key (city,lang)
                    ) organization index compress 1
;

insert into citylang values ('tpe','fi','Tampere');

insert into citylang values ('tpe','sv','Tammerfors');

insert into citylang values ('tpe','en','Tampere');

insert into citylang values ('hki','fi','Helsinki');

insert into citylang values ('hki','sv','Helsingfors');

insert into citylang values ('hki','en','Helsinki');


create table citycountry(city references city, country references country, constraint citycountry_pk primary key (city,country));

insert into citycountry values('tpe','FI');

insert into citycountry values('hki','FI');

insert into citycountry values('sto','SV');


create view citycountrynames as 
select la.lang
     , ci.city
     , co.country
     , (select cl.name from citylang cl where cl.city=ci.city and cl.lang=la.lang) cityname
     , (select cl.name from countrylang cl where cl.country=co.country and cl.lang=la.lang) countryname
  from lang la, city ci, country co
;

So that was the view. Be sure to populate the tables underneath correctly to avoid nulls.

create table shop (shop int constraint shop_pk primary key
                 , shop_name varchar2(200) not null
                 , city references city not null
                 , country references country
                 , constraint shop_citycountry_fk foreign key (city,country) references citycountry)
;

create index shop_city_fk_idx on shop(city);

create index shop_country_fk_idx on shop(country);

insert into shop values (1,'Helsinki shop','hki','FI');

insert into shop values (2,'Tampere shop','tpe','FI');

insert into shop values (3,'Stockholm shop','sto','SV');

commit;

select cn.cityname,cn.countryname
  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
;

It is late Saturday evening now. I will not show any execution plans now. Try those out yourself. I will have another taste of Isle of Jura.

SQL> variable lang varchar2(2);
SQL> variable city varchar2(3);
SQL> exec :city := 'tpe'

PL/SQL procedure successfully completed.

SQL> exec :lang := 'fi'

PL/SQL procedure successfully completed.

SQL> select sh.shop_name, cn.cityname,cn.countryname
  2    from citycountrynames cn
  3   inner join shop sh
  4      on sh.city=cn.city and sh.country=cn.country
  5   where cn.city=:city
  6     and cn.lang=:lang
  7  ;

SHOP_NAME
------------------------------------------------------

CITYNAME
------------------------------------------------------

COUNTRYNAME
------------------------------------------------------

Tampere shop
Tampere
Suomi