how to know whether a sql query is optimal or not

i have four queries, all of which are valid and they do the same work. how to find out which one is the most optimal? i'm using oracle 10g. is there a开发者_如何学Cny method like finding time complexity of a program?

One of the parameters to measure is the performance of the query. For oracle you can use Explain Plan. Check out the details below -

http://www.adp-gmbh.ch/ora/explainplan.html

http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm

If you use MySQL, i think that most practical method to compare queries efficiency is BENCHMARK() query.

If you're using Microsoft SQL Server you could use SQL Management Studio to look at the queries' Execution Plans, or even run a SQL Profiler trace.

Generate the execution plan (e.g. using EXPLAIN PLAN) and compare the costs of the different statements.