Oracle 大表添加索引的最佳方式

news/2024/11/1 9:53:39/

背景:

业务系统中现在经常存在上亿数据的大表,在这样的大表上新建索引,是一个较为耗时的操作,特别是在生产环境的系统中,添加不当,有可能造成业务表锁表,业务表长时间的停服势必会影响正常业务的开展。根据个人的实际经验,我们可以使用三种手段来帮助大家解决这个问题,需要注意的是这三种方法并不是独立使用的,很多时候我们会结合起来一起使用来提升建索引的效率。

解决方案:

第一种方法就是使用并行——parallel 开启并发执行并发执行可以最大程度的利用我们的数据库的硬件资源,把大批量的数据分成小批量到不同的进程去执行,从而大大减少sql的执行的时间。由于建索引属于ddl操作,我们可以通过下面的语句来实现并发执行。 下面的语句中,我们就配置了使用并发值为8来执行我们的sql语句

CREATE INDEX idx_table1_column1 ON table1 (column1) PARALLEL 8;

 注意:并不是所有的系统都适用使用并行来解决,比如:目前有个系统使用cpu已经很高,如果这时你再开启并行,只会加重系统的负载。因此,在执行并行操作前一定要看一下系统目前使用情况。第二种方法是不开启日志——nologging我们知道数据表新增、修改、删除记录都可能会触发redo日志和undo日志的记录,特别是insert into table1 select * from table2这种语句,每条insert动作都会同时生成redo日志和undo日志,从而降低sql的执行速度。 对于创建索引的操作也是如此,索引的创建同样也涉及到这两类日志的记录,我们可以手动指定不记录非必要日志来加快sql执行的速度。

注意:nologging的核心在于只输入最少的redo日志(注意,这里不是不输出日志,只是最小化需要输入的日志量而已) 用法的话十分简单,只需要在我们创建索引的语句上加上nologging关键字即可

CREATE INDEX idx_table1_column1 ON table1 (column1) nologging;

第三种方法是在线执行——online(推荐使用)前面介绍的两个命令虽然能大幅度提升效率,但归根结底建索引就是会导致锁表,不停服执行的话还是相当有风险的,online的作用在于不阻塞DML操作,使得生产环境不会因为执行DDL语句导致业务功能阻塞, 尤其适合于不停机新建表索引这类场景。

需要注意的是,online关键字的使用相对来说耗时会长一些,而且online关键字只能用于新增索引,并不能用在修改表结构等SQL语句中。 online的使用也十分简单,在sql语句后面加上online就行。

CREATE INDEX idx_table1_column1 ON table1 (column1) online;

有了这三个方法,我们的最终的sql大概是这样的,有了online可以保障不影响业务主流程的进行,而nologging和parallel则可以大幅度提高我们sql的执行速度,个人觉得是一种可行的解决方案。

CREATE INDEX idx_table1_column1 ON table1 (column1) parallel 8 nologging online ;

很多朋友认为到这里就结束了,其实oracle数据库优化的空间永无止境,如果有朋友想追求最佳,想把数据库的性能发挥到最佳。那么下面还有三种方法,但是这些不常用,作为学习数据库的原理,可以了解一下。

===========================================

补充方法1:由于创建索引时需要对表进行全表扫描,可以适当考虑调大db_file_multiblock_read_count的值, db_file_multiblock_read_count影响Oracle在读取数据时一次读取的最大block数量,在进行一些数据量比较大的操作时,可以适当 调整当前session的db_file_multiblock_read_count值,会在IO上节省节省一些时间。

SQL> show parameter db_file

NAME TYPE VALUE


db_file_multiblock_read_count integer 128

SQL> alter session set db_file_multiblock_read_count=256;

Session altered.

SQL> show parameter db_file

NAME TYPE VALUE


db_file_multiblock_read_count integer 256

补充方法2:我们知道索引都是有序的,利用索引的这个特性,因此我们可以想到,在创建索引时,要把索引列的值拿到内存中进行排序,因此我们调整排序区的大小(sort_area_size),建立索引时要对大量数据进行排序操作 在oracle11g,如果workarea_size_policy的值为AUTO,sort_area_size将被忽略,pga_aggregate_target将被启用,pga_aggregate_target决定了整个 的pga大小,而且一个session并不能使用全部的pga大小,它受到一个隐藏参数的限制,大致能使用pga_agregate_target的5%,因此可以 考虑将workarea_size_policy的值为manual,然后设置较大的sort_area_size以满足需求。

SQL> alter system set workarea_size_policy=‘MANUAL’;

System altered.

SQL> alter session set sort_area_size=204800;

Session altered.

SQL> show parameter sort_area_size;

NAME TYPE VALUE


sort_area_size integer 204800

补充方法3 :为了让添加索引的表能尽快加载到数据缓存区中buffer cache,我们可以使用cache和full hint对源表做fts,以使它尽可能的出现在 buffer cache中LRU的MRU一端。

SQL> select /*+ cache(t) full(t) / count() from big_table t;

打扫战场:添加完索引后,把打扫一下战场,把战场恢复到操作之前,因此我们要把调整的参数进行恢复到原来的样子。

SQL> alter system set workarea_size_policy=‘AUTO’;

System altered.

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.


http://www.ppmy.cn/news/1543545.html

相关文章

MFC工控项目实例二十八模拟量信号每秒采集100次

用两个多媒体定时器,一个定时0.1秒计时,另一个定时0.01秒用来对模拟量信号采集每秒100次。 1、在SEAL_PRESSUREDlg.h中添加代码 class CSEAL_PRESSUREDlg : public CDialo { public:CSEAL_PRESSUREDlg(CWnd* pParent NULL); // standard constructor&a…

解决pycharm无法添加conda环境的问题【Conda Environment下没有Existing environment】

解决pycharm无法添加conda environment 问题【Conda Environment下不显示Existing environment】 问题: 第一次下载好pycharm准备编写代码,在Anoconda Prompt建立好环境后,打开pycharm导入环境,却发现在【Conda Environment】处…

Kubernetes(K8s)相关漏洞介绍

Kubernetes(K8s)是一个开源的容器编排平台,用于自动化部署、扩展和管理容器化应用程序。然而,像任何复杂的软件系统一样,Kubernetes也存在一些安全漏洞。以下是一些已知的Kubernetes安全漏洞: Kubernetes镜…

Android 快捷方式

长按快捷方式 TargetApi(Build.VERSION_CODES.N_MR1)private ShortcutInfo createShortcutInfo1() {return new ShortcutInfo.Builder(this, "99009").setShortLabel("909888").setLongLabel("909888").setIcon(Icon.createWithResource(this, R…

使用Java内存级方式 和 Redis Lua 脚本方式实现滑动窗口限流

日常业务系统中,限流一般分为两种限流场景: 1、基于服务自身保护的应用自身限流。比如每一个启动的 springboot 实例服务都可以受理最大每秒150个请求的量,服务需要保护自身不被击垮对启动的每一个服务实例都进行限流处理。2、基于业务系统入…

Fsm3

采用读热码编写方式: module top_module(input clk,input in,input areset,output out); ////reg [3:0]A 4d0001;// reg [3:0]B 4d0010;//reg [3:0]C 4d0100;// reg [3:0]D 4d1000; //1、首先用读热码定义四个状态变量parameter A 4d0001 ,B 4d0010, C 4d01…

2、课程大纲、学习方法

1、课程大纲 2、学习方法 1、遗忘曲线 复习就是在不停的梳理自己的知识 理解基础上在你忘记之前不停重复 抓住主线做一个web版本的连接数据库的增删改查 2、学习成长曲线图 3、学习金字塔 只听和阅读肯定是不够的,最基本的是需要做笔记,复习&#xff0…

在pycharm中使用sqllite

在pycharm中使用sqllite sqllite 简介 SQLite 是一个开源的、轻量级的、关系型数据库管理系统(RDBMS),它设计用于嵌入到应用程序中,并且可以在无需外部服务器进程的情况下运行。SQLite 提供了完整的 SQL 语言支持,允…