5 Replies Latest reply: Feb 10, 2013 4:38 PM by Frank Kulash RSS

    SQL Rewrite help

    sk12345678
      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
          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
            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
              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
                we are using Oracle Database 11g 11.2.0.3.0.
                • 5. Re: SQL Rewrite help
                  Frank Kulash
                  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.