Database Administration
oracle query-performance oracle-11g-r2 performance-tuning
Updated Sat, 23 Jul 2022 20:48:30 GMT

Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?


I have two tables with below structures:

create table TABLE_LC
( lc_num   NUMBER,
  brn_code NUMBER,
  brn_role NUMBER)
create table BRN
( brn_code NUMBER,
  brn_name VARCHAR2(50))

Some example data for each table is this:

"TABLE_LC":
lc_num    |  brn_code  |  brn_role
------------------------------------
  1       |     1      |  1010  
  1       |     2      |  2020  
"BRN":
brn_code  |  brn_name  
---------------------
  1       |   BAHAR    
  2       |    KAJ     

I need To have one record for each lc_num in TABLE_LC and the desired output based on the given tables and data is this:

lc_num    |  Advising_Bank  |  Issuing_Bank
-------------------------------------------
  1       |     BAHAR       |  KAJ 

What I've written is below query which gives me the correct output,considering the fact that we know brn_role=1010 represents Advising Bank and brn_role=2020 represents Issuing Bank

select t1.lc_num, 
       t2.brn_name "Advising Bank", 
       t4.brn_name "Issuing bank"
  from TABLE_LC t1
 inner join BRN t2
    on t1.brn_code = t2.brn_code
   and t1.brn_role = 1010
 inner join TABLE_LC t3
    on t3.lc_num = t1.lc_num
 inner join BRN t4
    on t4.brn_code = t3.brn_code
   and t3.brn_role = 2020

As you can see, I have joined the base table twice, I want to know if there are better ways of getting the desired output ?

Thanks in advance




Solution

Better? It depends.

If the primary key of TABLE_LC is (lc_num, brn_role) then you could do something like this:

SELECT
  lc_Num
 ,MAX
    (
      CASE
        WHEN brn_role = 1010 THEN brn_name
        ELSE NULL
      END
    ) AS Advising_Bank
 ,MAX
    (
      CASE
        WHEN brn_role = 2020 THEN brn_name
        ELSE NULL
      END
    ) AS Issuing_Bank
FROM
  (
    SELECT
      LC.lc_num
     ,LC.brn_role
     ,LC.brn_code
     ,BRN.brn_name
    FROM
      TABLE_LC LC
    INNER JOIN
      BRN BRN
        ON BRN.brn_code = LC.brn_code
  ) x
GROUP BY
  lc_num

Which, you'll note:

  1. Is about the same amount of code
  2. Is harder to understand
  3. Even though it only performs one join, it still might not produce the best query plan, depending on whether or not TABLE_LC is index-organized.

You could also use PIVOT, but again, probably not that much time savings and you're left with the problem of dealing with goofy column names.

Joins on primary keys/appropriate indexes are generally not that expensive, so avoiding them only makes sense in certain situations.







External Links

External links referenced by this document: