How Join query execute

Question

When more than two table are joined then how oracle database determine the execution order of a join query.

I list 3 table which does not have any constraint except composite index like col1A,col1B on TableA.

Now tell me in which order query run?
and have any alternate solution for run query faster?

Tables:

TableA <br/>
_______ <br/>
col1A ====> composite Index Column | col2A ====> composite Index Column | col3A | col4A 
----------------------------------------------------------------------------------------



TableB
____________
col1B |  col2B  |  col3B  |  col4B 
-------------------------------------


TableC
___________ 
col1c |  col2c  |  col3c  |  col4c
-------------------------------------

Query:

SELECT col1c FROM TableA a,TableB b,TableC c 
WHERE a.col1A = b.col1B <br/>
AND b.col1B=c.col1C <br/>
AND a.col2A=b.col2B <br/>

The Oracle docs talk about this: How the Optimizer Executes Join Statements - docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL94679 ... might be what you want to know. — Stidgeon
– Stidgeon, Commented Feb 10, 2016 at 16:01

Community · Accepted Answer · 2017-05-23 12:23:24Z

1

The first thing examining a query is to preform EXPLAIN PLAN

You see in it what (if) indexes are used and in which order the table are processed.

Simple example

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
select col1c from TableA a,TableB b,TableC c 
where a.col1A = b.col1B
and b.col1B=c.col1C
and a.col2A=b.col2B 



 SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL'));

returns

Plan hash value: 2882163091

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |  1000 | 65000 |    80   (3)| 00:00:01 |
|*  1 |  HASH JOIN          |        |  1000 | 65000 |    80   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |        |  1000 | 52000 |    11  (10)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TABLEB |  1000 | 26000 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TABLEA | 10000 |   253K|     7   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | TABLEC |   110K|  1401K|    69   (2)| 00:00:01 |
------------------------------------------------------------------------------
...

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."COL1B"="C"."COL1C")
   2 - access("A"."COL1A"="B"."COL1B" AND "A"."COL2A"="B"."COL2B")

...

To be read from right (most nested) to left

first the tables A and B are HASH joined (without indexes)
in second step the result is joined with the table C aging with HASH join.

Note - Your plan may vary, dependent on your data and index definition!

Some additional information can be found here

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Feb 10, 2016 at 16:31

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Smith Magra Over a year ago

10x Mr Bomber , but i can't understand what is rows , Bytes and cost? in my plan 0 | SELECT STATEMENT | | 1 | 10900 | 100 ||0:00:01 and why cpu cost 100%.

Marmite Bomber Over a year ago

@Smith start with Oracle documentation, check "Oracle CBO" in web. A good practice to enter Oracle CBO (cost based optimizer) is to read a book on this topic - search "recommended Oracle CBO book"

Collectives™ on Stack Overflow

How Join query execute

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related