今天在CSDN上看到网友提出的一个关于“select distinct col from tabname”这样语句会不会用到col字段上索引的讨论。起初我毫不犹豫的以为,肯定可以使用到索引:全索引扫描。并且还做了一个试验加以验证:
但有网友提出这个语句“select distinct col from tabname”只有NOT NULL约束的字段上索引才会用到。
我做了相关试验,果如其然,记下来备忘:
然而,相似的情况在db2里是可以使用索引的:
-- The End --
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 --