You may be able to use a variation of the following technique - which forces repeated 'MIN/MAX' range scans:
Assumptions
- You can produce a list of all possible year/month combinations
number
is not null (which it can't be as it is in the PK, but I mention it as there is a way of working around if nulls are permitted)
testbed:
create table foo(month, year, num, primary key(month, year, num)) as
with m as ( select extract(month from d) as month, extract(year from d) as year
from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, num
from m cross join
(select level as num from dual connect by level<100000 order by dbms_random.random());
normal query:
select distinct month, year from foo;
--gets=11656
min/max technique:
with m as ( select extract(month from d) as month, extract(year from d) as year
from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, decode(( select min(num)
from foo
where month=m.month and year=m.year )
,null, 'N', 'Y') as has_data_yn
from m;
--gets=294
Some explanation in response to comments:
In each case (the testbed and the min/max query), the subquery factoring clause just generated a list of (year, month) tuples:
with m as ( select extract(month from d) as month, extract(year from d) as year
from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select * from m;
/*
MONTH YEAR
---------------------- ----------------------
1 2012
12 2011
11 2011
10 2011
...
...
*/
Then the technique uses a subquery in the select
clause to check if any rows are present for the (month, year) - this subquery necessarily must only produce at most 1 row:
select min(num)
from foo
where month=m.month and year=m.year;
This is very quick because it makes use of the ordered nature of the PK - however it needs to be executed once for each month - if there are millions of rows for each month that makes sense, but not if there are few enough to fit in a small number of block.
Best Answer
There are different ways to achieve the goal.
OR
Regarding the second one, what happens if the SYSDATE or the current date supplied happens to be a leap day?
What happens if we use the first method?