How to join static table once and use it in building complex queries-CodePudding

--table 1
CREATE TABLE test1 (
    e_id      NUMBER(10),
    test_col1  NUMBER(10)
);

INSERT INTO test1 VALUES(1,62);

--table 2
CREATE TABLE test2 (
    e_id      NUMBER(10),
    test_col2  NUMBER(10)
);

INSERT INTO test2 VALUES(1,63);

--Static table
CREATE TABLE lookup_table (
    l_id     NUMBER(10),
    l_value  VARCHAR2(30)
);

INSERT INTO lookup_table VALUES(62,'value_1');
INSERT INTO lookup_table VALUES(63,'value_2');

DB version: Oracle 18c

I want to create a view based on table 1, table 2 and static table (lookup/reference table). Basically I need to pull all the EUCs which are there in table1 along with the two additional columns which is lookup_value1 and lookup_value2. I tried joining the two tables and then joining static table to fetch the l_value from lookup table based on the ids present in table1 and table2.

My attempt:

SELECT t1.e_id,
lt.l_value AS lookup_value1,
lt1.l_value AS lookup_value2
FROM test1 t1
LEFT JOIN test2 t2 ON(t1.e_id = t2.e_id)
LEFT JOIN lookup_table lt ON(lt.l_id = t1.test_col1)
LEFT JOIN lookup_table lt1 ON(lt1.l_id = t2.test_col2);

This is giving me the expected result but here the problem is I need to join lookup_tableevery time I need to fetch the value from this table. In my case, I have joined lookup_table twice. Is there any way to join this table only once and fetch the required value from the lookup table instead of joining it again and again which will lead to a performance degradation issue

CodePudding user response：

Based on my experience, there were two ways to resolve this problem.

Use trigger to add one record into the lookup_table. But need to handle l_value filed's value that need to be provided.
Don't use lookup_table, add one column(l_value filed's value) into test1 & test2 table in order to save those static data.

CodePudding user response：

If you are not going to have duplicate e_id rows the you could use UNION ALL and then join once and PIVOT:

SELECT e_id, l_value1, l_value2
FROM   (
  SELECT t.e_id, t.type, l.l_value
  FROM   ( SELECT e_id, 1 AS type, test_col1 AS test_col FROM test1
           UNION ALL
           SELECT e_id, 2, test_col2 FROM test2 ) t
         LEFT OUTER JOIN lookup_table l
         ON (t.test_col = l.l_id)
)
PIVOT ( MAX(l_value) FOR type IN (1 AS l_value1, 2 AS l_value2) )

Which, for the sample data, outputs:

E_ID L_VALUE1 L_VALUE2

1 value_1 value_2

E_ID	L_VALUE1	L_VALUE2
1	value_1	value_2

Or, the same query using sub-query factoring clauses:

WITH complex_query1 (e_id, test_col1) AS (
  SELECT * FROM test1
),
complex_query2 (e_id, test_col2) AS (
  SELECT * FROM test2
),
combined_query (e_id, type, test_col) AS (
  SELECT e_id, 1, test_col1 FROM complex_query1
UNION ALL
  SELECT e_id, 2, test_col2 FROM complex_query2
),
lookup_values (e_id, type, l_value) AS (
  SELECT t.e_id, t.type, l.l_value
  FROM   combined_query t
         LEFT OUTER JOIN lookup_table l
         ON (t.test_col = l.l_id)
)
SELECT e_id, l_value1, l_value2
FROM   lookup_values
PIVOT ( MAX(l_value) FOR type IN (1 AS l_value1, 2 AS l_value2) )

db<>fiddle here