实验1:优化器对语句进行了merge
SELECT COUNT(DISTINCT T1_VC || T2_VC)
FROM (SELECT T1.SMALL_VC T1_VC, T2.SMALL_VC T2_VC
FROM T1, T2
WHERE T2.ID = T1.ID
AND T1.N1 <= 1000);
执行计划
----------------------------------------------------------
Plan hash value: 100109877
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 115 |
| 1 | SORT AGGREGATE | | 1 | 22 | |
| 2 | VIEW | VW_DAG_0 | 1000 | 22000 | 115 |
| 3 | HASH GROUP BY | | 1000 | 34000 | 115 |
|* 4 | HASH JOIN | | 1000 | 34000 | 63 |
|* 5 | TABLE ACCESS FULL| T1 | 1000 | 19000 | 31 |
| 6 | TABLE ACCESS FULL| T2 | 10000 | 146K| 31 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."ID"="T1"."ID")
5 - filter("T1"."N1"<=1000)
Note
-----
- cpu costing is off (consider enabling it)
实验2:使用no_merge hint,见Id=4处的不同
SELECT COUNT(DISTINCT T1_VC || T2_VC)
FROM (SELECT /*+ no_merge */
T1.SMALL_VC T1_VC, T2.SMALL_VC T2_VC
FROM T1, T2
WHERE T2.ID = T1.ID
AND T1.N1 <= 1000);
执行计划
----------------------------------------------------------
Plan hash value: 1355605975
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 114 |
| 1 | SORT AGGREGATE | | 1 | 22 | |
| 2 | VIEW | VW_DAG_0 | 1000 | 22000 | 114 |
| 3 | HASH GROUP BY | | 1000 | 22000 | 114 |
| 4 | VIEW | | 1000 | 22000 | 63 |
|* 5 | HASH JOIN | | 1000 | 34000 | 63 |
|* 6 | TABLE ACCESS FULL| T1 | 1000 | 19000 | 31 |
| 7 | TABLE ACCESS FULL| T2 | 10000 | 146K| 31 |
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T2"."ID"="T1"."ID")
6 - filter("T1"."N1"<=1000)
Note
-----
- cpu costing is off (consider enabling it)
实验3:改变hint位置,此处不起作用
SELECT /*+ no_merge */
COUNT(DISTINCT T1_VC || T2_VC)
FROM (SELECT /*+ qb_name(bb) */
T1.SMALL_VC T1_VC, T2.SMALL_VC T2_VC
FROM T1, T2
WHERE T2.ID = T1.ID
AND T1.N1 <= 1000);
执行计划
----------------------------------------------------------
Plan hash value: 100109877
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 115 |
| 1 | SORT AGGREGATE | | 1 | 22 | |
| 2 | VIEW | VW_DAG_0 | 1000 | 22000 | 115 |
| 3 | HASH GROUP BY | | 1000 | 34000 | 115 |
|* 4 | HASH JOIN | | 1000 | 34000 | 63 |
|* 5 | TABLE ACCESS FULL| T1 | 1000 | 19000 | 31 |
| 6 | TABLE ACCESS FULL| T2 | 10000 | 146K| 31 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."ID"="T1"."ID")
5 - filter("T1"."N1"<=1000)
Note
-----
- cpu costing is off (consider enabling it)
实验4:使用 no_merge(@bb) ,@bb来自/*+ qb_name(bb),此处作用与实验2一样,实验完毕
SELECT /*+ no_merge(@bb) */
COUNT(DISTINCT T1_VC || T2_VC)
FROM (SELECT /*+ qb_name(bb) */
T1.SMALL_VC T1_VC, T2.SMALL_VC T2_VC
FROM T1, T2
WHERE T2.ID = T1.ID
AND T1.N1 <= 1000);
执行计划
----------------------------------------------------------
Plan hash value: 1355605975
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 114 |
| 1 | SORT AGGREGATE | | 1 | 22 | |
| 2 | VIEW | VW_DAG_0 | 1000 | 22000 | 114 |
| 3 | HASH GROUP BY | | 1000 | 22000 | 114 |
| 4 | VIEW | | 1000 | 22000 | 63 |
|* 5 | HASH JOIN | | 1000 | 34000 | 63 |
|* 6 | TABLE ACCESS FULL| T1 | 1000 | 19000 | 31 |
| 7 | TABLE ACCESS FULL| T2 | 10000 | 146K| 31 |
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T2"."ID"="T1"."ID")
6 - filter("T1"."N1"<=1000)
Note
-----
- cpu costing is off (consider enabling it)