I'm just looking for the most efficient way of doing this.
Im creating a summary view from a table. i.e.
create view sum_sal as
select mgr, sum(sal) sal from emp
group by mgr
However now I want to filter the summary data based on the hiredate between 02/APR/81 and 28/SEP/81
I can't expose hiredate in the view because of the grouping, so at the moment I am just re-joining the table with something like
select s.mgr, s.sal from sum_sal s
join ( select distinct(mgr) mgr
where hiredate between to_date('02/APR/1981','DD/MON/YYYY') and to_date('28/SEP/1981','DD/MON/YYYY')
on s.mgr = e.mgr
On my table with 50,000 rows, its taking 15 seconds to run. I have an index on the ID (the equivalent of MGR here.). I also have an index on the date. However, its still doing two full table scans of the main table.
So, is there a more efficient way of executing this query?