Sql joining a table

I have a question reg开发者_运维知识库arding the SQL joins -

Whenever we join two different tables on some fields, what will happen exactly inside oracle which will result in the query output?

Does Oracle create/use a temporary table just for presenting the query output?

There is an overview of join mechanisms used in Oracle and a couple of Oracle wiki pages about join:

• Cluster join
• Hash join
• Nested loops join
• Sort merge join

The Cost-based optimizer documentation gives plenty of detail pertaining to access paths, how blocks of data are read, which scans are used etc.etc.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#35891

I don't think it will be temporary table, I guess it will table in the memory to speed up the operation.

If by "temporary table" you mean an Oracle global temporary table (GTT), the answer is No, Oracle never uses a GTT just for presenting the query output, but on the other hand, Yes, it might use a GTT for storing intermediate results depending on the query plan.