12 Replies Latest reply: Mar 7, 2013 4:23 AM by Rahul_India RSS

    Retrieving Periodical data

    sunitha2010
      Hello ALL,

      I have a requirement like to retrive past 6 months data and i have used below query.
      select count(1) from event where trunc(start_datae) between trunc(sydate)-180 and trunc(sysdate);
      it is giving the results but am not sure whether it is giving correct data or not.If ran the above query it is taking more time to execute.


      Is the above apporach is correct?

      Is there any differece between
      trunc(sysdate)-180 
      and
       trunc(sysdate-180) 
      ?


      Please suggest me.

      Thanks a lot for your help.
        • 1. Re: Retrieving Periodical data
          Rahul_India
          >
          Is there any differece between
          trunc(sysdate)-180 
          and
           trunc(sysdate-180) 
          ?


          Past 6 months data does not mean 180 days..Some months have 30 days,some 31 and Feb has 29 days.



          NO,there is no difference
          select  trunc(sysdate) -180 from dual;
          op:-
          08-SEP-12
          select  trunc(sysdate-180)  from dual
          
          op:-
          08-SEP-12
          to get data of past 6 months
          Use this
          select count(1) from event where trunc(start_date)
           between ADD_MONTHS(trunc(sysdate),-6) and sysdate
          Edited by: Rahul India on Mar 7, 2013 3:01 PM
          • 2. Re: Retrieving Periodical data
            Manik
            Use add_months(dtfield,-6) instead.

            Cheers,
            Manik.
            • 3. Re: Retrieving Periodical data
              Paul  Horth
              If you really want 6 months ago (which may not be 180 days) use
              add_months(trunc(sysdate),-6)
              • 4. Re: Retrieving Periodical data
                Karthick_Arp
                sunitha2010 wrote:
                Hello ALL,

                I have a requirement like to retrive past 6 months data and i have used below query.
                 
                select count(1) from event where trunc(start_datae) between trunc(sydate)-180 and trunc(sysdate); 
                There are few things to consider.

                1. 180 days is not equal to 6 months. A month may contain 28, 29, 30, 31 days. So 6 month will definitely not sum up to 180 days.

                2. You have used TRUNC function on START_DATE. By doing so you have made any index on START_DATE unusable.

                The proper way would be
                 
                select count(*) 
                  from event 
                where start_date between trunc(add_months(sysdate, -6)) and sysdate; 
                • 5. Re: Retrieving Periodical data
                  Rahul_India
                  >

                  >
                  2. You have used TRUNC function on START_DATE. By doing so you have made any index on START_DATE unusable.
                  Karthich how the index on start_date is made unusable?
                  • 6. Re: Retrieving Periodical data
                    sunitha2010
                    Thanks a lot for your quick response. i wil try with your queries.

                    can i use below query for same operation?
                      SELECT count(*)
                      FROM event
                      WHERE MONTHS_BETWEEN( SYSDATE, START_DATE_TIME )<=6 
                    Is tha above query correct?

                    Plesae suggest.

                    Thank for your help.
                    • 7. Re: Retrieving Periodical data
                      Rahul_India
                      Yes its correct
                      • 8. Re: Retrieving Periodical data
                        Karthick_Arp
                        Rahul India wrote:
                        >
                        2. You have used TRUNC function on START_DATE. By doing so you have made any index on START_DATE unusable.
                        Karthich how the index on start_date is made unusable?
                        Index is nothing but a skinny version of the table. That is it stores the column data in a specific structure (BTree for example).

                        When you apply a function like TRUNC on a column you change the data, and hence there is no point in looking into the index.

                        consider this test case
                        SQL> create table t
                          2  as
                          3  select sysdate+ level dt, 'xx' val 
                          4    from dual
                          5  connect by level <= 10000;
                         
                        Table created.
                         
                        SQL> create index t_idx on t(dt);
                         
                        Index created.
                         
                        SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade=>true)
                         
                        PL/SQL procedure successfully completed.
                        I want to get row from tomorrow where dt is tomorrow (time part can varie).

                        First using TRUNC, You can see Index is not used
                        SQL> select *
                          2    from t
                          3   where trunc(dt) = trunc(sysdate+1);
                         
                        DT        VA
                        --------- --
                        08-MAR-13 xx
                         
                        SQL> select * from table(dbms_xplan.display_cursor);
                         
                        PLAN_TABLE_OUTPUT
                        --------------------------------------------------------------------------
                        SQL_ID  82a8d4d4fpm3g, child number 0
                        -------------------------------------
                        select *   from t  where trunc(dt) = trunc(sysdate+1)
                         
                        Plan hash value: 2153619298
                         
                        --------------------------------------------------------------------------
                        | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                        --------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT  |      |       |       |    12 (100)|          |
                        |*  1 |  TABLE ACCESS FULL| T    |   100 |  1100 |    12  (34)| 00:00:01 |
                        --------------------------------------------------------------------------
                         
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                         
                           1 - filter(TRUNC(INTERNAL_FUNCTION("DT"))=TRUNC(SYSDATE@!+1))
                         
                         
                        18 rows selected.
                        Now without using any function on DT column
                        SQL> select *
                          2    from t
                          3   where dt between trunc(sysdate+1) and trunc(sysdate+2) - interval '1' second;
                         
                        DT        VA
                        --------- --
                        08-MAR-13 xx
                         
                        SQL> select * from table(dbms_xplan.display_cursor);
                         
                        PLAN_TABLE_OUTPUT
                        ---------------------------------------------------------------------------------------
                        SQL_ID  0m4knj1jsabx6, child number 0
                        -------------------------------------
                        select *   from t  where dt between trunc(sysdate+1) and trunc(sysdate+2) -
                        interval '1' second
                         
                        Plan hash value: 258909497
                         
                        --------------------------------------------------------------------------------------
                        | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                        --------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT             |       |       |       |     1 (100)|          |
                        |*  1 |  FILTER                      |       |       |       |            |          |
                        |   2 |   TABLE ACCESS BY INDEX ROWID| T     |     2 |    22 |     1   (0)| 00:00:01 |
                        |*  3 |    INDEX RANGE SCAN          | T_IDX |     2 |       |     1   (0)| 00:00:01 |
                        --------------------------------------------------------------------------------------
                         
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                         
                           1 - filter(TRUNC(SYSDATE@!+1)<=TRUNC(SYSDATE@!+2)-INTERVAL'+00
                                      00:00:01.000000' DAY(2) TO SECOND(6))
                           3 - access("DT">=TRUNC(SYSDATE@!+1) AND
                                      "DT"<=TRUNC(SYSDATE@!+2)-INTERVAL'+00 00:00:01.000000' DAY(2) TO SECOND(6))
                         
                         
                        24 rows selected.
                         
                        SQL> 
                        • 9. Re: Retrieving Periodical data
                          Rahul_India
                          Thanks.
                          SO we should try to avoid any function that change the data right?
                          • 10. Re: Retrieving Periodical data
                            APC
                            Rahul India wrote:
                            >
                            2. You have used TRUNC function on START_DATE. By doing so you have made any index on START_DATE unusable.
                            Karthich how the index on start_date is made unusable?
                            Because the index indexes values of START_DATE including the time element. The optimizer knows that applying TRUNC() means the values in the operand won't match the values in the index, and so discards the index when considering access paths.

                            This is a general rule it would apply to any modification of an indexed column. In the days before hints we used this trick to force a full table scan:
                            select * 
                            from whatever
                            where some_number+0 = :X
                            Remember, we have function-based indexes - and these days indexes on virtual coulmns - which allow us to use indexes even when applying functions to columns.

                            Cheers, APC
                            • 11. Re: Retrieving Periodical data
                              Paul  Horth
                              sunitha2010 wrote:
                              Thanks a lot for your quick response. i wil try with your queries.

                              can i use below query for same operation?
                              SELECT count(*)
                              FROM event
                              WHERE MONTHS_BETWEEN( SYSDATE, START_DATE_TIME )<=6 
                              Is tha above query correct?

                              Plesae suggest.

                              Thank for your help.
                              Yes and no. It probably gives the answer you want but

                              1. If you have an index on start_date_time, it can't use it.
                              2. If the table is partitioned by start_date_time, it won't partition eliminate.

                              In both cases, the query will be a lot slower.

                              Use the suggestion from Karthick
                              select count(*) 
                                from event 
                              where start_date between trunc(add_months(sysdate, -6)) and sysdate;
                              • 12. Re: Retrieving Periodical data
                                Rahul_India
                                can u see my thread n answer how indexes are used there
                                Order by