Oracle 数据库 count的优化-避免全表扫描

2024-04-12 05:28
文章标签 oracle 优化 数据库 扫描 全表 避免 count

本文主要是介绍Oracle 数据库 count的优化-避免全表扫描，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Oracle 数据库  count的优化-避免全表扫描

select count(*) from t1;
这句话比较简单，但很有玄机！对这句话运行的理解，反映了你对数据库的理解深度！
建立实验的大表他t1

SQL> conn scott/tiger
已连接。
SQL> drop table t1 purge;

表已删除。

SQL> create table t1 as select * from emp where 0=9;

表已创建。

SQL> insert into t1 select * from emp;

已创建14行。

SQL>  insert into t1 select * from t1;

已创建14行。

SQL> /

已创建28行。

SQL> /

已创建56行。

SQL> /

已创建112行。

SQL> /

已创建224行。

SQL> /

已创建448行。

SQL> /

已创建896行。

SQL> /

已创建1792行。

SQL> /

已创建3584行。

SQL> /

已创建7168行。

SQL> /

已创建14336行。

SQL> /

已创建28672行。

SQL> /

已创建57344行。

SQL> commit;

提交完成。

收集统计信息
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE EXP
SQL> SELECT COUNT(*) FROM T1;

执行计划
--------------------------------------------------                                
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                 
-------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT   |      |     1 |   124 (4)| 00:00:02 |                                 
|   1 |  SORT AGGREGATE    |      |     1 |          |          |                                 
|   2 |   TABLE ACCESS FULL| T1   |   116K|   124 (4)| 00:00:02 |                                 
-----------------------------------------------------
代价为124，运行的计划为全表扫描。              
SQL> DELETE T1 WHERE DEPTNO=10;

已删除24576行。

SQL> COMMIT;

提交完成。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
-----------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                 
-------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT   |      |     1 |   123 (3)| 00:00:02 |                                 
|   1 |  SORT AGGREGATE    |      |     1 |          |          |                                 
|   2 |   TABLE ACCESS FULL| T1   | 90286 |  123  (3)| 00:00:02 |                                 
-----------------------------------------------------
SQL> --1.降低高水位
SQL> alter table t1 move tablespace users;

表已更改。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
-----------------------------------------------------
| Id  | Operation          | Name | Rows  |Cost (%CPU)| Time   |                                 
-------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT   |      |     1 |   102 (3)| 00:00:02 |                                 
|   1 |  SORT AGGREGATE    |      |     1 |          |          |                                 
|   2 |   TABLE ACCESS FULL| T1   | 90667 |   102 (3)| 00:00:02 |                                 
-----------------------------------------------------                          
代价为102，降低了

SQL> --2.修改pctfree
SQL> alter table t1 pctfree 0;

表已更改。

SQL> alter table t1 move tablespace users;

表已更改。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------                                          
Plan hash value: 3724264953                                                                         
                                                                                                    
-------------------------------------------------------------------                                 
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                 
-------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT   |      |     1 |    92   (4)| 00:00:02 |                                 
|   1 |  SORT AGGREGATE    |      |     1 |            |          |                                 
|   2 |   TABLE ACCESS FULL| T1   | 91791 |    92   (4)| 00:00:02 |                                 
-------------------------------------------------------------------                                 
代价为92，降低了10%

SQL> --3.参数db_file_multiblock_read_count=64
SQL> --4.建立b*tree类型的索引
SQL> create index i1 on t1(empno);

索引已创建。

SQL> execute dbms_stats.gather_index_stats('SCOTT','I1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------                                          
Plan hash value: 3724264953                                                                         
                                                                                                    
-------------------------------------------------------------------                                 
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |                                 
-------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT   |      |     1 |    92   (4)| 00:00:02 |                                 
|   1 |  SORT AGGREGATE    |      |     1 |            |          |                                 
|   2 |   TABLE ACCESS FULL| T1   | 91791 |    92   (4)| 00:00:02 |                                 
-------------------------------------------------------------------                                 
为什么没有使用我们建立的索引，因为null不进入普通的索引！

SQL> alter table t1 modify(empno not null);

表已更改。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------                                          
Plan hash value: 129980005                                                                          
                                                                                                    
----------------------------------------------------------------------                              
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |                              
----------------------------------------------------------------------                              
|   0 | SELECT STATEMENT      |      |     1 |    36   (6)| 00:00:01 |                              
|   1 |  SORT AGGREGATE       |      |     1 |            |          |                              
|   2 |   INDEX FAST FULL SCAN| I1   | 91791 |    36   (6)| 00:00:01 |                              
----------------------------------------------------------------------                              
我们的索引起到了很大的作用！

SQL> --5.使用并行查询的特性
                                
强制全表扫描，屏蔽索引

SQL> select /*+ full(t1) parallel(t1 2) */ COUNT(*) FROM T1;

执行计划
----------------------------------------------------------------------------------------
| Id  | Operation    | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |      
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |    |     1 |    51   (4)| 00:00:01 |    |      |    |  
|   1 |  SORT AGGREGATE        |          |     1 |    |    |        |      |    |    
|   2 |   PX COORDINATOR       |   |       |            |          |        |    |    |            
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |    |   |  Q1,00 | P->S | QC(RAND)  |         
|   4 |     SORT AGGREGATE     |          |     1 |    |  |  Q1,00 | PCWP |  |                   
|   5 |      PX BLOCK ITERATOR |          | 91791 |    51   (4)| 00:00:01 |  Q1,00 | PCWC|  |    
|   6 |       TABLE ACCESS FULL| T1       | 91791 |    51   (4)| 00:00:01 |  Q1,00 | PCWP |  |     
-----------------------------------------------------------------------------------------------
并行度越高，代价越低

SQL> alter table t1 parallel 4;

表已更改。
也可以通过使用表的属性来定义并行度，但是影响比较大，不如语句级别限制并行！

SQL> select count(*) from t1;

执行计划
-----------------------------------------------------------------------------------------
| Id  | Operation      | Name     | Rows  | Cost (%CPU)| Time   |    TQ  |IN-OUT| PQDistrib |     
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    25   (0)| 00:00:01 |    |    |    |         
|   1 |  SORT AGGREGATE        |       |     1 |      |          |        |      |     |         
|   2 |   PX COORDINATOR       |          |       |        |      |        |      |    |         
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |       |   |  Q1,00 | P->S | QC(RAND)  |      
|   4 |     SORT AGGREGATE     |          |     1 |      |      |  Q1,00 | PCWP |    |          
|   5 |      PX BLOCK ITERATOR |          | 91791 |    25   (0)| 00:00:01 |  Q1,00 | PCWC |   |   
|   6 |       TABLE ACCESS FULL| T1       | 91791 |    25   (0)| 00:00:01 |  Q1,00 | PCWP |   |  
---------------------------------------------------------------------------------------------
代价为25，代价比两个的又少一半！

SQL> --6.建立位图索引来避免全表扫描
SQL> create bitmap index i2 on t1(deptno);

索引已创建。

SQL> execute dbms_stats.gather_index_stats('SCOTT','I2');

PL/SQL 过程已成功完成。

SQL> select count(*) from t1;

执行计划
----------------------------------------------------------                                          
Plan hash value: 3738977131                                                                         
                                                                                                    
------------------------------------------------------------------------------                      
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |                      
------------------------------------------------------------------------------                      
|   0 | SELECT STATEMENT              |      |     1 |     4   (0)| 00:00:01 |                      
|   1 |  SORT AGGREGATE               |      |     1 |            |          |                      
|   2 |   BITMAP CONVERSION COUNT     |      | 91791 |     4   (0)| 00:00:01 |                      
|   3 |    BITMAP INDEX FAST FULL SCAN| I2   |       |            |          |                      
------------------------------------------------------------------------------                      

SQL> alter index i2 parallel 4;

索引已更改。

SQL> select count(*) from t1;
执行计划
----------------------------------------------------------------------------------------
| Id  | Operation       | Name   | Rows  | Cost (%CPU)| Time   |   TQ  |IN-OUT| PQ Distrib |     
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     2   (0)| 00:00:01 |      | |       |           
|   1 |  SORT AGGREGATE   |   |     1 |            |          |        |  |      |                
|   2 |   PX COORDINATOR   |      |       |       |          |        |  |    |                
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |    |          |  Q1,00 | P->S | QC (RAND) |    
|   4 |     SORT AGGREGATE  |    |     1 |   |      |  Q1,00 | PCWP |        |         
|   5 |      PX BLOCK ITERATOR |  | 91791 |     2   (0)| 00:00:01 |  Q1,00 |PCWC |     |         
|   6 |       BITMAP CONVERSION COUNT  |   | 91791 |  2   (0)| 00:00:01 |  Q1,00 |PCWP |    |   
|   7 |        BITMAP INDEX FAST FULL SCAN| I2    |   |     |     |  Q1,00 | PCWP |    |         
--------------------------------------------------------------------------------------------
代价为2，原来为124，优化无止境呀！

这篇关于Oracle 数据库 count的优化-避免全表扫描的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/896225。

相关文章

Java实现数据库图片上传与存储功能

Java实现数据库图片上传与存储功能

《Java实现数据库图片上传与存储功能》在现代的Web开发中,上传图片并将其存储在数据库中是常见的需求之一,本文将介绍如何通过Java实现图片上传,存储到数据库的完整过程,希望对大家有所帮助... 目录1. 项目结构2. 数据库表设计3. 实现图片上传功能3.1 文件上传控制器3.2 图片上传服务4. 实现
阅读更多...
使用国内镜像源优化pip install下载的方法步骤

使用国内镜像源优化pip install下载的方法步骤

《使用国内镜像源优化pipinstall下载的方法步骤》在Python开发中,pip是一个不可或缺的工具,用于安装和管理Python包,然而,由于默认的PyPI服务器位于国外,国内用户在安装依赖时可... 目录引言1. 为什么需要国内镜像源？2. 常用的国内镜像源3. 临时使用国内镜像源4. 永久配置国内镜
阅读更多...
C#原型模式之如何通过克隆对象来优化创建过程

C#原型模式之如何通过克隆对象来优化创建过程

《C#原型模式之如何通过克隆对象来优化创建过程》原型模式是一种创建型设计模式,通过克隆现有对象来创建新对象,避免重复的创建成本和复杂的初始化过程,它适用于对象创建过程复杂、需要大量相似对象或避免重复初... 目录什么是原型模式？原型模式的工作原理C#中如何实现原型模式？1. 定义原型接口2. 实现原型接口3
阅读更多...
使用Dify访问mysql数据库详细代码示例

使用Dify访问mysql数据库详细代码示例

《使用Dify访问mysql数据库详细代码示例》：本文主要介绍使用Dify访问mysql数据库的相关资料,并详细讲解了如何在本地搭建数据库访问服务,使用ngrok暴露到公网,并创建知识库、数据库访... 1、在本地搭建数据库访问的服务，并使用ngrok暴露到公网。#sql_tools.pyfrom
阅读更多...
Java实现数据库图片上传功能详解

Java实现数据库图片上传功能详解

《Java实现数据库图片上传功能详解》这篇文章主要为大家详细介绍了如何使用Java实现数据库图片上传功能,包含从数据库拿图片传递前端渲染,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1、前言2、数据库搭建&nbsChina编程p; 3、后端实现将图片存储进数据库4、后端实现从数据库取出图片给前端5、前端拿到
阅读更多...
IDEA连接达梦数据库的详细配置指南

IDEA连接达梦数据库的详细配置指南

《IDEA连接达梦数据库的详细配置指南》达梦数据库（DMDatabase）作为国产关系型数据库的代表,广泛应用于企业级系统开发,本文将详细介绍如何在IntelliJIDEA中配置并连接达梦数据库,助力... 目录准备工作1. 下载达梦JDBC驱动配置步骤1. 将驱动添加到IDEA2. 创建数据库连接连接参数
阅读更多...
Jmeter如何向数据库批量插入数据

Jmeter如何向数据库批量插入数据

《Jmeter如何向数据库批量插入数据》：本文主要介绍Jmeter如何向数据库批量插入数据方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录Jmeter向数据库批量插入数据Jmeter向mysql数据库中插入数据的入门操作接下来做一下各个元件的配置总结Jmete
阅读更多...
MySql中的数据库连接池详解

MySql中的数据库连接池详解

《MySql中的数据库连接池详解》：本文主要介绍MySql中的数据库连接池方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql数据库连接池1、概念2、为什么会出现数据库连接池3、原理4、数据库连接池的提供商5、DataSource数据源6、DBCP7、C
阅读更多...
StarRocks数据库详解(什么是StarRocks)

StarRocks数据库详解(什么是StarRocks)

《StarRocks数据库详解(什么是StarRocks)》StarRocks是一个高性能的全场景MPP数据库,支持多种数据导入导出方式,包括Spark、Flink、Hadoop等,它采用分布式架构,... 目录StarRocks介绍什么是StarRocks？StarRocks适合什么场景？StarRock
阅读更多...
Windows环境下安装达梦数据库的完整步骤

Windows环境下安装达梦数据库的完整步骤

《Windows环境下安装达梦数据库的完整步骤》达梦数据库的安装大致分为Windows和Linux版本,本文将以dm8企业版Windows_64位环境为例,为大家介绍一下达梦数据库的具体安装步骤吧... 目录环境介绍1 下载解压安装包2 根据安装手册安装2.1 选择语言 时区2.2 安装向导2.3 接受协议
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2