代码之家 › 专栏 › 技术社区 › Ken Chan

关于“ORDER BY”和“LIKE”子句的性能调整

plsql oracle performance

Ken Chan · 技术社区 · 14 年前

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_table_stringField是table.srv_ref字段的索引名。我在没有提示的情况下再次运行explain计划,explain计划仍然得到相同的结果。

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")

8 回复 | 直到 14 年前

Dave Costa 14 年前

你说运行查询大约需要4分钟。EXPLAIN PLAN输出显示估计为18秒。因此,在这种情况下,优化器可能与它的某些估计值相差甚远。(它仍然可以选择最好的方案,但可能不会。)

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

如果没有这些信息,我的建议是尝试对查询进行以下重写。我相信它是等价的,因为ID似乎是table b的主键。

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

Lord Peter 14 年前

user328543 14 年前

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

baklarz2048 14 年前

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。

HamoriZ 14 年前

如果您有足够的内存,可以提示查询使用哈希联接。请附上解释计划好吗

Jane T 14 年前

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

一个很好的实验是删除连接并测试其速度,如果允许的话,还可以将rownum<201作为and子句放在主查询上。很可能此时查询正在将所有行返回到外部查询,然后它正在被修剪?

Alex Armijos 11 年前

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

BenMorel Manish Pradhan 11 年前

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

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

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