Home » RDBMS Server » Performance Tuning » View Performance Tuning
View Performance Tuning [message #667994] Tue, 30 January 2018 03:45 Go to next message
dancko
Messages: 95
Registered: June 2013
Location: italy
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
dancko
Messages: 95
Registered: June 2013
Location: italy
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
dancko
Messages: 95
Registered: June 2013
Location: italy
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #668004 is a reply to message #668002] Tue, 30 January 2018 09:40 Go to previous messageGo to next message
dancko
Messages: 95
Registered: June 2013
Location: italy
Member
"Is that 10 seconds for extracting all the records, or just the first batch as displayed on GUI?"

I have not checked truthfully. But I think more probably just the first batch displayed on a GUI.
Re: View Performance Tuning [message #668008 is a reply to message #668004] Tue, 30 January 2018 10:34 Go to previous message
cookiemonster
Messages: 13894
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.
Previous Topic: Is there a equivalent of delete nologging operations
Next Topic: Move unused partitioned from one tablespace to another tablespace.
Goto Forum:
  


Current Time: Sun Jun 07 01:03:54 CDT 2020