【唯一】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语句的描述中对此有严谨的描述。
链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm
摘录在此，供参考。

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 --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-625986/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-625986/