关于“ORDER BY”的性能调整和“喜欢”条款

Question

我有2个表，其中有很多记录（假设TableA和TableB都有大约3,000,000条记录）。vr2_input是用户输入的varchar输入参数，我想获取最多200个最大的“dateField”的TableA记录，其stringField像' vr2_input' 。这两个表的连接如下：

select * from(   
   select * from 
        TableA join TableB on TableA.id = TableB.id
        where  TableA.stringField like 'vr2_input' || '%'
        order by  TableA.dateField desc   
) where rownum < 201

查询很慢，我仔细研究了一下，发现这是因为“like”和“order by”涉及全表扫描。但是，我找不到解决方案问题 。我该如何调优这种类型的 SQL？我已经在 TableA.stringField 和 TableA.dateField 上创建了索引，但是如何在 select 语句中使用索引功能？数据库是oracle 10g。非常感谢！！

更新：我使用 iddqd 的建议，只选择我想要的字段并运行解释计划。完成查询大约需要4分钟。 IX_TableA_stringField 是 TableA.srv_ref 字段的索引名称。我在没有提示的情况下再次运行解释计划，解释计划仍然得到相同的结果。

EXPLAIN PLAN FOR
    select * from(
         select   
                 /*+ INDEX(TableB IX_TableA_stringField)*/ 
                  TableA.id,
                    TableA.stringField,
                    TableA.dateField,
                    TableA.someField2,
                   TableA.someField3,
            TableB.someField1,
            TableB.someField2,
            TableB.someField3,
                    from TableA 
                    join TableB  on  TableA.id=TableB.id
                    WHERE TableA.stringField  like '21'||'%'  
                 order by TableA.dateField  desc
        ) where rownum < 201

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 871807846                                                                                                                                                                                                                                                                    

--------------------------------------------------------------------------------------------------------                                                                                                                                                                           
| Id  | Operation                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                      
--------------------------------------------------------------------------------------------------------                                                                                                                                                                           
|   0 | SELECT STATEMENT                |                      |   200 | 24000 |  3293   (1)| 00:00:18 |                                                                                                                                                                   
|*  1 |  COUNT STOPKEY                  |                      |       |       |            |          |                                                                                                                                                                                   
|   2 |   VIEW                          |                      |  1397 |   163K|  3293   (1)| 00:00:18 |                                                                                                                                                                             
|*  3 |    SORT ORDER BY STOPKEY        |                      |  1397 | 90805 |  3293   (1)| 00:00:18 |                                                                                                                                                              
|   4 |     NESTED LOOPS                |                      |  1397 | 90805 |  3292   (1)| 00:00:18 |                                                                                                                                                                      
|   5 |      TABLE ACCESS BY INDEX ROWID| TableA       |  1397 | 41910 |   492   (1)| 00:00:03 |                                                                                                                                                 
|*  6 |       INDEX RANGE SCAN          | IX_TableA_stringField |  1397 |       |     6   (0)| 00:00:01 |                                                                                                                                                         
|   7 |      TABLE ACCESS BY INDEX ROWID| TableB      |     1 |    35 |     2   (0)| 00:00:01 |                                                                                                                                                      
|*  8 |       INDEX UNIQUE SCAN         | PK_TableB   |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                            
--------------------------------------------------------------------------------------------------------                                                                                                                                                                           

Predicate Information (identified by operation id):                                                                                                                                                                                                                                      
---------------------------------------------------                                                                                                                                                                                                                                             

   1 - filter(ROWNUM<201)                                                                                                                                                                                                                                                                      
   3 - filter(ROWNUM<201)                                                                                                                                                                                                                                                                      
   6 - access("TableA"."stringField" LIKE '21%')                                                                                                                                                                                                                                                 
       filter("TableA"."stringField" LIKE '21%')                                                                                                                                                                                                                                                     
   8 - access(TableA"."id"="TableB"."id")       

Answer 1

您说运行查询大约需要 4 分钟。 EXPLAIN PLAN 输出显示估计时间为 18 秒。因此，在这种情况下，优化器可能与它的一些估计相差甚远。 （它仍然可能选择最好的计划，但也可能不是。）

在这种情况下，第一步是获取实际的执行计划和统计数据。使用提示 /*+ Gather_plan_statistics */ 运行查询，然后立即执行 select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')) 。

这将显示已运行的实际执行计划，并且对于每个步骤，它将显示估计行数、实际行数和实际花费的时间。在此处发布输出，也许我们可以对您的问题说一些更有意义的内容。

如果没有这些信息，我的建议是尝试以下查询重写。我相信它是等效的，因为 ID 似乎是 TableB 的主键。

select TableA.id,
       TableA.stringField,
       TableA.dateField,
       TableA.someField2,
       TableA.someField3,
       TableB.someField1,
       TableB.someField2,
       TableB.someField3,
  from (select * from(
         select   
                  TableA.id,
                    TableA.stringField,
                    TableA.dateField,
                    TableA.someField2,
                    TableA.someField3,
                    from TableA 
                    WHERE TableA.stringField  like '21'||'%'  
                 order by TableA.dateField  desc
          )
          where rownum < 201
       ) TableA
       join TableB  on  TableA.id=TableB.id

Answer 2

是否需要选择所有列 (*)？如果您选择所有列，优化器将更有可能进行全面扫描。如果您需要输出中的所有列，您最好在内联视图中选择 id，然后返回选择其他列，这可以通过索引查找来完成。尝试对这两种情况运行解释计划以查看优化器正在做什么。

Answer 3

在 stringField 和 dateField 列上创建索引。 SQL 引擎自动使用它们。

Answer 4

select id from(   
   select /*+ INDEX(TableB stringField_indx)*/ TableB.id from 
        TableA join TableB on TableA.id = TableB.id
        where  TableA.stringField like 'vr2_input' || '%'
        order by  TableA.dateField desc   
) where rownum < 201

next:

SELECT * FROM TableB WHERE id iN( id from first query)

请发送该表的统计数据和 DDL。

Answer 5

如果您有足够的内存，您可以提示查询使用散列连接。请附上解释计划好吗

Answer 6

如果表 A 是较小的表，则表 A 有多少条记录，您可以在该表上进行选择，然后循环检索表 B 记录的结果，因为选择和排序都在表 A 上。

一个很好的实验是删除连接并测试速度，如果允许，您可以将 rownum < 201 作为主查询上的 AND 子句。目前查询很可能将所有行返回到外部查询，然后它被修剪？

Answer 7

要优化 like 谓词，您可以创建上下文索引并使用 contains 子句。

请看： http://docs.oracle.com/cd/B28359_01 /text.111/b28303/ind.htm

谢谢

Answer 8

您可以在 tableA 上创建一个函数索引。这将根据 TableA.stringField 的条件返回 1 或 0，如 'vr2_input' || '%' 是否满足。该索引将使查询运行得更快。该函数的逻辑将是

if (substr(TableA.stringField, 1, 9) = 'vr2_input'
THEN 
    return 1;
else 
    return 0;

使用实际的列名称而不是“*”可能会有所帮助。至少应该删除常见的列名称。