1.定义

DP - distinct placement
The purpose of distinct placement is to eliminate duplicates as soon as possible. This is a cost-based query transformation that’s available as of version 11.2 only. The DISTINCT operator eliminates duplicate rows from a result set. When it’s specified in a query along with one or several joins, conceptually the database engine should process the DISTINCT operator after the joins have already been resolved. To achieve best performance, though, some cases call for eliminating duplicates before processing joins. Eliminating duplicates earlier keeps intermediate result sets as small as possible, and less processing is required for joins.

当对连接查询进行distinct操作时，CBO可能优先将一个表先进行一次distinct操作转化成一个内嵌视图后，再与后续的表关联。即执行计划里会出现两次distinct，这种转换常用于对某个表先执行distinct能过滤大量数据的情况。是否转化取决于COST。

2.参数(默认为true)

KSPPINM KSPPSTVL KSPPDESC
------------------------------ -------------------- --------------------------------------------------
_optimizer_distinct_placement TRUE consider distinct placement optimization

hint：place_distinct/no_place_distinct

3.例子

sql文本：

explain plan for
select distinct t1.pad, t2.pad
from t1, t2
where t1.id = t2.id;

执行计划：

Plan hash value: 3429193153
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 410 | 8157 (1)| 00:01:38 |
| 1 | HASH UNIQUE | | 1 | 410 | 8157 (1)| 00:01:38 |
| 2 | NESTED LOOPS | | 72 | 29520 | 8156 (1)| 00:01:38 |
| 3 | NESTED LOOPS | | 72 | 29520 | 8156 (1)| 00:01:38 |
| 4 | VIEW | VW_DTP_AE9E49E8 | 72 | 14688 | 8011 (1)| 00:01:37 |
| 5 | HASH UNIQUE | | 72 | 14688 | 8011 (1)| 00:01:37 |
| 6 | TABLE ACCESS FULL | T2 | 1000K| 194M| 7980 (1)| 00:01:36 |
|* 7 | INDEX RANGE SCAN | T1_INDX | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 206 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$08FE944A
4 - SEL$BCD4421C / VW_DTP_AE9E49E8@SEL$AE9E49E8
5 - SEL$BCD4421C
6 - SEL$BCD4421C / T2@SEL$1
7 - SEL$08FE944A / T1@SEL$1
8 - SEL$08FE944A / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$BCD4421C")
FULL(@"SEL$BCD4421C" "T2"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$08FE944A")
NLJ_BATCHING(@"SEL$08FE944A" "T1"@"SEL$1")
USE_NL(@"SEL$08FE944A" "T1"@"SEL$1")
LEADING(@"SEL$08FE944A" "VW_DTP_AE9E49E8"@"SEL$AE9E49E8" "T1"@"SEL$1")
INDEX(@"SEL$08FE944A" "T1"@"SEL$1" ("T1"."ID"))
NO_ACCESS(@"SEL$08FE944A" "VW_DTP_AE9E49E8"@"SEL$AE9E49E8")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$AE9E49E8")
PLACE_DISTINCT(@"SEL$1" "T2"@"SEL$1")
OUTLINE_LEAF(@"SEL$08FE944A")
OUTLINE_LEAF(@"SEL$BCD4421C")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."ID"="ITEM_1")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=2) "T1"."PAD"[CHARACTER,200], "ITEM_2"[CHARACTER,200]
2 - (#keys=0) "ITEM_2"[CHARACTER,200], "T1"."PAD"[CHARACTER,200]
3 - (#keys=0) "ITEM_2"[CHARACTER,200], "T1".ROWID[ROWID,10]
4 - "ITEM_1"[NUMBER,22], "ITEM_2"[CHARACTER,200]
5 - (#keys=2) "T2"."ID"[NUMBER,22], "T2"."PAD"[CHARACTER,200]
6 - "T2"."ID"[NUMBER,22], "T2"."PAD"[CHARACTER,200]
7 - "T1".ROWID[ROWID,10]
8 - "T1"."PAD"[CHARACTER,200]

明显看到CBO对该sql进行了place distinct转换，先对T2做一次distinct形成内嵌视图VW_DTP_AE9E49E8，再与T1连接后再做了一次distinct，这样做的好处了大量减少了驱动结果集的大小(将card=1000k distinct后变成card=72)降低连接成本。

10053：

****************************************
Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$1 (#1)
GBP: Checking validity of group-by placement for query block SEL$1 (#1)
GBP: Bypassed: Query has invalid constructs.
DP: Checking validity of distinct placement for query block SEL$1 (#1)
Column (#1): ID(
AvgLen: 5 NDV: 1000000 Nulls: 0 Density: 0.000001 Min: 1 Max: 1000000
Column (#1): ID(
AvgLen: 3 NDV: 101 Nulls: 0 Density: 0.009901 Min: 0 Max: 100
Column (#2): PAD(
AvgLen: 201 NDV: 1 Nulls: 0 Density: 1.000000

开始对sql进行Distinct Placement转换：
DP: Using search type: linear
DP: Considering distinct placement on query block SEL$1 (#1)
1)未转换的情况
DP: Starting iteration 1, state space = (1) : (0)
DP: Original query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "T1"."PAD" "PAD","T2"."PAD" "PAD" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."ID"="T2"."ID"
FPD: Considering simple filter push in query block SEL$1 (#1)
"T1"."ID"="T2"."ID"
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: "T1"."ID"="T2"."ID"
...
DP: Updated best state, Cost = 36518.41 --成本为36518.41

2)对sql进行Distinct Placement转换的情况：
DP: Starting iteration 2, state space = (1) : (1)
DP: Using DP transformation in this iteration.

Registered qb: SEL$08FE944A 0x87dbd5e0 (DISTINCT PLACEMENT SEL$1; SEL$1; "T2"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$08FE944A nbfros=2 flg=0
fro(0): flg=0 objn=14512 hint_alias="T1"@"SEL$1"
fro(1): flg=1 objn=0 hint_alias="VW_DTP_AE9E49E8"@"SEL$AE9E49E8"

DP: Transformed query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "T1"."PAD" "PAD","VW_DTP_AE9E49E8"."ITEM_2" "PAD" FROM (SELECT DISTINCT "T2"."ID" "ITEM_1","T2"."PAD" "ITEM_2" FROM "SYS"."T2" "T2") "VW_DTP_AE9E49E8","SYS"."T1" "T1" WHERE "T1"."ID"="VW_DTP_AE9E49E8"."ITEM_1"
FPD: Considering simple filter push in query block SEL$08FE944A (#1)
"T1"."ID"="VW_DTP_AE9E49E8"."ITEM_1"
try to generate transitive predicate from check constraints for query block SEL$08FE944A (#1)
finally: "T1"."ID"="VW_DTP_AE9E49E8"."ITEM_1"

最终转换的语句：

SELECT DISTINCT "T1"."PAD" "PAD",
"VW_DTP_AE9E49E8"."ITEM_2" "PAD"
FROM (SELECT DISTINCT "T2"."ID" "ITEM_1",
"T2"."PAD" "ITEM_2"
FROM "SYS"."T2" "T2") "VW_DTP_AE9E49E8","SYS"."T1" "T1"
WHERE "T1"."ID" = "VW_DTP_AE9E49E8"."ITEM_1"

DP: Updated best state, Cost = 8156.63 --成本仅为8156.73
DP: Doing DP on the preserved QB. --由于转换后成本更小所以决定转换

ITPub博客

Distinct Placement