No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1487
Act. users last 24h2
Act. users last hour0
Registered user hits last week190
Registered user hits last month829
Go up

Merge Join
Next thread: putty for window database
Prev thread: Indexes have stale statistics

Message Score Author Date
In normal emp and dept table as shown below: ==...... Nishant Kariya Jul 10, 2019, 12:04
Another question in this is - I gathered stats and...... Nishant Kariya Jul 10, 2019, 12:05
Hi, https://blogs.oracle.com/optimizer/dynamic-...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Jul 10, 2019, 12:18
Thanks Philip Wisse Dynamic sampling went away ...... Nishant Kariya Jul 11, 2019, 10:51
Hi Nishant, Your query needs many rows from bot...... Philip Wisse Jul 11, 2019, 11:30
Hi Philip, I am still confused. As per my knowl...... Nishant Kariya Jul 15, 2019, 08:09
Hi Nishant, > I am still confused. As per my kn...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Philip Wisse Jul 15, 2019, 09:42

Follow up by mail Click here


Subject: Merge Join
Author: Nishant Kariya, India
Date: Jul 10, 2019, 12:04, 151 days ago
Oracle info: 11g
Message: In normal emp and dept table as shown below:

=======================================================================
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)
;


create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
);;
=======================================================================




If I join these 2 on base of deptno then I will land into a Hash join as shown below:

Plan hash value: 615168685

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 826 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 826 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 406 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
- dynamic sampling used for this statement (level=2)







But if I introduce a index on foreign key i.e. on dept no of emp then my plan changes and starts taking Merge join, as shown below:

Plan hash value: 4270319431

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 826 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 826 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 406 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_IDX | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 | 120 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
- dynamic sampling used for this statement (level=2)






Why it is so????


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Merge Join
Author: Nishant Kariya, India
Date: Jul 10, 2019, 12:05, 151 days ago
Message: Another question in this is - I gathered stats and then too it is picking Dynamic sampling.

Why so?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Merge Join
Author: Philip Wisse, Netherlands
Date: Jul 10, 2019, 12:18, 151 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

https://blogs.oracle.com/optimizer/dynamic-sampling-and-its-impact-on-the-optimizer

It says : Level=2 "One or more tables in the statement have no statistics"

But after stats gathering it should be no DS
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Merge Join
Author: Nishant Kariya, India
Date: Jul 11, 2019, 10:51, 150 days ago
Message: Thanks Philip Wisse

Dynamic sampling went away after I carefully analyzed both tables. I was missing to gather stats for 1 of the 2 tables.

below is the new plan. But merge join is still there. Any idea about this?

Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 686 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 686 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 406 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 406 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Merge Join
Author: Philip Wisse, Netherlands
Date: Jul 11, 2019, 11:30, 150 days ago
Message: Hi Nishant,

Your query needs many rows from both the EMP and the DEPT table.
Without index the optimizer will do full table scans for both tables followed by a hash join.
A hash join sorts the data on the fly, therefore it is quite expensive.
In this case the optimizer has selected a merge join because this requires no sorting.
Instead, the DEPT rows need to be retrieved individually.
The optimizer estimates the cost based on statistics and eventually chooses a plan.

On a top level you can influence the optimizer with the setting: OPTIMIZER_MODE
https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams167.htm

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Merge Join
Author: Nishant Kariya, India
Date: Jul 15, 2019, 08:09, 146 days ago
Message: Hi Philip,

I am still confused. As per my knowledge Sort Merge joins are costlier than hash joins.

I we see the join key i.e. dept_id. Dept_id in department table has primary key index and hence almost sorted.

Dept_id in emp table is a foreign key and I have placed a index on it as well. If we look in the plan, optimizer is a doing a SORT operation on emp which should be costlier.

Reason what I can think of is - For merge join both tables should be sorted on join keys and in this example oracle finds that 1 of the 2 tables is sorted and that too the child table. Only thing oracle has to do is to sort a parent table(which will likely have less records than a child table)

And that's why here merge join is a better option.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Merge Join
Author: Philip Wisse, Netherlands
Date: Jul 15, 2019, 09:42, 146 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Nishant,

> I am still confused. As per my knowledge Sort Merge joins are costlier than hash joins.

The advantage of merge joins is that the first rows come fast, probably fulfilling your optimizer_mode

> I we see the join key i.e. dept_id. Dept_id in department table has primary key index and hence almost sorted.

True but still the data of the department table needs to be read.

> Dept_id in emp table is a foreign key and I have placed a index on it as well. If we look in the plan, optimizer is a doing a SORT operation on emp which should be costlier.

There is no sort operation in the plan shown. It makes use of the index to avoid sorting.

> Reason what I can think of is - For merge join both tables should be sorted on join keys ...

Not true as shown above

HTH Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here