Asked  7 Months ago    Answers:  5   Viewed   25 times

Is there a difference in performance (in oracle) between

Select * from Table1 T1 
Inner Join Table2 T2 On T1.ID = T2.ID

And

Select * from Table1 T1, Table2 T2 
Where T1.ID = T2.ID

?

 Answers

52

No! The same execution plan, look at these two tables:

CREATE TABLE table1 (
  id INT,
  name VARCHAR(20)
);

CREATE TABLE table2 (
  id INT,
  name VARCHAR(20)
);

The execution plan for the query using the inner join:

-- with inner join

EXPLAIN PLAN FOR
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2

And the execution plan for the query using a WHERE clause.

-- with where clause

EXPLAIN PLAN FOR
SELECT * FROM table1 t1, table2 t2
WHERE t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2
Tuesday, June 1, 2021
 
juanrpozo
answered 7 Months ago
44

Depends on your SQL engine. Newer SQL systems that have reasonable query optimizers will most likely rewrite both queries to the same plan. Typically, a sub-query (your second query) is rewritten using a join (the first query).

In simple SQL engines that may not have great query optimizers, the join should be faster because they may run sub-queries into a temporary in-memory table before running the outer query.

In some SQL engines that have limited memory footprint, however, the sub-query may be faster because it doesn't require joining -- which produces more data.

So, in summary, it depends.

Monday, July 5, 2021
 
Pupil
answered 5 Months ago
29

Try using more bind variables, especially where nested loops might happen. I've noticed that you can use it in cases like

CREATE_DT >= :YOUR_DATE instead of CREATE_DT >= sysdate - 60 

I think this would explain why you have 180 million executions in the lowest part of your execution plan even though the whole other part of the update query is still at 8 million out of your 79 million.

Wednesday, August 11, 2021
 
Zigglzworth
answered 4 Months ago
55

You are correct. You did exactly the right thing, checking the query plan rather than trying to second-guess the optimiser. :-)

Sunday, September 5, 2021
 
sawyer
answered 3 Months ago
14

There should be no difference. The optimizer should generate the same plan in both cases and should be able to apply the predicate before, after, or during the join in either case based on what is the most efficient approach for that particular query.

Of course, the fact that the optimizer can do something, in general, is no guarantee that the optimizer will actually do something in a particular query. As queries get more complicated, it becomes impossible to exhaustively consider every possible query plan which means that even with perfect information and perfect code, the optimizer may not have time to do everything that you'd like it to do. You'd need to take a look at the actual plans generated for the two queries to see if they are actually identical.

Thursday, October 14, 2021
 
JustSteveKing
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share