I have confused with query process steps of select query. I read some docs, select query will run like this

1. Getting Data (From, Join)
2. Row Filter (Where)
3. Grouping (Group by)
4. Group Filter (Having)
5. Return Expressions (Select)
6. Order & Paging (Order by & Limit / Offset)

I retry test run a query join A table ( 70m records ) and B table( 75m records)

 select *
 from A join B on A.code = B.box_code
 where B.box_code = '123'

compare with

select *
 from A join (select * from B where box_code = '123' ) on A.code = B.box_code

I assume the first query will run slower than second query. Because the first query will take time when mapping large data while second query filters box_code before mapping data. But two queries run the same. Why did that happen?

I searched google, it may be related to clustered index, but I am not sure.

1 more question , why clustered index can get where condition to filter data before join ? i think the query will run join before where

Where did I get it wrong?

illustrating images

first query

second query

Thanks

CodePudding user response：

This part is wrong...

select query will run like this

1. Getting Data (From, Join)
2. Row Filter (Where)
3. Grouping (Group by)
4. Group Filter (Having)
5. Return Expressions (Select)
6. Order & Paging (Order by & Limit / Offset)

Oracle has a number of operations that it can perform to satisfy a query. Some operations may require child operations to be completed first. Operations include things like TABLE ACCESS BY INDEX ROWID, INDEX RANGE SCAN, and NESTED LOOPS.

Oracle's optimizer decides which operations are necessary and in what order. It very often will, for example, apply WHERE conditions to a row source before joining that row source to another one. It does that for exactly the reason you imply in your post: because it is probably faster to filter a million rows down to 10 before doing a join.

Oracle maintains an elaborate set of statistics on each table and column so that it can estimate when you submit your query what is likely to work well.

Theoretically, your job when writing SQL is to describe what you want and leave the how part to Oracle. In practice, the how part is still important, so your question is a very good one. Read Oracle's documentation on the subject, titled "Oracle Database SQL Tuning Guide". There is a version for each release of the database and they're available for free online (see: https://docs.oracle.com).