求提高substr(a.name,1,length(b.name))=b.name性能

xiaoe_7

精妙 anlinew

smithton

学习

qqjue

这样也能优化，把比较的数据分成两部分比较就优化了，还是想不通，我想这个不是hash  join的问题.你可以在原来的查询上强行走hash join　试试.

rollingpig

很强！！

原帖由 anlinew 于 2010-12-9 19:53 发表
SQL> select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2  where substr(a.object_name,1,length(b.object_name))=b.object_name
  3  and substr(a.object_name,1,4)=substr(b.object_name,1,4)  4  and length(b.object_name)>3
  5  union all
  6  select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  7  where substr(a.object_name,1,length(b.object_name))=b.object_name
  8  and length(b.object_name)3)
   6 - filter(LENGTH("B"."OBJECT_NAME") select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2  where substr(a.object_name,1,length(b.object_name))=b.object_name;

已选择108612行。

已用时间:  00: 06: 51.24
执行计划
----------------------------------------------------------
Plan hash value: 661671976

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             | 68676 |  7243K|  1745K  (1)| 05:49:05 |
|   1 |  NESTED LOOPS      |             | 68676 |  7243K|  1745K  (1)| 05:49:05 |
|   2 |   TABLE ACCESS FULL| TEST_OBJECT | 52544 |  4566K|   139   (1)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| TEST_OBJ1   |     1 |    19 |    33   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJ
              ECT_NAME")))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    9262734  consistent gets
          0  physical reads
          0  redo size
    4408357  bytes sent via SQL*Net to client
      80055  bytes received via SQL*Net from client
       7242  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     108612  rows processed

qqjue

select count(*) from (
select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
      where instr(a.object_name,b.object_name)=1
      and substr(a.object_name,1,4)=substr(b.object_name,1,4)
      and length(b.object_name)>3
      union all
      select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
      where substr(a.object_name,1,length(b.object_name))=b.object_name
      and length(b.object_name)<4);

改成这样，我机上从33秒减到17秒

qqjue

用or　就行的
select count(*) from (
select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
      where (instr(a.object_name,b.object_name)=1
      and substr(a.object_name,1,6)=substr(b.object_name,1,6)
      and length(b.object_name)>=6)
      or
      (instr(a.object_name,b.object_name)=1
      and length(b.object_name)<6));

sj198421

学习

shahand

进来学习+收藏

lastwinner

anlinew的方法真不错

killkill_shadow

精妙啊，使用条件
and substr(a.object_name,1,4)=substr(b.object_name,1,4)  打破了 substr(a.object_name,1,length(b.object_name))=b.object_name 这种“相关型”匹配的“魔咒”。
Mark，整理一下就写进blog。