Home » SQL & PL/SQL » SQL & PL/SQL » SQL help
SQL help [message #350073] Wed, 24 September 2008 00:45 Go to next message
basmraj
Messages: 8
Registered: September 2008
Junior Member
Hi,

Select * 
into temporders
from  (select * from orders
       union 
       select * from ordershistory) b 
where updateon= (select max(updateon)
                from (select updateon,name,units,subunits from orders 
                      union 
                      select updateon,name,units,subunits from ordershistory) a 
                where updateon <='11/08/2008 11:18 AM' 
                and a.name=b.name 
                and a.units=b.units 
                and a.subunits=b.subunits  
                group by name,units,subunits)
order by report,subunitsorder



This is my query which runs properly. but some how i need to improve the performance of this query (say from 3 secs to 1 secs).
So please anyone give me an alternate query to improve performance.


thanks in advance Smile

[ Modified by Rajuvan ; Added tags ; Formated ]

[Updated on: Wed, 24 September 2008 00:56] by Moderator

Report message to a moderator

Re: SQL help [message #350074 is a reply to message #350073] Wed, 24 September 2008 01:01 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
Hi,

Use Analytical query
like

rank() over (partition by name,units,subunits order by updateon desc) rnk

and then use condition rnk = 1

This should avoid querying orders table twice

Idris
Re: SQL help [message #350075 is a reply to message #350073] Wed, 24 September 2008 01:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

First level suggestions

1. Ensure that indices ( index) are proper .
2. Always use date fields with date format
('11/08/2008 11:18 AM' is Not date but Character )


NB: Please format your question properly using format tag for readbility.

Thumbs Up
Rajuvan.

[Updated on: Wed, 24 September 2008 01:04]

Report message to a moderator

Re: SQL help [message #350078 is a reply to message #350073] Wed, 24 September 2008 01:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


I dont think Analytical query will improve performance here .

Thumbs Up
Rajuvan.
Re: SQL help [message #350242 is a reply to message #350073] Wed, 24 September 2008 08:27 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
This one could be a little faster.

select *
from  (
		select * 
		from orders
		union 
		select * 
		from ordershistory
	) join (
		select name,units,subunits,max(updateon) as updateon
		from (
				select name,units,subunits
				select updateon,name,units,subunits 
				from orders 
				union all
				select updateon,name,units,subunits 
				from ordershistory
			)
		group by name,units,subunits
	) using (name,units,subunits,updateon)


And remember that if you're sure values involved in union are distinct you could use union all to get faster result.

Bye Alessandro

[Updated on: Wed, 24 September 2008 08:35]

Report message to a moderator

Re: SQL help [message #350244 is a reply to message #350073] Wed, 24 September 2008 08:28 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Change UNION to UNION ALL.
Previous Topic: ORA-00001: unique constraint
Next Topic: help required in sql query
Goto Forum:
  


Current Time: Sun Dec 11 02:40:06 CST 2016

Total time taken to generate the page: 0.14895 seconds