Home » RDBMS Server » Performance Tuning » Which SQL is faster/better (Oracle)
Which SQL is faster/better [message #600323] Mon, 04 November 2013 23:33 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Hi all,

Between below two SQL, which one will be better or faster in regards of query performance?

SQL-1:

select extract (year from period) period
from mytable
union
select null period from sys.dual

SQL-2:

select distinct extract (year from period) period
from mytable

Thanks
Re: Which SQL is faster/better [message #600325 is a reply to message #600323] Mon, 04 November 2013 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Between below two SQL, which one will be better or faster in regards of query performance?

post EXPLAIN PLAN for both statements
Re: Which SQL is faster/better [message #600354 is a reply to message #600325] Tue, 05 November 2013 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Those two sqls don't give the same results, the first always includes a null in the results, the 2nd doesn't, so why are you comparing the performance?
Re: Which SQL is faster/better [message #600356 is a reply to message #600354] Tue, 05 November 2013 03:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Depending on the data in mytable, there will be a difference in the order of NULLs in the result set. In 1st query it will be at last, in 2nd query(if there are any null values) will be ordered on top of result set.

Based on the queries, I think the union query will be a SORT operation, and the second query will be a HASH operation.
Re: Which SQL is faster/better [message #600357 is a reply to message #600356] Tue, 05 November 2013 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
there may be a difference in the order, or there may not.
Order is not guaranteed without an order by
Re: Which SQL is faster/better [message #600358 is a reply to message #600356] Tue, 05 November 2013 05:03 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Thanks all for the inputs.

Yes, UNION query is a SORT operation. And the DISTINCT is a HASH operation.

So, which one will be better if the table contains 3 to 5 millions of records?

I need to find out distinct no of years from the period column of mytable. Finally I will use COUNT to count the distinct no of years. Containing NULL is not a problem for me as I will COUNT the distinct years.
Re: Which SQL is faster/better [message #600365 is a reply to message #600358] Tue, 05 November 2013 07:17 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
mokarem wrote on Tue, 05 November 2013 11:03
Thanks all for the inputs.

Yes, UNION query is a SORT operation. And the DISTINCT is a HASH operation.

So, which one will be better if the table contains 3 to 5 millions of records?


It depends.

Your only answer to this is to benchmark it in a representative environment with runtime conditions.

On "paper" a hash will sound faster to many people and indeed it often is, but under serious concurrent loads I've seen CPU contention in getting the CPU cycles to hash it - we had to force a sort as under concurrent load the hash was killing us, even though it was faster in isolation.

Anyway, I digress - it depends.

Test it and see if you have a problem. Whomever does the least work always wins the performance race.
Re: Which SQL is faster/better [message #600385 is a reply to message #600365] Tue, 05 November 2013 21:37 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle will typically choose a HASH to resolve a DISTINCT, although it is possible for it to use a SORT if the optimizer thinks it will be faster. For example, if the query used a range scan to select values from an index - and the values were in sorted order as a result - then a SORT DISTINCT will be faster than a HASH DISTINCT - mostly because it does not actually SORT.

Just because UNION uses a sort now doesn't mean it will use SORT for all time. Usually it is best to write the SQL in the simplest way and then let the optimizer decide.

Ross Leishman

Re: Which SQL is faster/better [message #600388 is a reply to message #600385] Tue, 05 November 2013 23:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Some good advice here. However, I think the basic point raised by cookiemonster still trumps all. There is little point in asking which sql is faster if the two are not semantically equivalent. We cannot tell at this point if this is true or not.

If we assume that PERIOD is never null, then your queries are not the same to-whit one is correct and one is wrong in which case the correct one would be the faster one.

If you are considering alternative sql formulations for your queries, be certain that they are semantically equivalent. You should know this first by their logic and second be able to show it using before/after results comparisons.

Kevin
Re: Which SQL is faster/better [message #600409 is a reply to message #600388] Wed, 06 November 2013 02:26 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Kevin,

PERIOD column may contain NULL values. If it contains NULL, I don't need to consider those records. I need to count the distinct 'year' from period column which is date data type. As I use COUNT function, NULLs will not be cosidered.

Thanks All for inputing in this thread.
Re: Which SQL is faster/better [message #600414 is a reply to message #600409] Wed, 06 November 2013 03:29 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd just go with rleishman's suggestion of using the simplest sql, since the optimiser my change from the current plans at any point.
Re: Which SQL is faster/better [message #600415 is a reply to message #600323] Wed, 06 November 2013 03:32 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
mokarem wrote on Wed, 06 November 2013 09:22
Lalit Kumar B wrote on Mon, 04 November 2013 23:36
Why sys.dual? Why not just dual?



One can create table using name 'dual'. Thats why we are suggested to use sys.dual.

Thanks indeed.


That should be filed under things that are so stupid that they just aren't worth worrying about.
If someone creates their own table called dual the only sensible thing to do is drop it immediately.
Previous Topic: Performance issue in one sql
Next Topic: Adaptive Cursor Sharing VS SPM
Goto Forum:
  


Current Time: Fri Mar 29 01:23:56 CDT 2024