Forum Stats

  • 3,769,605 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

Conversion of number to date affect the performance in oracle

user12251389
user12251389 Member Posts: 322 Blue Ribbon
edited Sep 12, 2017 11:09AM in SQL & PL/SQL

I have below query where the DT_ID is number data type column and the value stored in this column is date in the format 'YYYYMMDD'.

In my below query i want to get the previous week data from Monday till Sunday for weekly reporting and this query works very fast.

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">NAME </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> TEST_REPORT </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> DT_ID </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">between</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">20170904</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">20170910</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span>

But this is hardcoded date and i want to make it dynamic. So I tried below query which is resulting error as expecting Number and got date

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">NAME </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> TEST_REPORT </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">>=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">14</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span>

When i convert this number to date like below query its resulting a lot of performance issue. Is there any other way i can make it dynamic with better performance ?

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">NAME </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> TEST_REPORT </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> to_date</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'YYYYMMDD'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">>=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">14</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'YYYYMMDD'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span>
user12251389

Best Answer

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Sep 12, 2017 9:32AM Accepted Answer

    Do not use DT_ID in a function. Use something like:

    select  ID,NAME

    from TEST_REPORT

    where DT_ID>= TO_NUMBER(TO_CHAR(next_day(trunc(sysdate), 'MONDAY') - 14,'YYYYMMDD'))

      and DT_ID< TO_NUMBER(TO_CHAR(next_day(trunc(sysdate), 'MONDAY') - 7,'YYYYMMDD')); 

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,074 Employee
    edited Sep 12, 2017 9:16AM

    Wrong forum, try the space.

    However.

    I have below query where the DT_ID is number data type column and the value stored in this column is date in the format 'YYYYMMDD'.

    That's your problem. Change the data type to DATE. Always store DATEs as DATE.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Sep 12, 2017 9:27AM
    user12251389 wrote:I have below query where the DT_ID is number data type column and the value stored in this column is date in the format 'YYYYMMDD'.In my below query i want to get the previous week data from Monday till Sunday for weekly reporting and this query works very fast.
    1. selectID,NAMEfromTEST_REPORTwhereDT_IDbetween20170904and20170910;
    select ID,NAME from TEST_REPORT where DT_ID between 20170904 and 20170910;

    But this is hardcoded date and i want to make it dynamic. So I tried below query which is resulting error as expecting Number and got date

    1. selectID,NAMEfromTEST_REPORTwhereDT_ID>=next_day(trunc(sysdate),'MONDAY')-14andDT_ID<next_day(trunc(sysdate),'MONDAY')-7;
    select ID,NAME from TEST_REPORT where DT_ID>= next_day(trunc(sysdate), 'MONDAY') - 14 and DT_ID< next_day(trunc(sysdate), 'MONDAY') - 7;

    When i convert this number to date like below query its resulting a lot of performance issue. Is there any other way i can make it dynamic with better performance ?

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">NAME </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> TEST_REPORT </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> to_date</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'YYYYMMDD'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">>=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">14</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'YYYYMMDD'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span>

    use  of FUNCTION in WHERE clause may prevent use of INDEX & result in Full Table Scan for every row.

  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Sep 12, 2017 9:32AM Accepted Answer

    Do not use DT_ID in a function. Use something like:

    select  ID,NAME

    from TEST_REPORT

    where DT_ID>= TO_NUMBER(TO_CHAR(next_day(trunc(sysdate), 'MONDAY') - 14,'YYYYMMDD'))

      and DT_ID< TO_NUMBER(TO_CHAR(next_day(trunc(sysdate), 'MONDAY') - 7,'YYYYMMDD')); 

  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    edited Sep 12, 2017 9:33AM

    Try

    <span class="kwd" style="color: #101094; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">select</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">  ID</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">,</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">NAME</span>

    <span class="kwd" style="color: #101094; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">from</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> TEST_REPORT</span>

    <span class="kwd" style="color: #101094; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">where</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> </span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">DT_ID</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> </span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">>=</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> to_number(to_char(next_day</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">(</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">trunc</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">(</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">sysdate</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">),</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> </span><span class="str" style="color: #7d2727; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">'MONDAY'</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">)</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> </span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">-</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> </span><span class="lit" style="color: #7d2727; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">14,'YYYYMMDD'))</span>

    <span class="kwd" style="color: #101094; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">and</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">   </span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">DT_ID</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> </span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"><</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> to_number(to_char(next_day</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">(</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">trunc</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">(</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">sysdate</span><span class="pun" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">),</span><span class="pln" style="color: #303336; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;"> </span><span class="str" style="color: #7d2727; font-family: inherit; font-size: inherit; font-style: inherit; font-weight: inherit;">'MONDAY') - 7, 'YYYYMMDD'))</span>

    Then you will still use any index on DT_ID and you will be comparing against numbers rather than dates

    Ideally though you should be storing your date information in a DATE datatype column rather than numeric, and that would make your life so much easier.

    See the community document: https://community.oracle.com/docs/DOC-991195

    user12251389
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,226 Red Diamond
    edited Sep 12, 2017 9:43AM

    Hi,

    user12251389 wrote:I have below query where the DT_ID is number data type column and the value stored in this column is date in the format 'YYYYMMDD'.In my below query i want to get the previous week data from Monday till Sunday for weekly reporting and this query works very fast.
    1. selectID,NAMEfromTEST_REPORTwhereDT_IDbetween20170904and20170910;
    select ID,NAME from TEST_REPORT where DT_ID between 20170904 and 20170910;

    But this is hardcoded date and i want to make it dynamic. So I tried below query which is resulting error as expecting Number and got date

    1. selectID,NAMEfromTEST_REPORTwhereDT_ID>=next_day(trunc(sysdate),'MONDAY')-14andDT_ID<next_day(trunc(sysdate),'MONDAY')-7;
    select ID,NAME from TEST_REPORT where DT_ID>= next_day(trunc(sysdate), 'MONDAY') - 14 and DT_ID< next_day(trunc(sysdate), 'MONDAY') - 7;

    When i convert this number to date like below query its resulting a lot of performance issue. Is there any other way i can make it dynamic with better performance ?

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">NAME </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> TEST_REPORT </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> to_date</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'YYYYMMDD'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">>=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">14</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">DT_ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'YYYYMMDD'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> next_day</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">trunc</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">sysdate</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'MONDAY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span>

    If you want fast, simple, robust queries, then use the DATE datatype for DATE information.

    Also, if dt_id is a NUMBER, don't try to compare it to a DATE, like the value returned by NEXT_DAY.

    If you're stuck with date information in a NUMBER column, then you can do things like this:

    WHERE   dt_id  >= TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE                                              , 'IW'                                              ) - 14                                      , 'YYYYMMDD'                                      )                            ) AND     dt_id  <  TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE                                              , 'IW'                                              ) - 7                                      , 'YYYYMMDD'                                      )                            ) 

    If you'd care to post a little sample data, tnem I could test this.

    TRUNC (dt, 'IW')    is the Monday on or before the DATE dt.  This is better than NEXT_DAY, because it doesn't depend on your NLS settings.

    To compare a NUMBER on each row to to a couple of DATEs, converting those 2 DATEs to NUMBERs will be more efficient than converting all those NUMBERs to DATEs.  Also, it will allow you to use an index on the NUMBER column, and it will not raise an error when you (inevitably) have invalid  values in the NUMBER column.

    user12251389user12251389
  • mathguy
    mathguy Member Posts: 10,156 Blue Diamond
    edited Sep 12, 2017 9:59AM

    As several responders suggested already:

    If you have an index on DT_ID (which you may, if your original query runs very fast), then you should take advantage of it. If you wrap DT_ID within some function to make it a date, you can't use the index anymore. So it would be better to convert the comparison dates to NUMBER. Replies 3 and 4 show how to do that.

    As an aside, you don't need to apply TRUNC to SYSDATE, since in the end you apply TO_CHAR with the 'YYYYMMDD' format model, which truncates the date anyway. TRUNC is just two more unnecessary function calls for each row in your table.

    You may find that the resulting query is still not very fast (even if the original query was fast). There is a serious performance problem with storing dates in NUMBER format, even if you have an index on the column. It has to do with how the optimizer uses statistics. Tom Kyte gave a talk, long time ago, about this kind of issue (and why it is so important that data types be used correctly). If your dates are from 20161201 to 20170331, that's 123 days. Let's say your rows are uniformly distributed over these dates. Then you want a report for just Dec. 31 and Jan. 1, that's two days out of 123. The selectivity is about 1.7%, so if an index exists, the optimizer may well choose to use it. But if the data is in NUMBER data type, then there are 9131 values between 20161201 and 20170331, and you are selecting 8871 of them (from 20161231 to 20170101), meaning you are selecting over 97% of the rows. YOU know that is not the case, but how would the optimizer know that? The result will be inevitable: the optimizer will choose a full table scan, resulting in much slower execution.

    There are a few ways to address that. You could add histograms to let the optimizer know that the data is not distributed uniformly over NUMBERS (even though it IS distributed uniformly over DATES) - there are NUMBER intervals with zero rows in those intervals (obviously: the numbers that are not valid dates). Gathering the statistics to create the histograms is extra work (and writing them correctly may be a pain in the neck), but the ability to use the index correctly may be worth the effort. Or, you could create a function-based index on TO_DATE(DT_ID, 'YYYYMMDD'). Or, best of all, you could change the data type of all your dates to DATE.

    user12251389
  • user12251389
    user12251389 Member Posts: 322 Blue Ribbon
    edited Sep 12, 2017 10:42AM

    Thanks all of you it really helps

  • user12251389
    user12251389 Member Posts: 322 Blue Ribbon
    edited Sep 12, 2017 10:58AM

    comparing the TRUNC (dt, 'IW') doesnt gave me the records from last week i.e for previous week Monday till Sunday. I tried your logic

    WHERE   dt_id  >= TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE

                                                  , 'IW'

                                                  ) - 14

                                          , 'YYYYMMDD'

                                          )

                                )

    AND     dt_id  <  TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE

                                                  , 'IW'

                                                  ) - 7

                                          , 'YYYYMMDD'

                                          )

                                )

  • mathguy
    mathguy Member Posts: 10,156 Blue Diamond
    edited Sep 12, 2017 11:01AM

    Frank missed that part when he converted from NEXT_DAY to TRUNC(..., 'IW')

    Using NEXT_DAY moves you FORWARD, so that is why you were subtracting 14 and 7 - to get you to last week, starting from next week.

    With TRUNC(...., 'IW')  you are staying within the current week, so to move back one week you need to subtract 7 and 0 (meaning, not subtract at all in the second condition), instead of subtracting 14 and 7.

    The fact that you weren't able to answer the question yourself suggests that you are not 100% clear, in your mind, on how NEXT_DAY and TRUNC work. Spend a little bit of time to understand what the code says - you should be able to look at the code and explain, in common language, what it does, to an intelligent person who doesn't know anything about Oracle and SQL. Then you will be able to confidently write and maintain such code!

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,226 Red Diamond
    edited Sep 12, 2017 11:09AM

    Hi,

    user12251389 wrote:comparing the TRUNC (dt, 'IW') doesnt gave me the records from last week i.r from Monday till Sunday. I tried your logic ...

    Right.  You never posted any sample data, or the results you wanted from that data, so I couldn't test if that would get the results you wanted from your data or not.  That's why I said "you can do things like this", and not "this is exactly what you want".

    The conditions in reply #5 DO look for a week, starting on a Monday and going through the following Sunday, but that may not be the week you want.  If you want a different week, then adjust the constant offsets (given as 14 and 7 in reply #5) accordingly.

This discussion has been closed.