【唯一】DISTINCT與UNIQUE的“區別”

今天一個朋友在Oracle中偶然發現UNIQUE也可以得到唯一的資料結果，問到DISTINCT與UNIQUE的區別。
答案是：他們沒有區別！

在Oracle資料庫中他們的功能是完全一樣的。

簡單演示一下（雖然多餘，不過務實一下亦好）。

1.建立測試表T
sec@ora10g> create table t (x int);

Table created.

2.初始化幾條資料
sec@ora10g> insert into t values (1);
sec@ora10g> insert into t values (2);
sec@ora10g> insert into t values (2);
sec@ora10g> insert into t values (3);
sec@ora10g> insert into t values (3);
sec@ora10g> insert into t values (3);
sec@ora10g> commit;

3.T表全貌
sec@ora10g> select * from t;

         X
----------
         1
         2
         2
         3
         3
         3

6 rows selected.

4.使用常用的DISTINCT得到唯一值方法
sec@ora10g> select distinct x from t;

         X
----------
         1
         2
         3

5.使用UNIQUE得到唯一值方法
sec@ora10g> select unique x from t;

         X
----------
         1
         2
         3

6.執行計劃也完全一樣
sec@ora10g> select distinct x from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1793979440

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     6 |    78 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |     6 |    78 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     6 |    78 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

sec@ora10g> select unique x from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1793979440

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     6 |    78 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |     6 |    78 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     6 |    78 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

7.結論及進一步論據
結論：DISTINCT與UNIQUE效果相同。
進一步論據：Oracle官方文件在有關SELECT語句的描述中對此有嚴謹的描述。
連結：
摘錄在此，供參考。

DISTINCT | UNIQUE

Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

Restrictions on DISTINCT and UNIQUE Queries These types of queries are subject to the following restrictions:

• When you specify DISTINCT or UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.

• You cannot specify DISTINCT if the select_list contains LOB columns.

8.小結
遇到疑問時，隨時請教Oracle官方文件是一個非常好的習慣。
簡單構造實驗是消除疑慮的最好手段。

Good luck.

secooler
10.01.25

-- The End --