Oracle SQL优化之使用索引提示一例
下午做完其他的就打算优化一下这个SQL首先查看这个SQL的执行计划在PL/SQL Developer中的执行计划窗口中执行这个SQL然后得到执行计
在做数据库的安检时候,发现一个ORA-01555错误:
这个SQL语句明显运行了很长时间而没有完成。在观察Statspack报告中这个SQL也在top
SQL中占用了大量的db cache。物理读很大。
下午做完其他的就打算优化一下这个SQL
首先查看这个SQL的执行计划
在PL/SQL Developer中的执行计划窗口中执行这个SQL然后得到执行计划:如下
可以看到在嵌套查询中使用了 提示 /*+ all_rows*/ (这个是我的错,因为在上礼拜五的时候我发现一条同样差不多的语句嵌套语句和另外一条语句是一模一样的,我使用了这个 /*+ all_rows*/提示优化了一下,开发人员觉得第一张图中中的语句也应该加上该提示,结果在今天这条语句出现了问题。)
Person表走的是索引全扫描这个效率有点儿低,但是更糟糕的是mailsend表走的是全表扫描,根据语句中的条件
select * from mailsend ms where ms.personid=p.userid and (sysdate-15)从执行计划可以看出次查询并没有使用索引,在去到 dba_indexs 中查询mailsend表是否有索引
Select * from dba_indexs I where i.table_name=’MAILSEND’
果然没有索引。
于是乎创建一条索引:
Create index idx_perid_mailsend on mailsend(personid);
同时分析了一下该表
Analyzed table mailsend compute statistics;
改SQL中还是用了 in 这个关键字,在查询中最好将in使用exists替代来提高性能
修改后的sql如下:
在看一下执行计划:
这个时候解决了mailsend表的全表扫描情况,但是person表最外层还是走的全表扫描(虽然内层走的是主键索引扫描)这才是很重要的原因,update因为要更新内层的结果集,所以走的是全表扫描,没有使用索引,,显然是很慢的原因。
这个时候查看person的相关索引,只有两个复合索引。
这时候想起了可以使用提示强制走索引执行于是添加了一个索引提示 /*+ INDEX (tablename indexname) */(语法)
修改结果如下:
update /*+ INDEX ( per INDEX3_PERSON) */ person per
set per.sort = nvl(per.sort, 0) + 1
where exists
(select userid
from (select
p.userid, p.email
from Person p
where (sysdate - p.lastupdate) >=
(p.lastupdate + 3 - p.lastupdate)
and p.status = 3
and p.sort != 3
and not exists (select *
from mailsend ms
where ms.personid = p.userid
and (sysdate - 15) and ms.mailid = 1102)) us where us.userid=per.userid)
再来看看执行计划:
IO耗费降低到66。
本文完。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
