CODEWITHSUNDEEP
sqloracle-databasedatabase-designmultilingual
Duncan
Duncan

Reputation: 183

Oracle view with ML tables I want optimizer to use one ML table only

Oracle 11g. I have ML (multilingual) child tables for text, one child table per parent/language. So for a parent table PRODUCT, I have PRODUCT_EN, PRODUCT_FR etc. I want to have a single view that combines all ML tables. When I query this for a specific language I'd like Oracle optimizer (Join Elimination?) to be smart enough to only access the ML table required. So if I specify lan_id (language id) = 5 (French) it should ignore the English and other other translation tables. Here's a couple of views that return the correct data, but from the plan I see a query against French still access English:

CREATE OR REPLACE VIEW myproduct_ml AS
SELECT p.PROD_ID, STATUS, 
       ml.lan_id, ml.prod_name
FROM myproduct p JOIN
(SELECT en.* FROM myproduct_en en WHERE lan_id = 1
 UNION ALL
 SELECT fr.* FROM myproduct_fr fr WHERE lan_id = 5) ml
 ON (p.prod_id = ml.prod_id);

 CREATE OR REPLACE VIEW myproduct_ml AS
SELECT p.PROD_ID, STATUS, 
       ml.lan_id, ml.prod_name
FROM myproduct p JOIN myproduct_en ml ON (p.prod_id = ml.prod_id) WHERE lan_id = 1
 UNION ALL
SELECT p.PROD_ID, STATUS, 
       ml.lan_id, ml.prod_name
FROM myproduct p JOIN myproduct_fr ml ON (p.prod_id = ml.prod_id) WHERE lan_id = 5;

 select * from myproduct_ml WHERE lan_id = 5 ;

lan_id doesn't need to be in the ML table, as the suffix specifies the language.

Upvotes: 2

Views: 53

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

Why do you have a separate table for each language anyway? That would mean to have to add a table everytime you introduce a new language and have to change all queries. That must not be. Added data should lead to added rows only so that the queries continue working.

So make this one language table instead and you are done.

Upvotes: 1

Related Questions