0

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/>

Tom
  • 6,988
  • 1
  • 26
  • 40
Smith Magra
  • 66
  • 1
  • 7
  • The Oracle docs talk about this: How the Optimizer Executes Join Statements - https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL94679 ... might be what you want to know. – Stidgeon Feb 10 '16 at 16:01

1 Answers1

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

Community
  • 1
  • 1
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • 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%. – Smith Magra Feb 10 '16 at 16:50
  • @Smith start with Oracle [documentation](http://docs.oracle.com/cd/E11882_01/server.112/e41573/ex_plan.htm#PFGRF94697), 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" – Marmite Bomber Feb 10 '16 at 17:29