The DISTINCT operator is used to remove duplicates from data rows, including duplicate NULL values.
The DISTINCT operator includes HASH DISTINCT and MERGE DISTINCT.
HASH DISTINCT
The HASH DISTINCT operator uses a hash algorithm to perform the DISTINCT operation.
Example 1: Use the hash algorithm to perform the DISTINCT operation and remove duplicates from the c1 column of the t1 table.
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+USE_HASH_AGGREGATION*/ DISTINCT c1 FROM t1;
Query Plan:
=======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
---------------------------------------
|0 |HASH DISTINCT| |101 |99169|
|1 | TABLE SCAN |t1 |100000 |66272|
=======================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
distinct([t1.c1])
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
In the preceding example, operator 0 (HASH DISTINCT) performs the deduplication operation in the execution plan. The outputs & filters section shows the output information of the HASH DISTINCT operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the HASH DISTINCT operator, so the value is nil. |
| partition | The partitions to be scanned for the query. |
| distinct | Specifies the columns that require deduplication. For example, the parameter t1.c1 of distinct([t1.c1]) specifies that column c1 of table t1 is deduplicated by using the hash algorithm. |
MERGE DISTINCT
The MERGE DISTINCT operator uses a merge algorithm to perform the DISTINCT operation.
Example 2: Use the merge algorithm to perform the DISTINCT operation.
obclient> EXPLAIN SELECT /*+NO_USE_HASH_AGGREGATION*/ DISTINCT c1 FROM t1;
Query Plan:
=======================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------
|0 |MERGE DISTINCT| |3 |40 |
|1 | SORT | |3 |39 |
|2 | TABLE SCAN |t1 |3 |37 |
=======================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
distinct([t1.c1])
1 - output([t1.c1]), filter(nil), sort_keys([t1.c1, ASC])
2 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
In the preceding example, operator 0 (MERGE DISTINCT) performs the deduplication operation by using the merge algorithm. Since the data output by operator 2 is unordered, and the MERGE DISTINCT operator requires ordered input, the SORT operator is used to sort the data before the deduplication operation. The outputs & filters section shows the output information of the MERGE DISTINCT operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the MERGE DISTINCT operator, so the value is nil. |
| distinct | Specifies the columns that require deduplication. For example, the parameter t1.c1 of distinct([t1.c1]) specifies that column c1 of table t1 is deduplicated by using the merge algorithm. |