SQL query optimizer

Question

I can not understand anything. I have Oracle 11g db with alot of data and tables. I want to join some tables and output result of it. Ok, just wrote:

SELECT *
FROM Table1
LEFT JOIN Table2 ON Table2.Table1_Id = Table1.Table1_Id
WHERE Table2.Column1=1

• very simple, but too slow. Ok, Table2 connected with Table3. This table contain some flag in column, which can tell us, that it generly connected with Table1. I wrote next code:

  SELECT *
  FROM Table1
  LEFT JOIN Table2 ON Table2.Table1_Id = Table1.Table1_Id
  LEFT JOIN Table3 ON Table3.Table3_Id = Table2_Table3_Id
  WHERE
  Table3.ColumnWithFlag LIKE '%some id info about Table1%'
  AND Table2.Column1=1
  

and this query have low cost and faster then first query. Why, I can not understand? I use 3 tables in my query and it work faster, then query with 2 tables (rowcount the same, data the same).

Answer 1

First, some basics. This is your first query:

SELECT *
FROM Table1 t1 LEFT JOIN
     Table2 t2 
     ON t2.Table1_Id = t1.Table1_Id
WHERE t2.Column1 = 1;

The LEFT JOIN is unnecessary, because the WHERE clause turns it into an INNER JOIN anyway. If there is no match, then t2.Column1 is NULL and it gets filtered out.

I could imagine at least one way that you could see this performance issue. Basically, if Table2 has no index on Table1_Id but it does have an index on Table3_Id.

In this case, the first query plan would need to scan Table2 (perhaps using an index on Column1) and then do database magic -- probably a hash join on the tables.

The second query would join Table2 and Table3 using available indexes. This would be (presumably) much smaller than just Table2. The database magic would be spared a full table scan of a very large table.

This is one possible scenario. The real way is to look at the execution plan for the queries to see what is different.