问题描述:

I am trying to write an SQL statement that would allow me to retrieve all the orders created in the month of September without much luck.

Not working:

select order_number, created_date

from orders

where created_date in to_date('2012-09', 'YYYY-MM');

Working but too long:

select order_number, created_date

from orders

where trunc(created_date) between to_date('2012-09-01', 'YYYY-MM-DD') and to_date('2012-09-30', 'YYYY-MM-DD');

网友答案:

How about:

select order_number, created_date
from orders
where created_date >= to_date('2012-09-01', 'YYYY-MM-DD') 
and created_date < to_date('2012-10-01', 'YYYY-MM-DD');

You should try to leave created_date untouched to make sure that you can leverage indexes on it nicely.

网友答案:

Use the following code.

select order_number, created_date
from orders
where TO_CHAR(created_date, 'YYYY-MM') in '2012-09';
网友答案:

I think your version that uses between will have better performance, but you can always try the other way around:

WHERE TO_CHAR(created_date, 'YYYY-MM') = '2012-09';

Another option would be EXTRACT:

WHERE 
  EXTRACT(year FROM created_date) = 2012 
  AND EXTRACT(month FROM created_date) = 9;

Update:

Function-Based Indexes can be used since Oracle 8i to improve the performance of this kind of query:

CREATE INDEX ORDS_CRTD_DT_YYYY_MM_IDX 
   ON orders (TO_CHAR(created_date, 'YYYY-MM'));

Of course that you should avoid creating indexes for no reason (they will slow down write operations), when there are simpler solutions to your problem - like the one @Rob provided - go with it. Just keep in mind that it is possible to use functions such as TRUNC, TO_CHAR and EXTRACT with a column and still avoid full scans.

相关阅读:
Top