I sometimes get confused about the difference between (no_)merge and(no_)unnest. I just do some test here to make the difference clearly.
The original sql and its plan are below. I’ll hint the sql with no_merge andno_unnest. You will find the difference quickly.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
select
*
from
emp
where
deptno
in
(
select
deptno
from
dept
where
dname=
'SALES'
);
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
----------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 4 | 208 | 6 (17)| 00:02:09 |
| 1 | MERGE
JOIN
| | 4 | 208 | 6 (17)| 00:02:09 |
|* 2 |
TABLE
ACCESS
BY
INDEX
ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:43 |
| 3 |
INDEX
FULL
SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:22 |
|* 4 | SORT
JOIN
| | 13 | 507 | 4 (25)| 00:01:26 |
| 5 |
TABLE
ACCESS
FULL
| EMP | 13 | 507 | 3 (0)| 00:01:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - filter(
"DNAME"
=
'SALES'
)
4 - access(
"DEPTNO"
=
"DEPTNO"
)
filter(
"DEPTNO"
=
"DEPTNO"
)
|
I try the hint no_merge in order to avoid merging the subquery. This obviously don’t work.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
select
*
from
emp
where
deptno
in
(
select
/*+ no_merge */
deptno
from
dept
where
dname=
'SALES'
);
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
----------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 4 | 208 | 6 (17)| 00:02:09 |
| 1 | MERGE
JOIN
| | 4 | 208 | 6 (17)| 00:02:09 |
|* 2 |
TABLE
ACCESS
BY
INDEX
ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:43 |
| 3 |
INDEX
FULL
SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:22 |
|* 4 | SORT
JOIN
| | 13 | 507 | 4 (25)| 00:01:26 |
| 5 |
TABLE
ACCESS
FULL
| EMP | 13 | 507 | 3 (0)| 00:01:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - filter(
"DNAME"
=
'SALES'
)
4 - access(
"DEPTNO"
=
"DEPTNO"
)
filter(
"DEPTNO"
=
"DEPTNO"
)
|
Then I try the hint no_unnest. It works now. That means (no_)unest works only in the where clause. So I guess (no_)merge is only suitable after the from.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
select
*
from
emp
where
deptno
in
(
select
/*+ no_unnest */
deptno
from
dept
where
dname=
'SALES'
);
----------------------------------------------------------
Plan hash value: 2809975276
----------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
----------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 4 | 156 | 6 (0)| 00:02:09 |
|* 1 | FILTER | | | | | |
| 2 |
TABLE
ACCESS
FULL
| EMP | 13 | 507 | 3 (0)| 00:01:05 |
|* 3 |
TABLE
ACCESS
BY
INDEX
ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:22 |
|* 4 |
INDEX
UNIQUE
SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
1 - filter( EXISTS (
SELECT
/*+ NO_UNNEST */
0
FROM
"DEPT"
"DEPT"
WHERE
"DEPTNO"
=:B1
AND
"DNAME"
=
'SALES'
))
3 - filter(
"DNAME"
=
'SALES'
)
4 - access(
"DEPTNO"
=:B1)
|
I do another test with the original sql below:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
select
*
from
emp, (
select
*
from
dept
where
dname =
'SALES'
) dept
where
dept.deptno = emp.deptno;
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
----------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 4 | 236 | 6 (17)| 00:02:09 |
| 1 | MERGE
JOIN
| | 4 | 236 | 6 (17)| 00:02:09 |
|* 2 |
TABLE
ACCESS
BY
INDEX
ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:43 |
| 3 |
INDEX
FULL
SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:22 |
|* 4 | SORT
JOIN
| | 13 | 507 | 4 (25)| 00:01:26 |
| 5 |
TABLE
ACCESS
FULL
| EMP | 13 | 507 | 3 (0)| 00:01:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - filter(
"DNAME"
=
'SALES'
)
4 - access(
"DEPT"
.
"DEPTNO"
=
"EMP"
.
"DEPTNO"
)
filter(
"DEPT"
.
"DEPTNO"
=
"EMP"
.
"DEPTNO"
)
|
you will see the no_unest hint doesn’t work.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
select
*
from
emp, (
select
/*+ no_unnest */
*
from
dept
where
dname =
'SALES'
) dept
where
dept.deptno = emp.deptno;
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
----------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 4 | 236 | 6 (17)| 00:02:09 |
| 1 | MERGE
JOIN
| | 4 | 236 | 6 (17)| 00:02:09 |
|* 2 |
TABLE
ACCESS
BY
INDEX
ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:43 |
| 3 |
INDEX
FULL
SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:22 |
|* 4 | SORT
JOIN
| | 13 | 507 | 4 (25)| 00:01:26 |
| 5 |
TABLE
ACCESS
FULL
| EMP | 13 | 507 | 3 (0)| 00:01:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 - filter(
"DNAME"
=
'SALES'
)
4 - access(
"DEPT"
.
"DEPTNO"
=
"EMP"
.
"DEPTNO"
)
filter(
"DEPT"
.
"DEPTNO"
=
"EMP"
.
"DEPTNO"
)
|
Now I hint the original sql with the hint no_merge. It works. That’s it!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
select
*
from
emp, (
select
/*+ no_merge */
*
from
dept
where
dname =
'SALES'
) dept
where
dept.deptno = emp.deptno;
----------------------------------------------------------
Plan hash value: 2910064727
----------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
----------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 4 | 276 | 7 (15)| 00:02:19 |
|* 1 | HASH
JOIN
| | 4 | 276 | 7 (15)| 00:02:19 |
| 2 |
VIEW
| | 1 | 30 | 3 (0)| 00:01:05 |
|* 3 |
TABLE
ACCESS
FULL
| DEPT | 1 | 20 | 3 (0)| 00:01:05 |
| 4 |
TABLE
ACCESS
FULL
| EMP | 13 | 507 | 3 (0)| 00:01:05 |
----------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
1 - access(
"DEPT"
.
"DEPTNO"
=
"EMP"
.
"DEPTNO"
)
3 - filter(
"DNAME"
=
'SALES'
)
|
扫一扫
1276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
