How Oracle handles (select/where) and a join execution plan

0
votes

If I have a simple query like this:

SELECT table1.col3, table2.col3 FROM table1 LEFT OUTER JOIN table2
ON table1.col1 = table2.col1
WHERE table1.col2>0;

-Would Oracle execution plan select everything in table1 that meets the where clause condition first and then joins the tables or vise verse or is sequential or it finds the best route that would save whether resources or performance-wise ?

1
sqloraclesql-execution-plan
Why don't you just look at the execution plan? - a_horse_with_no_name
It will generate a query plan based on what it thinks is the fastest way and do it that way. You'll see in the query plan what it's doing - Nick.McDermaid
Here are two, pretty good documents about Oracle cost-based optimizer - The Oracle Optimizer Explain the Explain Plan, The Optimizer. - AndrewMcCoist

1 Answers

2
votes

This is too long for a comment.

Oracle is a powerful database that has multiple different options for a query like this. Specific classes of algorithms include nested-loop joins, hash-joins, merge-joins, and index-based joins. The optimizer chooses the best execution plan based on characteristics of the data and software/hardware environment.

If you have large tables and col2 > 0 is rather selective and there is an index on table1(col2), then Oracle would probably use that index for filtering first and then execute the join.