4 Replies Latest reply: Nov 10, 2009 7:38 AM by 726157 RSS

    Performance issue with connect by level query

    726157
      Hi I have a problem with connect by level in oracle.

      My table is :


      J_USER_CALENDAR
      --------------------------

      USER_NAME     FROM_DATE     TO_DATE     COMMENTS
      -------------------------------------------------------------------------------------------------
      Uma Shankar     2-Nov-09     5-Nov-09     Comment1
      Veera     11-Nov-09     13-Nov-09     Comment2
      Uma Shankar     15-Dec-09     17-Dec-09     Commnet3
      Vinod     20-Oct-09     21-Oct-09     Comments4


      The above table is the user leave calendar.

      Now I need to display the users who are on leave between 01-Nov-2009 to 30-Nov-2009

      The output should look like:


      USER_NAME     FROM_DATE     COMMENTS
      ---------------------------------------------------------------------------------
      Uma Shankar     2-Nov-09     Comment1
      Uma Shankar     3-Nov-09     Comment1
      Uma Shankar     4-Nov-09     Comment1
      Uma Shankar     5-Nov-09     Comment1
      Veera     11-Nov-09     Comment2
      Veera     12-Nov-09     Comment2
      Veera     13-Nov-09     Comment2

      For this I have tried with following query , but it is taking too long time to execute.
      select FROM_DATE,user_name,comments from (SELECT distinct FROM_DATE,user_name ,
      comments FROM (SELECT (LEVEL) + FROM_DATE-1 FROM_DATE,TO_DATE, FIRST_NAME||' '|| LAST_NAME
      user_name ,COMMENTS FROM J_USER_CALENDAR
      where
      and J_USER_CALENDAR.IS_DELETED=0
      CONNECT BY LEVEL <= TO_DATE - FROM_DATE+1) a )where (FROM_DATE = '01-Nov-2009' or FROM_DATE = '30-Nov-2009'
      or FROM_DATE between '01-Nov-2009' and '30-Nov-2009') order by from_Date ,lower(user_name)

      Please help me.

      Thanks in advance.

      Regards,
      Phanikanth
        • 1. Re: Performance issue with connect by level query
          Solomon Yakobson
          Test this:
          with t as (
                     select 'Uma Shankar' user_name,DATE '2009-11-2' from_date,DATE '2009-11-5' to_date,'Comment1' comments from dual union all
                     select 'Veera',DATE '2009-11-11',DATE '2009-11-13','Comment2' from dual union all
                     select 'Uma Shankar',DATE '2009-12-15',DATE '2009-12-17','Commnet3' from dual union all
                     select 'Vinod',DATE '2009-10-20',DATE '2009-10-21','Comments4' from dual
                    )
          select  user_name,
                  from_date + column_value from_date,
                  comments
            from  t,
                  table(
                        cast(
                             multiset(
                                      select level
                                        from  dual
                                        connect by level <= to_date - from_date
                                     )
                             as sys.ODciNumberList
                            )
                       )
          /
          
          USER_NAME   FROM_DATE COMMENTS
          ----------- --------- ---------
          Uma Shankar 03-NOV-09 Comment1
          Uma Shankar 04-NOV-09 Comment1
          Uma Shankar 05-NOV-09 Comment1
          Veera       12-NOV-09 Comment2
          Veera       13-NOV-09 Comment2
          Uma Shankar 16-DEC-09 Commnet3
          Uma Shankar 17-DEC-09 Commnet3
          Vinod       21-OCT-09 Comments4
          
          8 rows selected.
          
          SQL>  
          SY.
          • 2. Re: Performance issue with connect by level query
            Frank Kulash
            Hi, Phanikanth,

            When you use CONNECT BY to generate a sequence of numbers, always use a table that has only one row, such as dual.

            [This thread|http://forums.oracle.com/forums/thread.jspa?messageID=3866008] is a problem similar to yours It shows how to join the results of a CONNECT BY query on dual with your own table to get the results you need.

            If you's like help applying this to your own probelm, post CREATE TABLE and INSERT statements for your sample data. It's a good idea to always post your sample data like this.
            • 3. Re: Performance issue with connect by level query
              Charles Hooper
              I have not attempted to analyze your SQL statement.

              Here is a test set up:
              CREATE TABLE T1(
                USERNAME VARCHAR2(30),
                FROM_DATE DATE,
                TO_DATE DATE,
                COMMENTS VARCHAR2(100));
              
              INSERT INTO T1 VALUES ('Uma Shankar', '02-Nov-09','05-Nov-09','Comment1');
              INSERT INTO T1 VALUES ('Veera','11-Nov-09','13-Nov-09','Comment2');
              INSERT INTO T1 VALUES ('Uma Shankar','15-Dec-09','17-Dec-09','Commnet3');
              INSERT INTO T1 VALUES ('Vinod','20-Oct-09','21-Oct-09','Comments4');
              INSERT INTO T1 VALUES ('Mo','20-Oct-09','05-NOV-09','Comments4');
              
              COMMIT;
              Note that I included one additional row, where the person starts their vacation in the previous month and ends in the month of November.

              You could approach the problem like this:
              Assume that you would like to list all of the days of a particular month:
              SELECT
                TO_DATE('01-NOV-2009','DD-MON-YYYY')+(ROWNUM-1) MONTH_DAY
              FROM
                DUAL
              CONNECT BY
                LEVEL<=ADD_MONTHS(TO_DATE('01-NOV-2009','DD-MON-YYYY'),1)-TO_DATE('01-NOV-2009','DD-MON-YYYY');
              Note that the above attempts to calculate the number of days in the month of November - if it is known that the month has a particular number of days, 30 for instance, you could rewrite the CONNECT BY clause like this:
              CONNECT BY
                LEVEL<=30
              Now, we need to pick up those rows of interest from the table:
              SELECT
                *
              FROM
                T1 T
              WHERE
                (T.FROM_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY')
                  OR T.TO_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY'));
              
              USERNAME        FROM_DATE TO_DATE   COMMENTS
              --------------- --------- --------- ----------
              Uma Shankar     02-NOV-09 05-NOV-09 Comment1
              Veera           11-NOV-09 13-NOV-09 Comment2
              Mo              20-OCT-09 05-NOV-09 Comments4
              If we then join the two resultsets, we have the following query:
              SELECT
                *
              FROM
                T1 T,
                (SELECT
                  TO_DATE('01-NOV-2009','DD-MON-YYYY')+(ROWNUM-1) MONTH_DAY
                FROM
                  DUAL
                CONNECT BY
                  LEVEL<=ADD_MONTHS(TO_DATE('01-NOV-2009','DD-MON-YYYY'),1)-TO_DATE('01-NOV-2009','DD-MON-YYYY')) V
              WHERE
                (T.FROM_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY')
                  OR T.TO_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY'))
                AND V.MONTH_DAY BETWEEN T.FROM_DATE AND T.TO_DATE
              ORDER BY
                USERNAME,
                MONTH_DAY;
              
              USERNAME        FROM_DATE TO_DATE   COMMENTS   MONTH_DAY
              --------------- --------- --------- ---------- ---------
              Mo              20-OCT-09 05-NOV-09 Comments4  01-NOV-09
              Mo              20-OCT-09 05-NOV-09 Comments4  02-NOV-09
              Mo              20-OCT-09 05-NOV-09 Comments4  03-NOV-09
              Mo              20-OCT-09 05-NOV-09 Comments4  04-NOV-09
              Mo              20-OCT-09 05-NOV-09 Comments4  05-NOV-09
              Uma Shankar     02-NOV-09 05-NOV-09 Comment1   02-NOV-09
              Uma Shankar     02-NOV-09 05-NOV-09 Comment1   03-NOV-09
              Uma Shankar     02-NOV-09 05-NOV-09 Comment1   04-NOV-09
              Uma Shankar     02-NOV-09 05-NOV-09 Comment1   05-NOV-09
              Veera           11-NOV-09 13-NOV-09 Comment2   11-NOV-09
              Veera           11-NOV-09 13-NOV-09 Comment2   12-NOV-09
              Veera           11-NOV-09 13-NOV-09 Comment2   13-NOV-09
              Charles Hooper
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              • 4. Re: Performance issue with connect by level query
                726157
                Hi Charles Hooper,

                Thank u very much. This works fine for me.

                Phanikanth