fallback for selecting locale dependent information is too expensive

From: Christian Mallwitz <mallwitz_at_acm.org>
Date: Wed, 24 Apr 2002 10:17:36 +0200
Message-ID: <3CC66A20.AE8C68A5@acm.org>

hello,

if have a table holding information to be displayed by the user. this information is available in multiple languages/locale. usually this information is not available in all languages. the user can have it's own configured language/locale, then there is the default language/locale of his organization and a general default language/locale. all can these language/locale can be different

I use a function to retrieve the information based on the configured languages/locale and I don't know for which locale there will be information available. as my productinformation table large it is expensive as most the time the information is only found in the lead locale. on the other hand even if the information is available it might be available in the user locale as well.

Question: are there any clever ideas how to solve this situation more efficiently (maybe by changing the database design)? note: of course there is an index over productinformation(productid, localeid).

example:

function getlocalizedname

         (p_productid in varchar2,
          p_userlocale in varchar2, p_orglocale in varchar2, 
p_leadlocale in varchar2)
         return varchar is

     return_val varchar2(258);
begin
     begin
         select name into return_val from productinformation
          where productid = p_productid and localeid = p_userlocale;

         return return_val;
         exception when no_data_found then null;
     end;

     begin
         select name into return_val from productinformation
          where productid = p_productid and localeid = p_orglocale;

         return return_val;
         exception when no_data_found then null;
     end;

     begin
         select name into return_val from productinformation
          where productid = p_productid and localeid = p_leadlocale;

         return return_val;
         exception when no_data_found then null;
     end;

     return null;

end getlocalizedname;

thanks
christian Received on Wed Apr 24 2002 - 03:17:36 CDT