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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

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)

When might a full table scan be faster than using an index in MySQL? When might a full table scan be faster than using an index in MySQL? Apr 09, 2025 am 12:05 AM

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

Explain InnoDB Full-Text Search capabilities. Explain InnoDB Full-Text Search capabilities. Apr 02, 2025 pm 06:09 PM

InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

Can I install mysql on Windows 7 Can I install mysql on Windows 7 Apr 08, 2025 pm 03:21 PM

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

MySQL: Simple Concepts for Easy Learning MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

Difference between clustered index and non-clustered index (secondary index) in InnoDB. Difference between clustered index and non-clustered index (secondary index) in InnoDB. Apr 02, 2025 pm 06:25 PM

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values ​​and pointers to data rows, and is suitable for non-primary key column queries.

The relationship between mysql user and database The relationship between mysql user and database Apr 08, 2025 pm 07:15 PM

In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.

Can mysql and mariadb coexist Can mysql and mariadb coexist Apr 08, 2025 pm 02:27 PM

MySQL and MariaDB can coexist, but need to be configured with caution. The key is to allocate different port numbers and data directories to each database, and adjust parameters such as memory allocation and cache size. Connection pooling, application configuration, and version differences also need to be considered and need to be carefully tested and planned to avoid pitfalls. Running two databases simultaneously can cause performance problems in situations where resources are limited.

Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial). Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial). Apr 02, 2025 pm 07:05 PM

MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.

See all articles