Home Database Mysql Tutorial Oracle SQL优化之使用索引提示一例

Oracle SQL优化之使用索引提示一例

Jun 07, 2016 pm 05:02 PM

下午做完其他的就打算优化一下这个SQL首先查看这个SQL的执行计划在PL/SQL Developer中的执行计划窗口中执行这个SQL然后得到执行计

在做数据库的安检时候,发现一个ORA-01555错误:

Oracle

这个SQL语句明显运行了很长时间而没有完成。在观察Statspack报告中这个SQL也在top
SQL中占用了大量的db cache。物理读很大。

Oracle

下午做完其他的就打算优化一下这个SQL
首先查看这个SQL的执行计划
在PL/SQL Developer中的执行计划窗口中执行这个SQL然后得到执行计划:如下

Oracle

可以看到在嵌套查询中使用了 提示 /*+ 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如下:

 

Oracle

在看一下执行计划:
 

Oracle

这个时候解决了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)

再来看看执行计划:

Oracle

IO耗费降低到66。
本文完。

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Reduce the use of MySQL memory in Docker Reduce the use of MySQL memory in Docker Mar 04, 2025 pm 03:52 PM

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

How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

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

How to solve the problem of mysql cannot open shared library How to solve the problem of mysql cannot open shared library Mar 04, 2025 pm 04:01 PM

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

Run MySQl in Linux (with/without podman container with phpmyadmin) Run MySQl in Linux (with/without podman container with phpmyadmin) Mar 04, 2025 pm 03:54 PM

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

What is SQLite? Comprehensive overview What is SQLite? Comprehensive overview Mar 04, 2025 pm 03:55 PM

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

Running multiple MySQL versions on MacOS: A step-by-step guide Running multiple MySQL versions on MacOS: A step-by-step guide Mar 04, 2025 pm 03:49 PM

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

How do I configure SSL/TLS encryption for MySQL connections? How do I configure SSL/TLS encryption for MySQL connections? Mar 18, 2025 pm 12:01 PM

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]

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

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

See all articles