当唯一索引列对相关列也有唯一约束时,在性能上有什么区别吗?
我知道唯一索引和非唯一索引在性能上是有区别的。
但我的问题是,如果列同时具有唯一约束和唯一索引,而只有唯一索引而没有唯一约束,那么性能会有什么不同吗?
另一个问题是,列统计信息对唯一索引的使用有影响吗?
发布于 2020-09-24 18:12:24
Oracle数据库使用(唯一)索引来策略unique约束。
当检查重复条目、查询表等时,数据库将使用索引。而不是约束。因此,在很大程度上,性能将是相同的:
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 |
----------------------------------------------------------------------------------------------有一个例外。唯一约束可以是外键的目标。而唯一索引(单独)不能:
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约束,优化器就可以消除某些查询中的表。这可能会带来巨大的性能优势:
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的唯一值:
select * from t
where c1 <= 100;如果表中只有100行,优化器更有可能进行全表扫描。但如果有数百万人,指数就会变得更具吸引力。
https://stackoverflow.com/questions/64040638
复制相似问题