Improve Query performance in Oracle & Postgres

You should never embed sql queries in the select component of your statement if at all possible.

You could rewrite it so that it goes against all the tables appropriately.

select num, ... from test_prim a , test_lov b , test_lov c , test_lov d where a.cat1_code = b.column_lkup and b.catgry = 'CAT1_CODE' and ...

The only problem with this approach is in the case that one of the categories in test_prim is null, in which case your joins against test_lov have to be outer joins.

This also has the benefit of using indexes if the test_lov.column_lkup column has an index on it.

LOL - this is turning into a run-on. Your selects in the select clause basically cause full table scans and then the resolution occurs in the PGA, which is slow. The other approach stops each time there is a success, so it's much faster.

In oracle, you can unpivot the prim table and use the result to join to lov table.

with primunpiv as 
(select * from test_prim
 unpivot
 (code for category in (CAT1_CODE,CAT2_CODE,CAT3_CODE))
)
select p.num
,max(case when p.category = 'CAT1_CODE' then l.ext_code end) cat1
,max(case when p.category = 'CAT2_CODE' then l.ext_code end) cat2
,max(case when p.category = 'CAT3_CODE' then l.ext_code end) cat3
from primunpiv p
join testlov l on p.code = l.column_lkp and p.category = l.category
group by p.num

My oracle isn't very good, but I've done a similar thing in the past in SQL Server. I think this should work, and might improve your performance. If there's always a corresponding record for each different CAT code, you can change these to INNER JOIN's.

SELECT 
NUM,
B.EXT_CODE CAT1, 
C.EXT_CODE CAT2, 
D.EXT_CODE CAT3
FROM TEST_PRIM A
LEFT JOIN TEST_LOV B ON B.CATEGRY='CAT1_CODE' AND B.COLUMN_LKP = A.CAT1_CODE
LEFT JOIN TEST_LOV C ON C.CATEGRY='CAT2_CODE' AND C.COLUMN_LKP = A.CAT2_CODE
LEFT JOIN TEST_LOV D ON D.CATEGRY='CAT3_CODE' AND C.COLUMN_LKP = A.CAT3_CODE

Having index on CATEGORY, COLUMN_LKP will avoid full table scans and fetch results faster in case you have huge data set.

SQLFiddle: Postgres

WITH
cat1 AS ( SELECT EXT_CODE, COLUMN_LKP FROM LOV WHERE CATEGORY='CAT1_CODE' ),
cat2 AS ( SELECT EXT_CODE, COLUMN_LKP FROM LOV WHERE CATEGORY='CAT2_CODE' ),
cat3 AS ( SELECT EXT_CODE, COLUMN_LKP FROM LOV WHERE CATEGORY='CAT3_CODE' )

SELECT num, cat1.ext_code CAT1, cat2.ext_code CAT2, cat3.ext_code CAT3
FROM prim
LEFT JOIN cat1 ON prim.cat1_code::varchar(1) = cat1.column_lkp
LEFT JOIN cat2 ON prim.cat2_code = cat2.column_lkp
LEFT JOIN cat3 ON prim.cat3_code = cat3.column_lkp
;

Problems w/ Your Question

1. Your query uses TEST_LOV and TEST_PRIM, but your question labels them as LOV and PRIM, respectively
2. Your query uses CATEGRY, but your question defines the table with field, CATEGORY