| How to Improve Oracle View Performance [message #178289] |
Tue, 20 June 2006 08:50  |
TheStupid
Messages: 3
Registered: June 2006
|
Junior Member |
|
|
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 value out of these views.
I am wondering if there is anyway to improve the speed? It's in Oracle 8.1.6
-------- View 1 -------
SELECT job_outputs.company_code,
job_outputs.factory,
job_outputs.job_number,
job_outputs.process_stage,
job_outputs.process_spec,
job_outputs.warehouse,
job_outputs.part_code,
job_outputs.output_unit,
job_outputs.temp_count_unit,
jobs.description
FROM jobs,
job_outputs
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 -------
SELECT jobs.company_code,
jobs.factory,
jobs.job_number,
jobs.description,
job_stage.process_stage,
jobs.std_process_spec,
job_stage_lines.source_type,
job_stage_lines.pm_close_line
FROM jobs,
job_stage,
job_stage_lines
WHERE Rtrim(jobs.status_flag,
' ') = '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
[Updated on: Tue, 20 June 2006 08:54] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How to Improve Oracle View Performance [message #178296 is a reply to message #178295] |
Tue, 20 June 2006 09:14  |
TheStupid
Messages: 3
Registered: June 2006
|
Junior Member |
|
|
Mahesh, thank you so much for your input. I don't really know much about Oracle.
When you say "views can make use of indexes on the base tables", how do I construct the indexes on the base tables then? Would you be able to do that for the examples I posted above? That's where I got stuck
I don't know what "statistics" or "execution plan" is, I guess I'll find out.
| Mahesh Rajendran wrote on Tue, 20 June 2006 09:11 |
Views can make use of Indexes on the base tables.
Did you collect the statistics lately?
Post your execution plan.
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
