Sql joining a table

Question

I have a question regarding 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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.