This discussion is archived
5 Replies Latest reply: Feb 10, 2013 2:38 PM by Frank Kulash RSS

SQL Rewrite help

sk12345678 Newbie
Currently Being Moderated
Can this SQL be written in a better way for performance. Appreciate your inputs. Thanks

Select 'A','A', ...
from TabA , TabB
where TabA.colA = TabB.ColA
UNION ALL
Select 'A','B',....
from TabA, TabB
where TabA.colA = TabB.colB
UNION ALL
Select 'B','A',...
from TabA, TabB
where TabA.colB = TabB.colA
UNION ALL
Select 'B','B',....
from TabA, TabB
where TabA.colB = TabB.colB
  • 1. Re: SQL Rewrite help
    damorgan Oracle ACE Director
    Currently Being Moderated
    This is homework. Please do it and if you can't see your instructor.

    For help from us you need to post the following:
    1. Database version.
    2. DDL to create the two tables.
    3. DML to load sample data.
    4. Your best attempt at writing the new SQL statement yourself.

    You will learn nothing if we hand you the answer. Education requires applying mental effort.

    Here is one hint appropriate in depth to point you in the right direction. You are doing a full table scan on both tables four times ... it would be more efficient to hit each table once then perform the joins after the fact and there are two different constructs that will allow you to do this: INLINE VIEW and COMMON TABLE EXPRESSION both of which are fully supported by currently supported versions of the Oracle database.
  • 2. Re: SQL Rewrite help
    ranit B Expert
    Currently Being Moderated
    Something like this...? not tested
    with xx as(
      select 
         TabA.colA aa, TabA.colB ab, TabB.ColA ba, TabB.colB bb
      from
         TabA, TabB
    )
     Select 'A','A',....
       from XX where aa = ba
    UNION ALL
     Select 'A','B',....
       from XX where aa = bb
    UNION ALL
     Select 'B','A',....
       from XX where ab = ba
    UNION ALL
     Select 'B','B',....
       from XX where bb = bb
    Ranit B.
  • 3. Re: SQL Rewrite help
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    As mentioned above, you can improve performance by getting rid of the UNIONs, and doing a single join of the tables instead.
    A single join will produce 1 row of output for every pair of matching rows in the two tables. However, your present query can have as many as 4 rows in the result set that all correspond to the same pair of matching rows, depending on your data. (This is one reason why we can't help too much unless you post CREATE TABLE and INSERT statements for some sample data.) To get the exact same output from a more efficient single join, you would need to Unpivot the results of the join. Like so many other things, the best way to unpivot differs from version to version. (This is one reason why you need to post which version of Oracle your're using, e.g. 11.2.0.2.0.)

    Depending on your exact requirements, a sub-query (either an in-line view or a WITH clause) may be handy, but it may not be necessary.

    If you'd like help, please post all the information that Damorgan requested, especially your best attempt at solving the problem yourself.
    See the forum FAQ {message:id=9360002}
  • 4. Re: SQL Rewrite help
    sk12345678 Newbie
    Currently Being Moderated
    we are using Oracle Database 11g 11.2.0.3.0.
  • 5. Re: SQL Rewrite help
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    sk12345678 wrote:
    we are using Oracle Database 11g 11.2.0.3.0.
    Good; the SELECT ... UNPIVOT feature (new in Oracle 11) might be very useful in this problem.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points