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:
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:
This solution removes the left-hand BIF:
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:
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
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.