Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Creating a Summary view but querying with date where clause

Richard LeggeFeb 9 2024

Hi All

12.1

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

from emp

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?

Many thanks

Richard

Comments
Post Details
Added on Feb 9 2024
4 comments
92 views