How to Improve Oracle View Performance?
Posted on 2006-06-20
We have an application written by a now defunc company a few years back. It uses a few synonyms which are views from another schema.
The problem is the speed. It takes almost 20 secs to get some values out of these views.
I know views can make use of indexes on the base tables, but how do I construct the indexes on the base tables then? Would anybody be able to do that for the examples I posted below? That's where I got stuck
I am wondering if there is anyway to improve the speed? It's in Oracle 8.1.6
-------- View 1 -------
WHERE job_outputs.company_code = jobs.company_code
AND job_outputs.factory = jobs.factory
AND job_outputs.job_number = jobs.job_number
AND job_outputs.destination_type = 'I'
AND jobs.status_flag = 'R'
-------View 2 -------
' ') = 'R'
AND jobs.company_code = job_stage.company_code
AND jobs.factory = job_stage.factory
AND jobs.job_number = job_stage.job_number
AND jobs.company_code = job_stage_lines.company_code
AND jobs.factory = job_stage_lines.factory
AND jobs.job_number = job_stage_lines.job_number