多动手多测试（五）直方图对执行计划影响的测试

最新推荐文章于 2023-10-20 15:09:41 发布

奶糖非糖

最新推荐文章于 2023-10-20 15:09:41 发布

阅读量964

点赞数 1

分类专栏：数据库学习文章标签： dba 数据库直方图索引执行计划

本文链接：https://blog.youkuaiyun.com/bojianhao123/article/details/123431129

版权

数据库学习专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了Oracle数据库中直方图的作用，通过创建测试表、插入数据、建立索引、分析表以及查看执行计划等步骤，阐述了直方图如何影响查询优化器的选择。直方图能描述数据分布，帮助优化器决定是否使用索引。当数据分布不均匀时，直方图对于优化执行计划至关重要，避免全表扫描。测试结果显示，查询数据行数小于5%时，Oracle倾向于使用索引。

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

0.前言

直方图是一种统计学上的工具，并非Oracle专有。通常用于对被管理对象的某个方面的质量情况进行管理，通常情况下它会表现为一种几何图形表，这个图形表是根据从实际环境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的，通常会画成以数量为底边，以频度为高度的一系列连接起来的矩形图，因此直方图在统计学上也称为质量分布图。

在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择

1.创建测试表

在这里插入图片描述

2.插入100W条测试数据

在这里插入图片描述

3.单独插入一行数据性别MF

在这里插入图片描述

4.建立索引

这里先插数，再建索引是因为，如果先建索引，后插数每个insert都会重建索引，减慢插入速度，数据少时不明显，100W的时候就会很慢。
正常来说，像 SEX这样的列是不适合建立索引的，走索引会比不走索引更慢，这里只是为了测试
在这里插入图片描述

5.查看列状态

在这里插入图片描述

此时没有进行表分析（没有收集信息），所以没有列状态信息

6.表分析

在这里插入图片描述

method_opt=> ‘for all indexed columns’ ，是给索引列建立直方图

7.再查看列状态

在这里插入图片描述

可以看到ID,NAME,SCORE上建立了HEIGHT BALANCED 高度平衡直方图，SEX上建立FREQUENCY 频率直方图，直方图种类是由数据决定的，因为SEX性别列有100W条数只有三个类型（男M，女F和中性MF）

8.查看执行计划

在这里插入图片描述

可以看到当SEX=’M’时，虽然SEX列上有索引但是走的全表扫。这里要特别说明一下，这是正确的，oracle 一般查询数据行数在5%以下希望走索引这里SEX=’M’有50W条数，所以不走索引，SEX=’MF’只有一条数，所以走索引。

9.删除直方图信息

在这里插入图片描述

10.再查看执行计划

在这里插入图片描述

可以看到，没了直方图，执行计划没办法准确判断数据的分布情况，认为他们是平均分布，M、F、MF各占三分之一，导致他们全都走了全表扫，这是不正确的。

11.顺便测试下查询总数百分之5以下数据会不会走索引

插入数据使SEX是种类增加到21，并且没有直方图的情况，如果推理正确的话，SEX=’M’和SEX=’MF’返回的ROWS数量均为1000020 / 21 = 47620，且执行计划应该会走索引
在这里插入图片描述

测试结果如下
在这里插入图片描述

可以看到，和推理一致，说明查询总数百分之5以下数据会走索引的说法是可靠的

附言一：执行sql

-- 建表

CREATE TABLE TABLE_TEST
(
   ID      VARCHAR2 (10 CHAR),
   NAME    VARCHAR2 (10 CHAR),
   sex     VARCHAR2 (2 CHAR),
   SCORE   VARCHAR2 (10 CHAR)
)
TABLESPACE CNAPS_DATA;


-- 插数

DECLARE
   num       NUMBER;
   t_score   VARCHAR2 (5);
   sex       VARCHAR2 (5);
BEGIN
   num := 0;
   t_score := 0;

   LOOP
      SELECT ABS (MOD (DBMS_RANDOM.RANDOM, 100)) INTO t_score FROM DUAL;

      IF (MOD (t_score, 2) = 0)
      THEN
         sex := 'M';
      ELSE
         sex := 'F';
      END IF;

      INSERT INTO TABLE_TEST (ID,
                              NAME,
                              sex,
                              SCORE)
           VALUES (num,
                   '哈' || num || '哈哈',
                   sex,
                   t_score);



      IF (MOD (num, 5000) = 0)
      THEN
         COMMIT;
         DBMS_OUTPUT.put_line ('提交了5000条');
      END IF;

      num := num + 1;
      EXIT WHEN num = 1000000;
   END LOOP;
END;

-- 建索引

CREATE INDEX CNAPS.TABLE_TEST_IDX1
   ON CNAPS.TABLE_TEST (id)
   TABLESPACE CNAPS_INDX;

CREATE INDEX CNAPS.TABLE_TEST_IDX2
   ON CNAPS.TABLE_TEST (name)
   TABLESPACE CNAPS_INDX;

CREATE INDEX CNAPS.TABLE_TEST_IDX3
   ON CNAPS.TABLE_TEST (sex)
   TABLESPACE CNAPS_INDX;

CREATE INDEX CNAPS.TABLE_TEST_IDX4
   ON CNAPS.TABLE_TEST (score)
   TABLESPACE CNAPS_INDX;

-- 单独查入一条

INSERT INTO TABLE_TEST (ID,
                        NAME,
                        SEX,
                        SCORE)
     VALUES ('3000000',
             '哈3000000哈哈',
             'MF',
             '101');

COMMIT;


-- 表分析（信息收集）
--（‘for all indexed columns’ 为建立直方图）
--（‘for all columns size 1’  为删除直方图）

BEGIN
   DBMS_STATS.gather_table_stats (
      ownname            => 'CNAPS',
      tabname            => 'TABLE_TEST',
      estimate_percent   => 100,
      no_invalidate      => FALSE,
      method_opt         => 'for all indexed columns',
      granularity        => 'All',
      cascade            => TRUE);
END;

-- 分组查询

  SELECT SEX, COUNT (1) FROM TABLE_TEST GROUP BY SEX;