This discussion is archived
12 Replies Latest reply: Mar 7, 2013 2:23 AM by Rahul_India RSS

Retrieving Periodical data

sunitha2010 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    Use add_months(dtfield,-6) instead.

    Cheers,
    Manik.
  • 3. Re: Retrieving Periodical data
    Paul Horth Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    >

    >
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Yes its correct
  • 8. Re: Retrieving Periodical data
    Karthick_Arp Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Thanks.
    SO we should try to avoid any function that change the data right?
  • 10. Re: Retrieving Periodical data
    APC Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    can u see my thread n answer how indexes are used there
    Order by

Legend

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