首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >具有或不具有唯一列的唯一索引性能

问具有或不具有唯一列的唯一索引性能
EN

Stack Overflow用户
提问于 2020-09-24 14:24:42
回答 1查看 95关注 0票数 1

当唯一索引列对相关列也有唯一约束时,在性能上有什么区别吗?

我知道唯一索引和非唯一索引在性能上是有区别的。

但我的问题是,如果列同时具有唯一约束和唯一索引,而只有唯一索引而没有唯一约束,那么性能会有什么不同吗?

另一个问题是,列统计信息对唯一索引的使用有影响吗?

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-09-24 18:12:24

Oracle数据库使用(唯一)索引来策略unique约束。

当检查重复条目、查询表等时,数据库将使用索引。而不是约束。因此,在很大程度上,性能将是相同的:

代码语言:javascript
复制
create table t (
  c1 int, c2 int
);

alter table t 
  add constraint u 
  unique ( c1 );
  
create unique index ui 
  on t ( c2 );
  
insert into t
with rws as (
  select level x from dual
  connect by level <= 10000
)
  select x, x from rws;
  
commit;

exec dbms_stats.gather_table_stats ( user, 't' ) ;

alter session set statistics_level = all;
set serveroutput off

select * from t
where  c1 = 1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

----------------------------------------------------------------------------------------------    
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
----------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:00:00.01 |       3 |    
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |      1 |      1 |      1 |00:00:00.01 |       3 |    
|*  2 |   INDEX UNIQUE SCAN         | U    |      1 |      1 |      1 |00:00:00.01 |       2 |    
----------------------------------------------------------------------------------------------

select * from t
where  c2 = 1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

----------------------------------------------------------------------------------------------    
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
----------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT            |      |      1 |        |      1 |00:00:00.01 |       3 |    
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |      1 |      1 |      1 |00:00:00.01 |       3 |    
|*  2 |   INDEX UNIQUE SCAN         | UI   |      1 |      1 |      1 |00:00:00.01 |       2 |    
----------------------------------------------------------------------------------------------

有一个例外。唯一约束可以是外键的目标。而唯一索引(单独)不能:

代码语言:javascript
复制
alter table t
  add constraint fk 
  foreign key ( c1 )
  references t ( c2 );
  
ORA-02270: no matching unique or primary key for this column-list
  
alter table t
  add constraint fk 
  foreign key ( c2 )
  references t ( c1 );

只要创建了unique和foreign key约束,优化器就可以消除某些查询中的表。这可能会带来巨大的性能优势:

代码语言:javascript
复制
select t1.* from t t1
join   t t2
on     t1.c1 = t2.c2;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

-------------------------------------------------------------------------------------    
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
-------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |      1 |        |   5000 |00:00:00.01 |     202 |    
|   1 |  NESTED LOOPS      |      |      1 |  10000 |   5000 |00:00:00.01 |     202 |    
|   2 |   TABLE ACCESS FULL| T    |      1 |  10000 |   5000 |00:00:00.01 |      60 |    
|*  3 |   INDEX UNIQUE SCAN| UI   |   5000 |      1 |   5000 |00:00:00.01 |     142 |    
------------------------------------------------------------------------------------- 

select t1.* from t t1
join   t t2
on     t1.c2 = t2.c1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

------------------------------------------------------------------------------------    
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |      1 |        |   5000 |00:00:00.01 |      60 |    
|*  1 |  TABLE ACCESS FULL| T    |      1 |  10000 |   5000 |00:00:00.01 |      60 |    
------------------------------------------------------------------------------------  

表统计信息将影响优化器是否使用索引。如果搜索小于100的唯一值:

代码语言:javascript
复制
select * from t
where  c1 <= 100;

如果表中只有100行,优化器更有可能进行全表扫描。但如果有数百万人,指数就会变得更具吸引力。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/64040638

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档