1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with creating a view with extra column

Discussion in 'SQL PL/SQL' started by james shallow, Apr 25, 2017.

  1. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    I am trying to use LEFT JOIN to replace the MINUS operator as the performance is poor
    but I couldn't fugure out how to select the extra column from the tables.
    what is the best way to archieve this?

    Code (SQL):
    /* This is the original sql to create the new VIEW */
    CREATE OR REPLACE FORCE VIEW "view_c" ("a", "b", "c", "d", "e", "f") AS
      SELECT a,
             b,
             c,
             d,
             e,
            '1'f
      FROM (SELECT * FROM table_1
            MINUS
            SELECT * FROM table_2)
      UNION
      SELECT a,
             b,
             c,
             d,
             e,
            '0'f
      FROM table_2);
    /*ddl */
    Both tables_1&2 contain columns: a,b,c,d,e,

    /* This code is suppose to replace the MINUS */
    Select
    t1.a, t1.b, t1.c, t1.d, t1.e
    From table_1 t1 LEFT JOIN table_2 on (t1.a = t2.a)

    The problem is that because this view is creating extra column (f) as '1' from table_1 and '0' from table_2,
    I am not sure how to select these values on my LEFT JOIN to populate the new VIEW_C.


    Thanks for your help
     
    Last edited: Apr 25, 2017
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    768
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    here are two problematic points :
    1) MINUS
    2) UNION


    can you provide here sql-plan :
    Code (Text):

    explain plan for  Select
    t1.a, t1.b, t1.c, t1.d, t1.e
    From table_1 t1
    LEFT JOIN table_2 on (t1.a = t2.a)
    where t2.a is null;
     
     
    james shallow likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why use MINUS when you can do this:

    Code (SQL):
    SQL> CREATE OR REPLACE FORCE VIEW view_c (a, b, c, d, e, f) AS
      2    SELECT a,
      3            b,
      4            c,
      5            d,
      6            e,
      7           '1' f
      8    FROM table_1
      9    WHERE a NOT IN (SELECT a FROM table_2)
    10    UNION
    11    SELECT a,
    12            b,
    13            c,
    14            d,
    15            e,
    16           '0' f
    17    FROM table_2;

    VIEW created.

    SQL>
    SQL> SELECT * FROM view_c;

             A          B          C          D          E F                      
    ---------- ---------- ---------- ---------- ---------- -                      
             1          1          1          1          1 1                      
             2          2          2          2          2 1                      
             3          3          3          3          3 1                      
             4          4          4          4          4 1                      
             5          5          5          5          5 1                      
             6          6          6          6          6 1                      
             7          7          7          7          7 1                      
             8          8          8          8          8 1                      
             9          9          9          9          9 1                      
            10         10         10         10         10 1                      
            11         11         11         11         11 1                      
    ...
             A          B          C          D          E F
    ---------- ---------- ---------- ---------- ---------- -
          1497       1497       1497       1497       1497 0
          1498       1498       1498       1498       1498 0
          1499       1499       1499       1499       1499 0
          1500       1500       1500       1500       1500 0

    1500 ROWS selected.

    SQL>
    It will probably perform better.
     
    james shallow likes this.
  4. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Thanks David
     
  5. Sgraphinfotec

    Sgraphinfotec Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    Thanks for sharing the useful information...It was very helpful information for me..