Oracle SQL 查询运行缓慢，主键上的全表扫描，为什么

Question

我在一段代码上遇到了问题，我不明白为什么下面的查询在wrk.cre_surr_id是主键时对工作表进行全表扫描。两个表的统计信息都是最新的，以下是两个表上的索引。

 TABLE INDEXES
 WORKS
 INDEX NAME         UNIQUE  LOGGING     COLUMN NAME             ORDER
 WRK_I1             N       NO          LOGICALLY_DELETED_Y     Asc
 WRK_ICE_WRK_KEY    N       YES         ICE_WRK_KEY             Asc
 WRK_PK             Y       NO          CRE_SURR_ID             Asc
 WRK_TUNECODE_UK    Y       NO          TUNECODE                Asc
TLE_TITLE_TOKENS
INDEX NAME          UNIQUE  LOGGING     COLUMN NAME             ORDER
TTT_I1              N       YES         TOKEN_TYPE,             Asc
                                        SEARCH_TOKEN,
                                        DN_WRK_CRE_SURR_ID
TTT_TLE_FK_1        N       YES         TLE_SURR_ID

下面的问题查询。它的成本为 245,876，这似乎很高，它正在对表中有 21,938,384 行的 WORKS 表进行全表扫描。它正在对包含 19,923,002 行的 TLE_TITLE_TOKENS 表进行索引范围扫描。在解释计划上还有一个 INLIST 迭代器，我不知道它是什么意思，但我认为这与在我的 sql 查询中有一个"in （'E'"、"N'）"有关。

 SELECT wrk.cre_surr_id 
 FROM   works wrk, 
        tle_title_tokens ttt      
 WHERE ttt.dn_wrk_cre_surr_id = wrk.cre_surr_id
 AND wrk.logically_deleted_y IS NULL   
 AND ttt.token_type in ('E','N')  
 AND  ttt.search_token LIKE 'BELIEVE'||'%'

当我分解查询并从TLE_TITLE_TOKENS表中进行简单选择时，我得到了 280,000 条记录。

 select ttt.dn_wrk_cre_surr_id 
 from tle_title_tokens ttt
 where ttt.token_type in ('E','N') 
 and ttt.search_token LIKE 'BELIEVE'||'%'

如何停止它在工作表上进行全表扫描。我可以在查询上给出提示，但我认为 Oracle 会足够聪明，知道在没有提示的情况下使用索引。

同样在TLE_TITLE_TOKENS表上，最好在列SEARCH_TOKEN上创建一个基于功能的索引，因为用户似乎在此字段上进行 LIKE % 搜索。基于功能的索引会是什么样子。

我正在运行 Oracle 11g 数据库。

提前感谢任何答案。

Answer 1

首先，使用 join 重写查询：

SELECT wrk.cre_surr_id 
FROM tle_title_tokens ttt JOIN
     works wrk 
     ON ttt.dn_wrk_cre_surr_id = wrk.cre_surr_id 
WHERE wrk.logically_deleted_y IS NULL  AND
      ttt.token_type in ('E', 'N')  AND
      ttt.search_token LIKE 'BELIEVE'||'%';

您应该能够使用索引加快此查询的速度。 目前尚不清楚最佳指数是什么。 我建议要么tle_title_tokens(search_token, toekn_type, dn_wrk_cre_surr_id)，要么works(cre_surr_id, logically_deleted_y).

另一种可能性是使用 EXISTS 编写查询，例如：

SELECT wrk.cre_surr_id 
FROM works wrk 
WHERE wrk.logically_deleted_y IS NULL AND
      EXISTS (SELECT 1
              FROM tle_title_tokens ttt
              WHERE ttt.dn_wrk_cre_surr_id = wrk.cre_surr_id AND
                    ttt.token_type IN ('N', 'E') AND
                    ttt.search_token LIKE 'BELIEVE'||'%'
             ) ;

对于此版本，您需要在 works(logically_deleted_y, cre_surr_id) 和 tle_title_tokens(dn_wrk_cre_surr_id, token_type, search_token) 上建立索引。

Answer 2

试试这个：

SELECT /*+ leading(ttt) */ wrk.cre_surr_id 
 FROM   works wrk, 
        tle_title_tokens ttt      
 WHERE ttt.dn_wrk_cre_surr_id = wrk.cre_surr_id
 AND wrk.logically_deleted_y IS NULL   
 AND ttt.token_type in ('E','N')  
 AND  ttt.search_token LIKE 'BELIEVE'||'%'

Answer 3

在 LE_TITLE_TOKENS 的 19,923,002 行中，

有多少条记录TOKEN_TYPE"E"，有多少记录"N"？还有其他令牌类型吗？如果是，那么它们放在一起有多少？

如果 E 和 N 放在一起构成了总记录的一小部分，则检查该列的直方图统计信息是否已更新。

执行计划取决于从给定筛选器的 20M 条记录LE_TITLE_TOKENS中选择的记录数。

Answer 4

我假设这个索引定义

 create index works_idx on works (cre_surr_id,logically_deleted_y);
 create index title_tokens_idx on  tle_title_tokens(search_token,token_type,dn_wrk_cre_surr_id); 

通常有两种可能的方案来执行联接

使用索引访问内部表的嵌套循环WORKS，但对外部表中的每一行重复循环

哈希联接，使用完全扫描访问WORKS，但只能访问一次。

不可能说一种选择是坏的，另一种是好的。

如果外部表中

只有几行（很少循环），则嵌套循环更好，但随着外部表中记录数量的增加（TOKEN）会变慢并且速度较慢，并且在一定数量的行中，哈希连接是中间的。

如何看什么执行计划更好？ 简单强制 Oracle 使用提示运行两个 scanarios 并比较经过的时间。

在您的情况下，您应该看到这两个执行计划

哈希联接

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |   207K|    10M|       |  2439   (1)| 00:00:30 |
|*  1 |  HASH JOIN         |                  |   207K|    10M|  7488K|  2439   (1)| 00:00:30 |
|*  2 |   INDEX RANGE SCAN | TITLE_TOKENS_IDX |   207K|  5058K|       |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| WORKS            |   893K|    22M|       |   431   (2)| 00:00:06 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TTT"."DN_WRK_CRE_SURR_ID"="WRK"."CRE_SURR_ID")
   2 - access("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%')
       filter("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%' AND ("TTT"."TOKEN_TYPE"='E' OR 
              "TTT"."TOKEN_TYPE"='N'))
   3 - filter("WRK"."LOGICALLY_DELETED_Y" IS NULL)

嵌套循环

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   207K|    10M|   414K  (1)| 01:22:56 |
|   1 |  NESTED LOOPS     |                  |   207K|    10M|   414K  (1)| 01:22:56 |
|*  2 |   INDEX RANGE SCAN| TITLE_TOKENS_IDX |   207K|  5058K|    29   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| WORKS_IDX        |     1 |    26 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%')
       filter("TTT"."SEARCH_TOKEN" LIKE 'BELIEVE%' AND 
              ("TTT"."TOKEN_TYPE"='E' OR "TTT"."TOKEN_TYPE"='N'))
   3 - access("TTT"."DN_WRK_CRE_SURR_ID"="WRK"."CRE_SURR_ID" AND 
              "WRK"."LOGICALLY_DELETED_Y" IS NULL)

我的问题是（具有 280K 循环）哈希连接（即 FULLTABLE SCAN）将是中间的，但可能是您认识到应该使用嵌套循环。在这种情况下，优化无法正确识别嵌套循环和哈希连接之间的切换点。造成这种情况的常见原因是系统统计信息错误或缺失，或者优化器参数不正确。