| View Performance Tuning [message #667994] |
Tue, 30 January 2018 03:45  |
 |
dancko
Messages: 108
Registered: June 2013
Location: italy
|
Senior Member |
|
|
Hi,
can any one of you help or suggest me how to improve performance and optimize a view?
In particular my view is a union of a set of select statements and has the following form:
create or replace view my_view as
select field1,
field2,
...,
fieldn
from table1,
table2,
...,
tablen
where join condition
union
select field1,
field2,
...,
fieldn
from another_table1,
table2,
...,
tablen
where join condition
union
...
...
union
select field1,
field2,
...,
fieldn
from table1,
another_table2,
...,
tablen
where join condition
Note that the join condition are on key and index field.
How we improve performance further of a such view?
For example can we use HINTS in select statement inside view script?
Can you suggest me how to optimize this view?
Thanks in advance.
dancko
[Updated on: Tue, 30 January 2018 03:58] Report message to a moderator |
|
|
|
| Re: View Performance Tuning [message #667995 is a reply to message #667994] |
Tue, 30 January 2018 04:14  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Please read How to tune SQL or Identify Performance Problem and Bottleneck and supply the requested information
The only general points I'd make for a view in that form are:
1) Does it really need UNION (which does an implicit distinct) or will UNION ALL suffice (which doesn't do the distinct)?
2) Is it possible to combine 2 or more of the existing selects you've unioned into a single select?
For any other suggestions we would need the information detailed in the link above.
|
|
|
|
| Re: View Performance Tuning [message #667997 is a reply to message #667995] |
Tue, 30 January 2018 04:19 |
|
dancko
Messages: 108
Registered: June 2013
Location: italy
|
Senior Member |
|
|
Hi
"Does it really need UNION (which does an implicit distinct) or will UNION ALL suffice (which doesn't do the distinct)?"
All record in a view are distinct among them, so I can use also UNION ALL.
What I ask you is this: is it possible use HINTS on existing select statements in a view script?
thanks
[Updated on: Tue, 30 January 2018 04:20] Report message to a moderator |
|
|
|
| Re: View Performance Tuning [message #667999 is a reply to message #667997] |
Tue, 30 January 2018 04:27 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Probably but I really wouldn't.
At some point you may need to query that view in a way where the hint is problematic.
If you want to use hints put them in the queries against the view, not in the view itself.
Also, I generally regard hints as the last resort, I'd see if you can speed it up without them first.
|
|
|
|
| Re: View Performance Tuning [message #668001 is a reply to message #667999] |
Tue, 30 January 2018 04:57 |
|
dancko
Messages: 108
Registered: June 2013
Location: italy
|
Senior Member |
|
|
Hi
I have replace the UNION operator with the UNION ALL operator and the performance are improved unbelievably!!!
Using the UNION operator the running time of a query Q on a view was more or less four hours extracting 49733 records.
Using the UNION ALL operator the running time of the same query Q on a view was of ten second extracting the same number of records 49733.
As it possible that replacing UNION operator with the UNION ALL the running time is improved of about four hours?!?
Unbelievably!!!
[Updated on: Tue, 30 January 2018 05:00] Report message to a moderator |
|
|
|
| Re: View Performance Tuning [message #668002 is a reply to message #668001] |
Tue, 30 January 2018 06:38 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
4 hours seems somewhat excessive for skipping a sort of ~ 50,000 rows.
Is that 10 seconds for extracting all the records, or just the first batch as displayed on GUI?
|
|
|
|
|
|
| Re: View Performance Tuning [message #668008 is a reply to message #668004] |
Tue, 30 January 2018 10:34 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Then you should probably check - I imagine it'll be a fair bit longer than 10 seconds.
For a lot queries oracle can start returning data to the caller before it finishes finding all the rows that match the where clause.
If you use union, with it's implicit distinct, then it can't. It has to find all the matching rows before it can do the distinct. So it has to finish doing all it's work before you see any results.
UNION ALL will be faster than UNION, but not to the tune of 4 hours. Better see how long it takes now, you may well still have work to do.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
