CODEWITHSUNDEEP
oracle-database
Robin
Robin

Reputation: 133

How does oracle execute an sql statement?

such as:

select country 
  from table1 
 inner join table2 on table1.id=table2.id
 where table1.name='a' and table2.name='b'
 group by country

after the parse, which part will be executed first?

Upvotes: 2

Views: 6121

Answers (3)

Peter G.
Peter G.

Reputation: 15124

It looks like you want to know the execution plan chosen by Oracle. You can get that ouput from Oracle itself:

set serveroutput off
< your query with hint "/*+ gather_plan_statistics */" inserted after SELECT >
select * from table(dbms_xplan.display_cursor(null, null, 'last allstats'));

See here for an explanation how to read a query plan: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/ex_plan.htm#i16971

Be aware however that the choice of a query plan is not fixed. Oracle tries to find the currently best query plan, based on available statistics data.

Upvotes: 4

Greg Reynolds
Greg Reynolds

Reputation: 10206

If you install the free tool SQL*Developer from Oracle, then you can click a button to get the explain plan.

A quick explanation is at http://www.seeingwithc.org/sqltuning.html

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

There are plenty of places you can find the order in which SQL is executed:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

But note that this is the "theoretical" order - SQL engines are allowed to perform the operations in other orders, provided that the end result appears to have been produced by using the above order.

Upvotes: 1

Related Questions