Query Refactor for Better Efficiency 1

[userMikeD]

Hi, I'm writing a query which will grab translations from a table. The rules are that I select the keys with the specific locale first, then I select the keys with the language, finally select the rest in english.

I wrote the query below, but I'm not happy with the 12 step explain plan and cost of 486. It's already using the PK also. Is there a cleaner way to write this that I'm not seeing?

SELECT key_name, key_value, locale
  FROM translation t1
 WHERE set_id = 1
   AND locale = 'fr-FR'
 UNION ALL
SELECT key_name, key_value, locale
  FROM translation t2
 WHERE set_id = 1
   AND locale = 'fr'
   AND NOT EXISTS(SELECT 1
                    FROM translation t3
                   WHERE set_id = 1
                     AND locale = 'fr-FR'
                     AND t3.key_name = t2.key_name)
 UNION ALL
SELECT key_name, key_value, locale
  FROM translation t4
 WHERE set_id = 1
   AND locale = 'en'
   AND NOT EXISTS(SELECT 1
                    FROM translation t5
                   WHERE set_id = 1
                     AND locale IN ('fr-FR', 'fr')
                     AND t5.key_name = t4.key_name);

Thanks for looking!

 

[LKBrwnDBA]

You could try something like this:

SELECT *
  FROM (SELECT key_name,
               key_value,
               locale,
               ROW_NUMBER ()
                  OVER (
                     PARTITION BY key_name
                     ORDER BY DECODE (locale, 'en', 1, 'fr-FR', 2, ' fr', 3))
                  rn
          FROM translation
         WHERE set_id = 1 AND locale IN ('fr-FR', 'fr', 'en'))
 WHERE rn < 3;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[userMikeD]

Thank you for your suggestion! Not only is that way more concise and elegant, but the explain plan also shows far fewer steps, lower cost, and lower I/O. I can't thank you enough for your help! That is really awesome! Thanks again!