CODEWITHSUNDEEP
sqloracle-database
user1474111
user1474111

Reputation: 1516

inner date query inefficiency

I need to run a query and I need to add date range in the where clause. It should not be hardcoded therefore I used it like below. But it takes quite longer than hardcoded version.

tbl1.date >= ( SELECT 
   trunc( (ADD_MONTHS(SYSDATE, -1)), 'MM')
  from dual) and tbl1.date <= ( SELECT 
   last_day( (ADD_MONTHS(SYSDATE, -1)))
  from dual)

Same query with hardcoded date takes much shorter time

tbl1.date >= '16/05/01' and tbl1.date <= '16/05/31'

So how can I optimize the query which is not hardcoded?

Upvotes: 1

Views: 59

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You don't need to use subqueries:

tbl1.date >= trunc(ADD_MONTHS(SYSDATE, -1), 'MM')
and tbl1.date <= last_day(ADD_MONTHS(SYSDATE, -1))

or (though I don't really like this construct with dates, as it's easy to overlook the time part):

tbl1.date between trunc(ADD_MONTHS(SYSDATE, -1), 'MM')
    and last_day(ADD_MONTHS(SYSDATE, -1))

Or if your date column has values which are not midnight:

tbl1.date >= trunc(ADD_MONTHS(SYSDATE, -1), 'MM')
and tbl1.date < trunc(SYSDATE, 'MM')

Whether that fixes your performance issue is another matter...

Upvotes: 3

Related Questions