oracle :为什么索引完全扫描用于非领先索引列上的group by子句
oracle :为什么索引完全扫描用于非领先索引列上的group by子句
提问于 2020-07-23 03:36:39
我有一个桌子雇员，一个正常的综合指数(身份，性别)，
SQL如下所示：
select sex,count(*) from employee group by sex;
Plan hash value: 1246558535
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 2 (50)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 4 | 2 (50)| 00:00:01 |
| 2 | INDEX FULL SCAN| IDX_ID_SEX | 104 | 208 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
----------------------------------------------------------
11 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed索引全扫描是使用的，我在这里很好奇:为什么使用索引全扫描而不是索引快速全扫描？我的理解是:对于索引全扫描，数据是按排序顺序返回的，由于列“性别”不是综合索引中的前导列，所以不能按其排序顺序返回，为什么在这里使用索引全扫描？
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-07-23 06:59:48
由于复合索引而更新答案
我决定根据您指定索引是复合的注释来更新答案。
- 索引全扫描按排序顺序读取每个索引节点。
- 索引快速全扫描用于按未排序顺序从索引中检索表行。
索引全扫描：当CBO统计数据表明全索引扫描比全表扫描更有效时，Oracle将选择索引完全扫描，并且对结果集进行排序或分组。当CBO确定查询将按索引顺序返回多行时，通常会调用全索引扫描，而全表扫描和按选项排序或分组可能会导致磁盘排序或散列操作进入临时表空间。
快速全索引扫描 当索引包含满足查询所需的所有值并且不需要访问表时调用该执行计划。快速全索引扫描执行计划将使用多块读取(使用db_file_multiblock_read_count)读取整个索引，并以未排序的顺序返回行。
在您的具体情况下，它应该使用快速的全扫描，但可能是统计数据方面的问题，或者是optimizer_index_cost_adj参数值太低。
让我给你看一个测试用例。我创建了一个表，其中一个id字段作为标识生成，一个字段具有性别(M或F )，一个随机字符串用于列c1。
SQL> create table test_index ( c1 varchar2(10), c2 number generated always as identity, c3 varchar2(1) );
Table created.填充200000条记录并在c2和c3上创建索引组合
SQL> declare
2 begin
3 for r in 1 .. 100000
4 loop
5 insert into test_index ( c1 , c3 ) values ( dbms_random.string('U',1
6 insert into test_index ( c1 , c3 ) values ( dbms_random.string('U',1
7 end loop;
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> create index idx_test_index on test_index (c2 , c3) ;
Index created.现在，让我们看看它的行为
SQL> select count(*) from cpl_rep.test_index ;
COUNT(*)
----------
200000
SQL> exec dbms_stats.gather_table_stats ( 'MYOWNER', 'TEST_INDEX' , cascade => true );
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> set lines 200
SQL> select c3, count(*) from test_index group by c3
Execution Plan
----------------------------------------------------------
Plan hash value: 805205005
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 102 (8)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 4 | 102 (8)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IDX_TEST_INDEX | 200K| 390K| 95 (2)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select /*+index ( a IDX_TEST_INDEX ) */ c3, count(*) from myowner.test_index a group by c3
2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2605845939
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 257 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 4 | 257 (4)| 00:00:01 |
| 2 | INDEX FULL SCAN| IDX_TEST_INDEX | 200K| 390K| 250 (1)| 00:00:01 |
-----------------------------------------------------------------------------------在我的示例中，由于optimizer_index_cost_adj的值，它使用快速全扫描。
SQL> set autotrace off
SQL> show parameter index_cost_adj
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100让我们把它改为一个低值，看看会发生什么
SQL> alter session set optimizer_index_cost_adj=20 ;
Session altered.
SQL> set autotrace traceonly explain
SQL> select c3, count(*) from cpl_rep.test_index group by c3 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2605845939
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 57 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 4 | 57 (13)| 00:00:01 |
| 2 | INDEX FULL SCAN| IDX_TEST_INDEX | 200K| 390K| 50 (0)| 00:00:01 |
-----------------------------------------------------------------------------------创建optimizer_index_cost_adj参数是为了允许更改全扫描操作相对于索引操作的相对成本。这是所有参数中最重要的参数，对于大多数Oracle系统来说，默认设置100是不正确的。它允许您对访问路径选择的优化器行为进行调优，使其或多或少对索引友好--也就是说，使优化器或多或少容易在全表扫描或全索引扫描的基础上选择索引访问路径。
此参数的默认值为100 %，优化器按常规成本计算索引访问路径。任何其他值都会使优化器按常规成本的这个百分比评估访问路径。例如，设置50会使索引访问路径看起来比正常值高一半。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63046475
复制相关文章
相似问题
腾讯云开发者
