oracle下一条SQL语句的优化过程(比较详细)
很简单的一次调整,语句加了适当的索引后性能就有大幅的提升。当时看到这条语句的时候,第一感觉就是执行效率肯定低下。语句的功能是求某一客户当天产品的总销量。
原来的语句是这样的:select sum(sl0000) from xstfxps2 where
dhao00 in (
select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00='500000003913');
已用时间: 00: 02: 49.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'XSTFXPS2'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS1'
5 4 INDEX (UNIQUE SCAN) OF 'XSTFXPS1_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17355138 consistent gets
34141 physical reads
2912 redo size
198 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们看到统计信息里面进行了17355138次逻辑读,34141次物理IO,这是相当吓人的数字。在执行计划里面我们看到表XSTFXPS2来了一次全表扫描。
我们首先看一下这两张表总的数据量:
SQL> select count(*) from xstfxps2;
COUNT(*)
----------
5585018
我们这里看到XSTFXPS2这张表有5585018条记录。
SQL> select count(*) from xstfxps1;
COUNT(*)
----------
702121
两张表的表结构如下所示:
SQL> desc xstfxps1
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DHAO00 NUMBER(8)
LHDH00 NUMBER(8) Y
FLDH00 NUMBER(8) Y
FPLB00 VARCHAR2(2) Y
YWRQ00 DATE Y
YWRY00 VARCHAR2(8) Y
SHRQ00 DATE Y
XSQRRQ DATE Y
XSQRRY VARCHAR2(8) Y
KHDM00 VARCHAR2(12)
XKZH00 VARCHAR2(12)
CKDM00 VARCHAR2(2) Y
THCKDM VARCHAR2(2) Y
XSFSDM VARCHAR2(2) Y
FXRYDM VARCHAR2(4) Y
SHRYDM VARCHAR2(4) Y
SHBJ00 VARCHAR2(1) 'N'
FXBJ00 VARCHAR2(1) 'N'
SKBJ00 VARCHAR2(2) Y
FKDM00 VARCHAR2(2) Y
SQL> desc xstfxps2
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DHAO00 NUMBER(8)
SPDM00 VARCHAR2(8)
DJIA00 NUMBER(7,2) 0
FXSL00 NUMBER Y 0
SL0000 NUMBER Y 0
THSL00 NUMBER Y 0
JE0000 NUMBER Y 0
SE0000 NUMBER Y
FPBBH0 VARCHAR2(11) Y
FPHAO0 VARCHAR2(10) Y
RBDH00 NUMBER(8) Y
其中XSTFXPS1的客户订单的表头,保存订单的客户信息、订货日期等信息。XSTFXPS2是订单的表体,详细记录了客户订单的商品、价格、数量等信息。
调整的第一步是把子查询提取出来,再看语句的执行计划。通常来说,如果语句能够避免子查询的使用,就尽量不用子查询。因为子查询的开销是相当昂贵的。改写后的语句如下:
select sum(sl0000)
from xstfxps2 a,(select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate)
and khdm00='500000003913') b
where a.dhao00=b.dhao00;
已用时间: 00: 00: 03.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS2'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'XSTFXPS1'
5 3 INDEX (RANGE SCAN) OF 'XSTFXPS2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11974 consistent gets
225 physical reads
832 redo size
211 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们可以看到逻辑IO由原来的17355138次下降到11974次,有了数量级的提升。执行时间也有原来将近3分钟下降到现在的3秒多一些。很显然性能有了大幅的提升。不过我们看到执行计划里面表XSTFXPS1还是有一个全表扫描存在。通常来说我们应该尽量避免全表扫描的存在,尤其对于大表,应该建立合适的索引以避免FTS的产生。我们来看这两张表的索引信息:
select index_name,column_name from dba_ind_columns where table_name like 'XSTFXPS%'
INDEX_NAME COLUMN_NAME
------------------------------ -----------------------------------
XSTFXPS1_PK DHAO00
XSTFXPS2_PK DHAO00
XSTFXPS2_PK SPDM00
我们看到这两张表除了主键约束外都没有建另外的索引。根据语句的查询情况,我们建立了如下的复合索引:
create index idx_xstfxps1_khdm00_ywrq00 on xstfxps1(khdm00,ywrq00) tablespace indx;
为了使用索引,我们必须对原来的日期字段的条件进行一些调整。因为有个trunc()函数的存在,语句将不会使用到索引。我们只要明白trunc(ywrq00)=trunc(sysdate)事实上等同于ywrq00大于trunc(sysdate),小于trunc(sysdate+1)减去一秒,我们就有了比较好的办法来处理
这个条件。最终改写后的语句如下:
select sum(sl0000)
from xstfxps2 a, xstfxps1 b
where a.dhao00=b.dhao00
and b.khdm00='500000003913'
and b.ywrq00 between trunc(sysdate)
and trunc(sysdate)+1-1/(24*60*60);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS2'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'XSTFXPS1'
5 4 INDEX (RANGE SCAN) OF 'IDX_XSTFXPS1_KHDM00_YWRQ00'
(NON-UNIQUE)
6 3 INDEX (RANGE SCAN) OF 'XSTFXPS2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
210 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们这时候看逻辑IO已经降为3次,语句的执行计划也符合我们的调整目标,创建的索引产生了比较大的效果。这条语句的调整至此告一段落。

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



HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

"Usage of Division Operation in OracleSQL" In OracleSQL, division operation is one of the common mathematical operations. During data query and processing, division operations can help us calculate the ratio between fields or derive the logical relationship between specific values. This article will introduce the usage of division operation in OracleSQL and provide specific code examples. 1. Two ways of division operations in OracleSQL In OracleSQL, division operations can be performed in two different ways.

Oracle and DB2 are two commonly used relational database management systems, each of which has its own unique SQL syntax and characteristics. This article will compare and differ between the SQL syntax of Oracle and DB2, and provide specific code examples. Database connection In Oracle, use the following statement to connect to the database: CONNECTusername/password@database. In DB2, the statement to connect to the database is as follows: CONNECTTOdataba

Interpretation of MyBatis dynamic SQL tags: Detailed explanation of Set tag usage MyBatis is an excellent persistence layer framework. It provides a wealth of dynamic SQL tags and can flexibly construct database operation statements. Among them, the Set tag is used to generate the SET clause in the UPDATE statement, which is very commonly used in update operations. This article will explain in detail the usage of the Set tag in MyBatis and demonstrate its functionality through specific code examples. What is Set tag Set tag is used in MyBati

What is Identity in SQL? Specific code examples are needed. In SQL, Identity is a special data type used to generate auto-incrementing numbers. It is often used to uniquely identify each row of data in a table. The Identity column is often used in conjunction with the primary key column to ensure that each record has a unique identifier. This article will detail how to use Identity and some practical code examples. The basic way to use Identity is to use Identit when creating a table.

When Springboot+Mybatis-plus does not use SQL statements to perform multi-table adding operations, the problems I encountered are decomposed by simulating thinking in the test environment: Create a BrandDTO object with parameters to simulate passing parameters to the background. We all know that it is extremely difficult to perform multi-table operations in Mybatis-plus. If you do not use tools such as Mybatis-plus-join, you can only configure the corresponding Mapper.xml file and configure The smelly and long ResultMap, and then write the corresponding sql statement. Although this method seems cumbersome, it is highly flexible and allows us to

Solution: 1. Check whether the logged-in user has sufficient permissions to access or operate the database, and ensure that the user has the correct permissions; 2. Check whether the account of the SQL Server service has permission to access the specified file or folder, and ensure that the account Have sufficient permissions to read and write the file or folder; 3. Check whether the specified database file has been opened or locked by other processes, try to close or release the file, and rerun the query; 4. Try as administrator Run Management Studio as etc.

How to use SQL statements for data aggregation and statistics in MySQL? Data aggregation and statistics are very important steps when performing data analysis and statistics. As a powerful relational database management system, MySQL provides a wealth of aggregation and statistical functions, which can easily perform data aggregation and statistical operations. This article will introduce the method of using SQL statements to perform data aggregation and statistics in MySQL, and provide specific code examples. 1. Use the COUNT function for counting. The COUNT function is the most commonly used
