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 any method like finding time complexity of a program?
A:
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.
TimS
2010-08-25 10:21:56
+2
A:
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
Sachin Shanbhag
2010-08-25 10:23:28
A:
Generate the execution plan (e.g. using EXPLAIN ANALYZE or EXPLAIN PLAN) and compare the costs of the different statements.
a_horse_with_no_name
2010-08-25 10:26:56
A:
If you use MySQL, i think that most practical method to compare queries efficiency is BENCHMARK() query.
killer_PL
2010-08-25 10:31:21