For example, I have to create a view for a particular employee which stores the attendance information for the last 50 days and I have over a milion employees.
I will be giving the employee id as the input and I want this view to be created only for this employee with the last 50 days of his attendance details.
A simplistic explanation will be much appreciated.
Thank you.
A view is just a stored query. Therefore, if you always need only the last 50 days, then
create or replace view v_50 as
select empno, ename, date_in, date_out
from attendance
where date_in >= trunc(sysdate) - 50
Then fetch data for any employee you need:
select *
from v_50
where empno = 1234;
On the other hand, you don't need a view for this at all - do the same from the original table:
select empno, ename, date_in, date_out
from attendance
where empno = 1234
and date_in >= trunc(sysdate) - 50
Make sure that both empno and date_in are indexed, regularly gather statistics.
To retrieve data for specific month, one option is to
select ...
from attendance
where to_char(date_in, 'yyyymm') = '202009'
A function-based index would help; otherwise, you'd have to use something like
where date_in between date '2020-09-01' and date '2020-03-30'
if date_in is indexed, as to_char-ing it wouldn't be able to use such an index.
