Oracle SQL sub query vs inner join

Question

At first, I seen the select statement on Oracle Docs.

I have some question about oracle select behaviour, when my query contain select,join,where.

see this below for information:

My sample table:

[ P_IMAGE_ID ]
 IMAGE_ID (PK)
 FILE_NAME
 FILE_TYPE
 ...
 ...

[ P_IMG_TAG ]
 IMG_TAG_ID (PK)
 IMAGE_ID (FK)
 TAG
 ...
 ...

My requirement are: get distinct of image when it's tag is "70702".

---

Method 1: Select -> Join -> Where -> Distinct

SELECT DISTINCT PID.IMAGE_ID
              , PID.FILE_NAME
FROM P_IMAGE_ID PID
INNER JOIN P_IMG_TAG PTAG
        ON PTAG.IMAGE_ID = PID.IMAGE_ID
WHERE PTAG.TAG = '70702';

I think the query behaviour should be like:

join table -> hint where cause -> distinct select

I use Oracle SQL developer to get the explain plan:

Method 1 cost 76.

---

Method 2: Select -> Where -> Where -> Distinct

SELECT DISTINCT PID.IMAGE_ID
              , PID.FILE_NAME
FROM P_IMAGE_ID PID
WHERE PID.IMAGE_ID IN 
(
  SELECT PTAG.IMAGE_ID
  FROM P_IMG_TAG PTAG
  WHERE PTAG.TAG = '70702'
);

I think the second query behaviour should be like:

hint where cause -> hint where cause -> distinct select

I use Oracle SQL developer to get the explain plan too:

Method 2 cost 76 too. Why?

I believe when I try where cause first for reduce the database process and avoid join table that query performance should be better than the table join query, but now when I test it, I am confused, why 2 method cost are equal ? Or am I misunderstood something ?

List of my question here:

1. Why 2 method above cost are equal ?

2. If the result of sub select Tag = '70702' more than thousand or million or more, use join table should be better alright ?

3. If the result of sub select Tag = '70702' are least, use sub select for reduce data query process is better alright ?

4. When I use method 1 Select -> Join -> Where -> Distinct mean the database process table joining before hint where cause alright ?

5. Someone told me when i move hint cause Tag = '70702' into join cause (ie. INNER JOIN P_IMG_TAG PTAG ON PAT.IMAGE_ID = PID.IMAGE_ID AND PTAG.TAG = '70702' ) it's performance may be better that's alright ?

I read topic subselect vs outer join and subquery or inner join but both are for SQL Server, I don't sure that may be like Oracle database.

Answer 1

The DBMS takes your query and executes something. But it doesn't execute steps that correspond to SQL statement parts in the order they appear in an SQL statement.

Read about "relational query optimization", which could just as well be called "relational query implementation". Eg for Oracle.

Any language processor takes declarations and calls as input and implements the described behaviour in terms of internal data structures and operations, maybe through one or more levels of "intermediate code" running on a "virtual machine", eventually down to physical machines. But even just staying in the input language, SQL queries can be rearranged into other SQL queries that return the same value but perform significantly better under simple and general implementation assumptions. Just as you know that your question's queries always return the same thing for a given database, the DBMS can know. Part of how it knows is that there are many rules for taking a relational algebra expression and generating a different but same-valued expression. Certain rewrite rules apply under certain limited circumstances. There are rules that take into consideration SQL-level relational things like primary keys, unique columns, foreign keys and other constraints. Other rules use implementation-oriented SQL-level things like indexes and statistics. This is the "relational query rewriting" part of relational query optimization.

Even when two different but equivalent queries generate different plans, the cost can be similar because the plans are so similar. Here, both a HASH and SORT index are UNIQUE. (It would be interesting to know what the few top plans were for each of your queries. It is quite likely that those few are the same for both, but that the plan that is more directly derived from the particular input expression is the one that is offered when there's little difference.)

The way to get the DBMS to find good query plans is to write the most natural expression of a query that you can find.