学步园

SQL> select * from dept; 

    DEPTNO DNAME          LOC 
---------- -------------- ------------- 
        10 ACCOUNTING    NEW YORK 
        20 RESEARCH      DALLAS 
        30 SALES         CHICAGO 
        40 OPERATIONS    BOSTON 

已选择4行。 

SQL> select INDEX_NAME from ind where TABLE_NAME='DEPT'; 

INDEX_NAME 
------------------------------ 
PK_DEPT 

已选择 1 行。 

SQL> 
SQL> set autotrace on 
SQL> select distinct deptno from scott.dept; 

    DEPTNO 
---------- 
        10 
        20 
        30 
        40 

已选择4行。 

Execution Plan 
---------------------------------------------------------- 
  0      SELECT STATEMENT Optimizer=CHOOSE 
  1    0  SORT (UNIQUE) 
  2    1    TABLE ACCESS (FULL) OF 'DEPT' 

Statistics 
---------------------------------------------------------- 
        18  recursive calls 
          0  db block gets 
          7  consistent gets 
          0  physical reads 
          0  redo size 
        452  bytes sent via SQL*Net to client 
        503  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          3  sorts (memory) 
          0  sorts (disk) 
          4  rows processed 

SQL> analyze table scott.dept compute statistics 
  2  ; 

表已分析。 

SQL> select distinct deptno from scott.dept; 

    DEPTNO 
---------- 
        10 
        20 
        30 
        40 

已选择4行。 

Execution Plan 
---------------------------------------------------------- 
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=8) 
  1    0  SORT (UNIQUE NOSORT) (Cost=3 Card=4 Bytes=8) 
  2    1    INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=4 B 
          ytes=8) 

Statistics 
---------------------------------------------------------- 
          0  recursive calls 
          0  db block gets 
          2  consistent gets 
          0  physical reads 
          0  redo size 
        452  bytes sent via SQL*Net to client 
        503  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
          4  rows processed 

SQL>

但有网友提出这个语句“select distinct col from tabname”只有NOT NULL约束的字段上索引才会用到。
我做了相关试验，果如其然，记下来备忘：

SQL> create table mytab(id number,name varchar2(10));

表已创建。

SQL> create index mytab_i1 on mytab(id);

索引已创建。

SQL> begin
  2  for x in 1..4 loop
  3  insert into mytab values(x,dbms_random.string('u',10));
  4  end loop;
  5  insert into mytab values(null,'test');
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> desc mytab
 名称                            是否为空? 类型
 ------------------------------- -------- -------------
 ID                                       NUMBER
 NAME                                     VARCHAR2(10)

SQL> analyze table mytab compute statistics;

表已分析。

SQL> set autotrace on
SQL> select distinct id from mytab;

        ID
----------
         1
         2
         3
         4

已选择5行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=4 Bytes=8)
   1    0   SORT (UNIQUE) (Cost=4 Card=4 Bytes=8)
   2    1     TABLE ACCESS (FULL) OF 'MYTAB' (Cost=2 Card=5 Bytes=10)

SQL> delete from mytab where id is null;  -- 删除掉null

已删除 1 行。

SQL> analyze table mytab compute statistics; -- 再次搜集统计信息

表已分析。

SQL> select distinct id from mytab;

        ID
----------
         1
         2
         3
         4

已选择4行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=4 Bytes=8)
   1    0   SORT (UNIQUE) (Cost=4 Card=4 Bytes=8)
   2    1     TABLE ACCESS (FULL) OF 'MYTAB' (Cost=2 Card=4 Bytes=8)

SQL> alter session set optimizer_mode=rule;

会话已更改。

SQL> select /*+ index(mytab mytab_i1) */ distinct id from mytab;  -- 使用索引提示也没用

        ID
----------
         1
         2
         3
         4

已选择4行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=4 Card=4 Bytes=8)
   1    0   SORT (UNIQUE) (Cost=4 Card=4 Bytes=8)
   2    1     TABLE ACCESS (FULL) OF 'MYTAB' (Cost=2 Card=4 Bytes=8)

然而，相似的情况在db2里是可以使用索引的：

d:/>db2 create table mytab(id int,name varchar(10))
DB20000I  SQL 命令成功完成。

d:/>db2 insert into mytab values(1,'test1'),(2,'test2'),(3,'test3'),(null,'test4')
DB20000I  SQL 命令成功完成。

d:/>db2 create index mytab_i1 on mytab(id)
DB20000I  SQL 命令成功完成。

d:/>db2 runstats on table administrator.mytab on all columns and indexes all
DB20000I  RUNSTATS 命令成功完成。

d:/>db2expln -d test -t -q "select distinct id from mytab"

SQL Statement:

  select distinct id
  from mytab

Section Code Page = 1386

Estimated Cost = 0.016442
Estimated Cardinality = 4.000000

Access Table Name = ADMINISTRATOR.MYTAB  ID = 2,8
|  Index Scan:  Name = ADMINISTRATOR.MYTAB_I1  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: ID (Ascending)
|  #Columns = 1
|  #Key Columns = 0
|  |  Start Key: Beginning of Index
|  |  Stop Key: End of Index
|  Index-Only Access
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Index Predicate(s)
|  |  Distinct Filter  #Columns = 1
Return Data to Application
|  #Columns = 1

End of section

d:/>

-- The End --