组合索引和单列索引效率对比

最新推荐文章于 2025-02-01 19:00:06 发布

scan724

最新推荐文章于 2025-02-01 19:00:06 发布

阅读量931

点赞数

CC 4.0 BY-SA版权

分类专栏： sql 查询优化

本文链接：https://blog.youkuaiyun.com/zhaoyangjian724/article/details/22858203

sql 查询优化专栏收录该内容

204 篇文章 ¥39.90 ¥99.00

订阅专栏

本文通过创建并测试单列索引和组合索引，对比了在不同查询条件下它们的效率。实验显示，在某些情况下，虽然组合索引可能导致更少的物理读取，但当TABLE ACCESS BY INDEX ROWID能过滤掉大量数据时，单列索引可能更高效。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> create table  test as select * from dba_objects;

Table created.


select * from test where object_id=10 and OWNER='SYS';


SQL> select count(*) from test where object_id=10;

  COUNT(*)
----------
	 1

SQL> select count(*) from test;

  COUNT(*)
----------
     74443


模拟插入object_id=10 and OWNER='SYS'的记录

SQL> select count(*) from test where object_id=10 and owner='SYS';

  COUNT(*)
----------
   4194304




create table test4 as (select * from (select * from test where object_id=10 and owner='SYS') where  rownum<100001);

SQL> select count(*) from test4;

  COUNT(*)
----------
    100000


update test4 set owner='SCOTT';


SQL> insert into test select * from test4;

100000 rows created.

SQL> commit;

Commit complete.


SQL>

了解本专栏