玉面飞龙的BLOG

看到itpub和asktom上都有讨论，唯一索引和非唯一索引如何选择。会留意到一个网友的这个提问。

hi tom,
please could you explain the following to me?
———————————————————
SQL> create table test as select * from tcp_stat_run_head;
SQL> select count(*) from test;
COUNT(*)
———-
44864
SQL> create table ids as select row_id from test where rownum<3000;
SQL> create index test_idx on test(row_id);
SQL> select min(strt_tm) from test where row_id in (select row_id from ids);
Plán provedení
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF ‘TEST’
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF ‘IDS’
5 3 INDEX (RANGE SCAN) OF ‘TEST_IDX’ (NON-UNIQUE)
Statistiky
———————————————————-
0 recursive calls
0 db block gets
3027 consistent gets
25 physical reads
0 redo size
396 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> drop index test_idx;
SQL> create unique index test_unq on test(row_id);
SQL> select min(strt_tm) from test where row_id in (select row_id from ids);
Plán provedení
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF ‘IDS’
4 2 TABLE ACCESS (BY INDEX ROWID) OF ‘TEST’
5 4 INDEX (UNIQUE SCAN) OF ‘TEST_UNQ’ (UNIQUE)
Statistiky
———————————————————-
0 recursive calls
0 db block gets
6007 consistent gets
6 physical reads
0 redo size
396 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

———————————————————–
why is the execution plan generated for non unique index scan better than the one for unique index scan?it’s 3027 consistent gets with non unique vs 6007 consistent gets with unique index.

Tom直接解释了吗？没有。由于唯一索引和非唯一索引的物理结构和实现方式不一样，相等查询( where column = X)的非唯一索引的访问一定会比唯一索引的相等查询多一个逻辑读。

SQL> create table t (uniq number,nonuniq number);
Table created.
SQL> insert into t select rownum,rownum from dba_objects;
18236 rows created.
SQL> commit;
Commit complete.
SQL> create unique index tuniq on t(uniq);
Index created.
SQL> create index tnonuniq on t(nonuniq);
Index created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> set autotrace on
SQL> select uniq from t where uniq=400;
Statistics
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
487 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select nonuniq from t where nonuniq=800;
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
490 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off

我们看一下这多出的一个buffer gets对应用的资源消耗影响，使用10046 trace event。

SQL> alter session set sql_trace=true;
Session altered.
SQL> declare
2 a number;
3 begin
4 for i in 1..18236 loop
5 select nonuniq into a from t where nonuniq=i;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> declare
2 b number;
3 begin
4 for i in 1..18236 loop
5 select uniq into b from t where uniq=i;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.

**********************************
SELECT NONUNIQFROM T WHERE NONUNIQ=:B1

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.01 0.00 0 0 0 0
Execute 18236 0.67 0.60 0 0 0 0
Fetch 18236 0.38 0.42 0 36511 0 18236
——- —— ——– ———- ———- ———- ———- ———-
total 36473 1.06 1.02 0 36511 0 18236
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (recursive depth: 1)
Rows Row Source Operation
——- —————————————————
18236 INDEX RANGE SCAN TNONUNIQ (object id 40555)
**************************************

SELECT UNIQ FROM T WHERE UNIQ=:B1

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 18236 0.67 0.58 0 0 0 0
Fetch 18236 0.24 0.22 0 36472 0 18236
——- —— ——– ———- ———- ———- ———- ———-
total 36473 0.91 0.81 0 36472 0 18236
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (recursive depth: 1)
Rows Row Source Operation
——- —————————————————
18236 INDEX UNIQUE SCAN TUNIQ (object id 40554)
*************************************
看到这个结果，各位看官一定会很惊讶，以为Non-unique Index的buffer gets 一定会比 Unique index 的buffer gets多出1/2。

这说明pl/sql对buffer gets(CBC latch)有特殊的优化和Cache. (研究中…)

但如果你的app是java等其他代码并且执行的非常频繁的话，这额外的1个buffer gets需要价值多少金钱的Capacity那。

所以，要慎重选择唯一索引还是非唯一索引 。