OraFAQ Forum: Training & Certification » Which query is more efficient regarding db performance??

Home » Other » Training & Certification » Which query is more efficient regarding db performance??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Which query is more efficient regarding db performance?? [message #287815]

Thu, 13 December 2007 11:39

ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal

Junior Member

I have a table which contains huge data. And now
which query will perform better, SQL1 or SQL2 ?

Quote:

SQL1> select * from mytable where sal != 1000

Quote:

SQL2> select * from mytable where sal < 1000 or sal > 1000

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287819 is a reply to message #287815]

Thu, 13 December 2007 11:44

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What did this give you when you tried?

Regards
Michel

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287824 is a reply to message #287815]

Thu, 13 December 2007 12:38

ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal

Junior Member

Its a query about performance issue which i just imagine. I have not face it practically.
In addition, if the salary field has an index and if it has not then if there any difference between sql1 and sql2 exists regarding faster retrieval?
Thanks

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287825 is a reply to message #287815]

Thu, 13 December 2007 12:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It depends.
What did you own benchmarks show you?

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287827 is a reply to message #287824]

Thu, 13 December 2007 12:49

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No difference as long as < and > are defined.

Regards
Michel

[Updated on: Thu, 13 December 2007 12:49]

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287835 is a reply to message #287815]

Thu, 13 December 2007 13:28

ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal

Junior Member

Ok am I right
If salary has an index then there is no difference.
If not then sql1 will perform faster than sql2 because in sql2 and additional and checking is performed. And both are using FTS.

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287836 is a reply to message #287835]

Thu, 13 December 2007 13:39

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 13 December 2007 19:49

No difference as long as < and > are defined.

Regards
Michel

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287837 is a reply to message #287815]

Thu, 13 December 2007 13:47

ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal

Junior Member

I need more explanation why my assumption

Quote:

If salary has an index then there is no difference.
If not then sql1 will perform faster than sql2 because in sql2 and additional and checking is performed. And both are using FTS.

is wrong??

Thanks

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287838 is a reply to message #287837]

Thu, 13 December 2007 13:52

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Benchmark it.

Regards
Michel

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287898 is a reply to message #287815]

Fri, 14 December 2007 00:33

ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal

Junior Member

in sql1 just one checking is performed
In sql2 both checking < and > are performed. So it should take time longer.
Thats my view.

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287905 is a reply to message #287898]

Fri, 14 December 2007 00:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is good to have an assumption to start a research.
Now you have to prove your assumption otherwise it is useless.
So build a test case and time.

Regards
Michel

Report message to a moderator

Previous Topic:	Need Research topic about Data Warehousing
Next Topic:	Upgradation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:05:25 CDT 2024