Optimizing view to read rows as per usage statistics

Question

There are two modules in the project:

Module 1: patient registration module

patient_admitted table holds information of patient admitted in hospital. patient_id is primary key.

patient_discharged table holds information of patient that got discharged from the hospital.

patient_personal_details view represents patient personal information from patient_admitted table such as name,age,gender etc.
It also reads from patient_discharged table in scenario as below:
A row in patient_admitted table is moved to patient_discharged table once the patient gets discharged, If medical_reports table consists such row then the view retains that row information by reading it from patient_discharged table.

Module 2: Medical test module

medical_reports table holds medical report details of patient along with the personal information. If the patient is currently admitted then it reads the personal information from the patient_personal_details view.

Problem- Optimization needed:

Over the time the view would read bulk records from patient_discharged table.
Is it possible to discard records(row) in patient_personal_details view that haven't been accessed from past 120 days using some access statistics.

If there better way to design such scenario please suggest.

• Justin Cave: That seems like a lot of work. It could probably be done but it would not be very efficient and would be a lot of work to develop. It seems much more likely that you'd want to purge data after some time following the admission date. But it's not even clear that you have a problem-- assuming you are searching by indexed values, the number of rows in the underlying table is not going to have a huge impact on performance.
• Akki: All records in patient_discharge table should be stored permanently and never be purged. I am trying to ignore reading those rows from patient_discharge table into patient_personal_details view if they aren't accessed from a long time...
• Justin Cave: Is that technically possible? Maybe. You could configure fine-grained auditing and have the view scan the audit trail (depending on what "access" means to you). I cannot imagine that would be a performant operation. It would be a whole lot of extra work to filter out rows in the view logic just because they hadn't been read in a while. A view is just a query-- there is 0 cost to having a row be visible in a view. Keeping the row in a table but filtering it out in the view is just going to add cost. It won't make anything faster.

Answer 1

This sounds like you want to build a materialized view that satisfies your data condition. A materialized view (previously called a snapshot) physically stores a copy of the data rather than a standard view which is simply a query on the underlying tables. You should get some benefit when querying as the older records will not be in the view and you can define indexes appropriate specifically to viewing.

There are lots of options and restrictions around refreshing of these views and you will need to do some research to establish the right approach in your situation. https://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm