sql - Oracle top-n 查询排序性能-6ren

sql - Oracle top-n 查询排序性能

转载作者：行者123 更新时间：2023-12-02 00:40:56

我是 sql 优化的新手，我试图理解为什么 IN 子句中包含超过 1 个项目会导致巨大的性能损失，以及如果可能的话如何防止它。以下或多或少是我正在处理的内容。第二个查询是我当前所拥有的，我希望提高性能。在现实生活中，TABLE_1 有数百万行，计划的排序部分的 CPU 成本为 21M。

SELECT 
    TOPNWRAPPER.*, 
    TABLE_2.X, 
    TABLE_2.Y 
FROM 
    TABLE_2, 
    ( 
        SELECT 
            * 
        FROM 
            ( 
                SELECT 
                    /*+ index (TABLE_1 TABLE_1_B_E_F_ID) */ 
                    TABLE_1.ID, 
                    TABLE_1.C, 
                    TABLE_1.B, 
                    TABLE_1.E, 
                    TABLE_1.F
                FROM 
                    TABLE_1 
                WHERE 
                    ( TABLE_1.F IN ( ‘STATE1’ ) ) AND 
                    ( TABLE_1.B= 'SOMETEXT' ) AND 
                    ( TABLE_1.C=1 ) AND 
                    ( TABLE_1.E= 'IN' ) AND 
                    ( TABLE_1.D IS NULL ) 
                ORDER BY 
                    TABLE_1.ID 
            ) 
        WHERE 
            ( ROWNUM <= 150 ) 
    ) TOPNWRAPPER 
WHERE 
    ( TOPNWRAPPER.ID = TABLE_2.T1_ID_FK ) 
ORDER BY 
    TOPNWRAPPER.ID ASC

统计数据:

|--------------------------------------------------------------------------------------------------------------------------|
|| Id  | Operation                        | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers ||
|--------------------------------------------------------------------------------------------------------------------------|
||   0 ||SELECT STATEMENT                 |                             |      1 |        |    120 |00:00:00.01 |     965 ||
||   1 |||NESTED LOOPS                    |                             |      1 |        |    120 |00:00:00.01 |     965 ||
||   2 ||||NESTED LOOPS                   |                             |      1 |      1 |    120 |00:00:00.01 |     845 ||
||   3 |||||VIEW                          |                             |      1 |      1 |    120 |00:00:00.01 |     245 ||
||*  4 ||||||COUNT STOPKEY                |                             |      1 |        |    120 |00:00:00.01 |     245 ||
||   5 |||||||VIEW                        |                             |      1 |      1 |    120 |00:00:00.01 |     245 ||
||*  6 ||||||||TABLE ACCESS BY INDEX ROWID| TABLE_1                     |      1 |      1 |    120 |00:00:00.01 |     245 ||
||*  7 |||||||||INDEX RANGE SCAN          | TABLE_1_B_E_F_ID            |      1 |     25 |    120 |00:00:00.01 |     125 ||
||*  8 |||||INDEX RANGE SCAN              | TABLE_2_T1_ID_FK            |    120 |      1 |    120 |00:00:00.01 |     600 ||
||   9 ||||TABLE ACCESS BY INDEX ROWID    | TABLE_2                     |    120 |      1 |    120 |00:00:00.01 |     120 ||
|--------------------------------------------------------------------------------------------------------------------------|
|                                                                                                                          |
|Predicate Information (identified by operation id):                                                                       |
|---------------------------------------------------                                                                       |
|                                                                                                                          |
|   4 - filter(ROWNUM<=150)                                                                                                |
|   6 - filter((“TABLE_1”.”C”=1 AND “TABLE_1”.”D” IS NULL))                                                                |
|   7 - access(“TABLE_1”.”B”='SOMETEXT' AND                                                                                |
|              “TABLE_1”.”E”=‘IN' AND “TABLE_1”.”F”=’STATE1’)                                                              |
|   8 - access(“TOPNWRAPPER”.”ID”=“TABLE_2”.”T1_ID_FK”)                                                                         |
+--------------------------------------------------------------------------------------------------------------------------+

当我更新查询以在 IN 子句中包含“STATE2”时，计划中会添加一个额外的排序步骤。

SELECT 
    TOPNWRAPPER.*, 
    TABLE_2.X, 
    TABLE_2.Y 
FROM 
    TABLE_2, 
    ( 
        SELECT 
            * 
        FROM 
            ( 
                SELECT 
                    /*+ index (TABLE_1 TABLE_1_B_E_F_ID) */ 
                    TABLE_1.ID, 
                    TABLE_1.C, 
                    TABLE_1.B, 
                    TABLE_1.E, 
                    TABLE_1.F
                FROM 
                    TABLE_1 
                WHERE 
                    ( TABLE_1.F IN ( 'STATE1', 'STATE2' ) ) AND 
                    ( TABLE_1.B= 'SOMETEXT' ) AND 
                    ( TABLE_1.C=1 ) AND 
                    ( TABLE_1.E= 'IN' ) AND 
                    ( TABLE_1.D IS NULL ) 
                ORDER BY 
                    TABLE_1.ID 
            ) 
        WHERE 
            ( ROWNUM <= 150 ) 
    ) TOPNWRAPPER 
WHERE 
    ( TOPNWRAPPER.ID = TABLE_2.T1_ID_FK ) 
ORDER BY 
    TOPNWRAPPER.ID ASC

统计数据:

|-------------------------------------------------------------------------------------------------------------------------------------------------------|
|| Id  | Operation                          | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem ||
|-------------------------------------------------------------------------------------------------------------------------------------------------------|
||   0 ||SELECT STATEMENT                   |                             |      1 |        |    150 |00:00:00.01 |    1076 |       |       |          ||
||   1 |||NESTED LOOPS                      |                             |      1 |        |    150 |00:00:00.01 |    1076 |       |       |          ||
||   2 ||||NESTED LOOPS                     |                             |      1 |      1 |    150 |00:00:00.01 |     926 |       |       |          ||
||   3 |||||VIEW                            |                             |      1 |      1 |    150 |00:00:00.01 |     176 |       |       |          ||
||*  4 ||||||COUNT STOPKEY                  |                             |      1 |        |    150 |00:00:00.01 |     176 |       |       |          ||
||   5 |||||||VIEW                          |                             |      1 |      1 |    150 |00:00:00.01 |     176 |       |       |          ||
||*  6 ||||||||SORT ORDER BY STOPKEY        |                             |      1 |      1 |    150 |00:00:00.01 |     176 | 15360 | 15360 |14336  (0)||
||   7 |||||||||INLIST ITERATOR             |                             |      1 |        |    165 |00:00:00.01 |     176 |       |       |          ||
||*  8 ||||||||||TABLE ACCESS BY INDEX ROWID| TABLE_1                     |      2 |      1 |    165 |00:00:00.01 |     176 |       |       |          ||
||*  9 |||||||||||INDEX RANGE SCAN          | TABLE_1_B_E_F_ID            |      2 |     50 |    165 |00:00:00.01 |      11 |       |       |          ||
||* 10 |||||INDEX RANGE SCAN                | TABLE_2_T1_ID_FK            |    150 |      1 |    150 |00:00:00.01 |     750 |       |       |          ||
||  11 ||||TABLE ACCESS BY INDEX ROWID      | TABLE_2                     |    150 |      1 |    150 |00:00:00.01 |     150 |       |       |          ||
|-------------------------------------------------------------------------------------------------------------------------------------------------------|
|                                                                                                                                                       |
|Predicate Information (identified by operation id):                                                                                                    |
|---------------------------------------------------                                                                                                    |
|                                                                                                                                                       |
|   4 - filter(ROWNUM<=150)                                                                                                                             |
|   6 - filter(ROWNUM<=150)                                                                                                                             |
|   8 - filter((“TABLE_1”.”C”=1 AND “TABLE_1”.”D” IS NULL))                                         |
|   9 - access(“TABLE_1”.”B”='SOMETEXT' AND                                                                                |
|              “TABLE_1”.”E”='IN' AND ((“TABLE_1”.”F”='STATE1') OR (“TABLE_1”.”F”='STATE2'))                                              |
|  10 - access(“TOPNWRAPPER”.”ID”=“TABLE_2”.”T1_ID_FK”)                                                              |
|                                                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+

这几天我一直在四处寻找。我尝试的一个建议是使用提示 /*+ USE_CONCAT (OR_PREDICATES(1)) */ ，这有助于将内存使用量减少一半，但它并没有完全消除问题。

编辑:环顾四周( http://use-the-index-luke.com/sql/sorting-grouping/indexed-order-by#tip-ixord-full )并认为这可能是由于 order by 造成的。如果我将 order by 语句更改为: TABLE_1.F,TABLE_1.ID 和 TOPNWRAPPER.F,TOPNWRAPPER.ID ASC 那么排序操作就会消失，不幸的是我需要基于 ID 的前 n 行。或者，我尝试在 (ID F) 上创建一个新索引来进行测试，它也删除了排序操作，但 ID 每行都是唯一的，这使得表访问操作的效率降低。

编辑2:

OPERATION      |OPTION           |CPU COST
--------------------------------------------
SORT           |ORDER BY STOPKEY |21042774
|NESTED LOOPS  |OUTER            |56052
||TABLE ACCESS |BY INDEX ROWID   |38980
|||INDEX       |RANGE SCAN       |30086

最佳答案

性能差异可能并不重要。执行计划的差异是因为如果前导列使用相等条件，则多列索引访问只会隐式排序。

性能差异

不要太担心执行计划的成本。尽管它被称为“基于成本的优化器”，但成本是一个奇怪的数字，世界上只有少数人完全理解。

比较解释计划成本很复杂的一个原因是总成本有时小于子操作成本之一。正如我在my answer here中解释的那样，这可以通过 COUNT STOPKEY 操作发生。这是 Oracle 的说法，“这个子操作将花费如此巨大的费用，但是 COUNT STOPKEY 可能会在它达到那么高之前将其切断”。通常最好比较计划的最高成本，但即使是这个数字有时也可能会产生误导，正如该答案中的其他示例所示。

这意味着通常运行时间是唯一重要的事情。如果两个查询的 A-Time(实际时间)仅为 0.1 秒，那么您的工作可能已经完成。

执行计划差异

执行计划的差异是由多列索引的存储和访问方式造成的。有时扫描索引时，结果会自动存储，有时则不会。这就是为什么一个计划具有 COUNT STOPKEY 而另一个计划具有更昂贵的 SORT ORDER BY STOPKEY。

为了演示此计划差异，请创建一个仅包含 2 列和 4 行的简单表和索引:

create table test1 as 
select 1 a, 10 b from dual union all
select 1 a, 30 b from dual union all
select 2 a, 20 b from dual union all
select 2 a, 40 b from dual;

create index test1_idx on test1(a, b);

begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/

下面是索引存储方式的简化思路。数据首先按前导列排序存储，然后按尾随列排序。

               +----+
        +------+Root+-------+
        |      +----+       |
        |                   |
      +-v-+               +-v-+
   +--+A=1+--+         +--+A=2+--+
   |  +---+  |         |  +---+  |
   |         |         |         |
 +-v--+   +--v-+     +-v--+   +--v-+
 |B=10|   |B=30|     |B=20|   |B=40|
 +----+   +----+     +----+   +----+

如果查询仅访问前导列 A 中的一个值，那么它可以按顺序读取列 B 中的值，而无需任何额外的工作。 Oracle 会先读取 A block 之一，然后按顺序读取 B block ，甚至无需尝试。

请注意此查询如何具有 ORDER BY，但执行计划中没有 SORT。

explain plan for select * from test1 where a = 1 and b > 0 order by b;
select * from table(dbms_xplan.display(format => 'basic'));

Plan hash value: 598212486

--------------------------------------
| Id  | Operation        | Name      |
--------------------------------------
|   0 | SELECT STATEMENT |           |
|   1 |  INDEX RANGE SCAN| TEST1_IDX |
--------------------------------------

但是，如果查询访问前导列 A 中的多个值，则不一定会按顺序检索 B 中的结果。 Oracle 可以按顺序读取 A block ，但 B block 顺序仅适用于一个 A 值。

现在，执行计划中会显示一个额外的 SORT ORDER BY 操作。

explain plan for select * from test1 where a in (1,2) and b > 0 order by b;
select * from table(dbms_xplan.display(format => 'basic'));

Plan hash value: 704117715

----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  SORT ORDER BY     |           |
|   2 |   INLIST ITERATOR  |           |
|   3 |    INDEX RANGE SCAN| TEST1_IDX |
----------------------------------------

这就是为什么将 (value1, value2) 中的 column1 = value1 更改为 column1 可能会添加额外的 SORT 操作。

关于sql - Oracle top-n 查询排序性能，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39135808/

文章推荐： java - Rest url 中查询参数的日期时间格式

文章推荐：允许对成员进行数字操作的 scala 类型签名

文章推荐： c++ - 三元运算符的意外结果

sql - SQL、PL-SQL 和 T-SQL 之间有什么区别？
SQL、PL-SQL 和 T-SQL 之间有什么区别？谁能解释一下这三者之间的区别，并提供每一个的相关使用场景？最佳答案 SQL 是一种对集合进行操作的查询语言。它或多或少是标准化的，几乎所有关
sql - T-SQL、SQL Server 和 SQL 有什么区别
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。我对 SQL 的了解足以完成我的
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列有一个默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任
sql - 在字符串中查找第二组数字(SQL/PL-SQL)
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
sql - 服务器端 sql 与客户端 sql
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 9年前关闭
sql - sql 如何计算 sql 存储过程中的附加表？
我有一个存储过程(称为 sprocGetArticles)，它从文章表中返回文章列表。这个存储过程没有任何参数。用户可以对每篇文章发表评论，我将这些评论存储在由文章 ID 链接的评论表中。有什么方
sql - 嵌入式 SQL 与动态 SQL
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。我自己做了一些数据库工作
sql - SQL Server SQL 语句可以有多少个字符？
SQL Server 中 SQL 语句的最大长度是多少？这个长度是否取决于 SQL Server 的版本？例如，在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
sql-server - SQL 行到列 sql
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
sql - SQL Server SQL 语句中的动态日期
预先感谢您对此提供的任何帮助。假设我有一个查询，可以比较跨年的数据，从某个任意年份开始，永无止境(进入 future )，每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
sql - Linq To Sql - SQL 默认约束问题
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列，该列的默认约束为 GETDATE()。使用 LINQ 时，我没有为 RegistrationDate 列提供任何数
sql - (SQL Server) SQL 不允许在检查过程是否存在后创建过程
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
sql - 动态 SQL 是否比 SQL Server 中的静态 SQL 性能更高？
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快，所以我经常看到 DSQL 到处都是。除了明显的缺点，比如在运行之前无法检测到错误并且更难阅读，这是否准确？当我问他为什么一直使用
sql - exec sp_executesql @sql 和 exec (@sql) SQL Server
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
sql - 动态 SQL - EXEC(@SQL) 与 EXEC SP_EXECUTESQL(@SQL)
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ？最佳答案 sp_executes
c# - SQL > Linq to Sql，SQL 查询有效，Linq to SQL 返回空数据集
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
sql-server - "> sql.txt && sql -h-1 -i sql.txt && del sql.txt"命令是什么意思？
我正在调试一些构建成功运行的 SQL 命令的代码。然而，在查询结束时，查询结果似乎被写入了一个文本文件。完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
sql - 如何从 MS SQL 数据库(Microsoft SQL Server)中的其他 .sql 文件运行 .sql 文件？
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
sql - 如何在查询中使用 SQL 变量(SQL Server)？
我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
sql - 如何将两个列表转换为邻接矩阵 SQL Server T-SQL？
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示 Fruit Colour Apple Red Orange

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - Oracle top-n 查询排序性能