Which SQL is faster/better [message #600323] |
Mon, 04 November 2013 23:33 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 #600356 is a reply to message #600354] |
Tue, 05 November 2013 03:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #600358 is a reply to message #600356] |
Tue, 05 November 2013 05:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
mokarem wrote on Tue, 05 November 2013 11:03Thanks 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #600409 is a reply to message #600388] |
Wed, 06 November 2013 02:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 #600415 is a reply to message #600323] |
Wed, 06 November 2013 03:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13923 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mokarem wrote on Wed, 06 November 2013 09:22Lalit Kumar B wrote on Mon, 04 November 2013 23:36Why 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.
|
|
|