 |
|
How to select rows for a specific month
Oracle Tips by Burleson Consulting
January 8, 2008
|
Question: How do I select rows based on a
specific year and month? I tried this to count the number of rows for a
specific month and it failed:
select count(*)
from mytab
where
TRUNC(enddate,'Mon-YYYY')=TO_DATE('Dec-2007','mm-yyyy') ;
What is the best way to select rows for a specific month?
Answer: First, remember that using a built-in
function on the left-hand side of a where clause predicate will bypass any index
and do a full-table scan:
select
count(*)
from
mytab
where
to_char(enddate,'Mon-YYYY')='Jan-2007';
This solution removes the left-hand BIF:
SELECT
COUNT(*)
FROM
mytab
WHERE
enddate>=date '2007-12-01' and enddate<date '2008-01-01';
Another option to remove the full-table scan and simplify
syntax is to build a
function-based index (FBI) on the date columns:
create index
fbi_mon
on
mytab
(to_char(enddate, 'Mon-YYYY')):
select
count(*)
from
mytab
where
to_char(enddate,'Mon-YYYY')='Jan-2007';
This will get your result using the index, with less I/O.
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
|
