The DISTINCT hint is used to instruct the optimizer to adopt specific behavior when processing queries containing the DISTINCT keyword.
Hint type |
Description |
|---|---|
USE_HASH_DISTINCT |
Enables the optimizer to use the HASH algorithm to execute the DISTINCT operation. Its reverse operation is the NO_USE_HASH_DISTINCT hint. |
NO_USE_HASH_DISTINCT |
Disables the optimizer from using the HASH algorithm when executing the DISTINCT operation. Its reverse operation is the USE_HASH_DISTINCT hint. |
DISTINCT_PUSHDOWN |
Prompt the optimizer to allow distributed execution. DISTINCT Push down. Its reverse operation is NO_DISTINCT_PUSHDOWN Hint. |
NO_DISTINCT_PUSHDOWN |
Prompt the optimizer to prohibit in distributed environments. DISTINCT Push down. Its reverse operation is DISTINCT_PUSHDOWN Hint. |
USE_HASH_DISTINCT Hint
The USE_HASH_DISTINCT hint enables the optimizer to use the HASH algorithm for DISTINCT operations. Its counterpart is the NO_USE_HASH_DISTINCT hint.
Syntax
/*+ USE_HASH_DISTINCT [ ( [ @ qb_name ] ) ] */
Examples
In the query example below, USE_HASH_DISTINCT is used to enable the HASH deduplication algorithm.
-- Use the `USE_HASH_DISTINCT` hint to enable the `HASH` algorithm for `DISTINCT` operations.
EXPLAIN BASIC SELECT /*+ USE_HASH_DISTINCT */ DISTINCT c1, c2, c3 FROM t1;
| Query Plan |
+-------------------------------------------------------------------+
| =========================== |
| |ID|OPERATOR |NAME| |
| --------------------------- |
| |0 |HASH DISTINCT | | |
| |1 |└─TABLE FULL SCAN|T1 | |
| =========================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| distinct([T1.C1], [T1.C2], [T1.C3]) |
| 1 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------+
14 rows in set
NO_USE_HASH_DISTINCT Hint
The NO_USE_HASH_DISTINCT hint disables the specific algorithm used to perform a DISTINCT operation. Its counterpart is the USE_HASH_DISTINCT hint.
Syntax
/*+ NO_USE_HASH_DISTINCT [ ( [ @ qb_name ] ) ] */
Examples
In the query example below, the NO_USE_HASH_DISTINCT hint is used to disable the optimizer from using the HASH algorithm for DISTINCT operations. Since OceanBase supports both HASH and MERGE deduplication algorithms, the actual effect of using the NO_USE_HASH_DISTINCT hint is to use the MERGE deduplication algorithm.
-- Use the `NO_USE_HASH_DISTINCT` hint to ensure that the query does not use the `HASH` algorithm but instead uses the `MERGE` algorithm when performing a `DISTINCT` operation.
EXPLAIN BASIC SELECT /*+ NO_USE_HASH_DISTINCT */ DISTINCT c1, c2, c3 FROM t1;
+-------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------+
| ============================= |
| |ID|OPERATOR |NAME| |
| ----------------------------- |
| |0 |MERGE DISTINCT | | |
| |1 |└─SORT | | |
| |2 | └─TABLE FULL SCAN|T1 | |
| ============================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| distinct([T1.C1], [T1.C2], [T1.C3]) |
| 1 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| sort_keys([T1.C1, ASC], [T1.C2, ASC], [T1.C3, ASC]) |
| 2 - output([T1.C1], [T1.C2], [T1.C3]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------+
DISTINCT_PUSHDOWN Hint
The DISTINCT_PUSHDOWN hint instructs the optimizer to push down DISTINCT operations during distributed execution, allowing local deduplication to occur first on each partition node. Its counterpart is the NO_DISTINCT_PUSHDOWN hint.
Syntax
/*+ DISTINCT_PUSHDOWN [ ( [ @ qb_name ] ) ] */
Examples
In the query example below, DISTINCT_PUSHDOWN is used to enable pushdown of DISTINCT during distributed execution. If no hint is added, DISTINCT pushdown is performed by default.
-- Use the `DISTINCT_PUSHDOWN` hint to push down `DISTINCT` operations to partition nodes in distributed queries.
CREATE TABLE tp1(c1 INT, c2 INT, c3 INT) PARTITION BY HASH(c1) PARTITIONS 10;
EXPLAIN BASIC SELECT /*+ DISTINCT_PUSHDOWN */ DISTINCT c2, c3 FROM tp1;
+---------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME | |
| ----------------------------------------------- |
| |0 |PX COORDINATOR | | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001| |
| |2 | └─HASH DISTINCT | | |
| |3 | └─EXCHANGE IN DISTR | | |
| |4 | └─EXCHANGE OUT DISTR (HASH)|:EX10000| |
| |5 | └─HASH DISTINCT | | |
| |6 | └─PX PARTITION ITERATOR| | |
| |7 | └─TABLE FULL SCAN |TP1 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(TP1.C2, TP1.C3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(TP1.C2, TP1.C3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| distinct([TP1.C2], [TP1.C3]) |
| 3 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| 4 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| (#keys=2, [TP1.C2], [TP1.C3]), dop=1 |
| 5 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| distinct([TP1.C2], [TP1.C3]) |
| 6 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| force partition granule |
| 7 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| access([TP1.C2], [TP1.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([TP1.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------+
NO_DISTINCT_PUSHDOWN Hint
The NO_DISTINCT_PUSHDOWN hint prohibits the optimizer from pushing down DISTINCT operations during distributed execution. Its counterpart is the DISTINCT_PUSHDOWN hint.
Syntax
/*+ NO_DISTINCT_PUSHDOWN [ ( [ @ qb_name ] ) ] */
Example
In the query example below, NO_DISTINCT_PUSHDOWN is used to prevent the pushdown of DISTINCT operations during distributed execution.
-- Use the NO_DISTINCT_PUSHDOWN hint to prohibit pushdown of DISTINCT operations in distributed queries
CREATE TABLE tp1(c1 INT, c2 INT, c3 INT) PARTITION BY HASH(c1) PARTITIONS 10;
EXPLAIN BASIC SELECT /*+ NO_DISTINCT_PUSHDOWN */ DISTINCT c2, c3 FROM tp1;
+---------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME | |
| ----------------------------------------------- |
| |0 |PX COORDINATOR | | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001| |
| |2 | └─HASH DISTINCT | | |
| |3 | └─EXCHANGE IN DISTR | | |
| |4 | └─EXCHANGE OUT DISTR (HASH)|:EX10000| |
| |5 | └─PX PARTITION ITERATOR | | |
| |6 | └─TABLE FULL SCAN |TP1 | |
| =============================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(TP1.C2, TP1.C3)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(TP1.C2, TP1.C3)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| distinct([TP1.C2], [TP1.C3]) |
| 3 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| 4 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| (#keys=2, [TP1.C2], [TP1.C3]), dop=1 |
| 5 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| force partition granule |
| 6 - output([TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| access([TP1.C2], [TP1.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, |
| range_key([TP1.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------+
27 rows in set