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

yaonc

真真见识了

Ora-600

按钮，截取长度为什么要用4？
如果把取的长度缩短，速度会更快：
已写入 file afiedt.buf

  1  select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2        where instr(a.object_name,b.object_name)=1
  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)<4
SQL> /

已选择6843行。

已用时间:  00: 00: 01.01

执行计划
----------------------------------------------------------
Plan hash value: 4080738151

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  2723 |   247K|  4613  (96)| 00:00:56 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|*  2 |   HASH JOIN         |             |   139 | 12927 |   191   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
|   5 |   NESTED LOOPS      |             |  2584 |   234K|  4422   (1)| 00:00:54 |
|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL| TEST_OBJECT |    79 |  6873 |   132   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   2 - access(SUBSTR("A"."OBJECT_NAME",1,4)=SUBSTR("B"."OBJECT_NAME",1,4))
       filter(INSTR("A"."OBJECT_NAME","B"."OBJECT_NAME")=1)
   3 - filter(LENGTH("B"."OBJECT_NAME")>3)
   6 - filter(LENGTH("B"."OBJECT_NAME")<4)
   7 - filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJE
              CT_NAME")))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      29033  consistent gets
          0  physical reads
          0  redo size
     235626  bytes sent via SQL*Net to client
       5401  bytes received via SQL*Net from client
        458  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       6843  rows processed

SQL> ed
已写入 file afiedt.buf

  1  select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2        where instr(a.object_name,b.object_name)=1
  3        and substr(a.object_name,1,2)=substr(b.object_name,1,2)
  4        and length(b.object_name)>1
  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)<2
SQL> /

已选择6843行。

已用时间:  00: 00: 00.29

执行计划
----------------------------------------------------------
Plan hash value: 4080738151

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  2723 |   247K|  4613  (96)| 00:00:56 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|*  2 |   HASH JOIN         |             |   139 | 12927 |   191   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
|   5 |   NESTED LOOPS      |             |  2584 |   234K|  4422   (1)| 00:00:54 |
|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL| TEST_OBJECT |    79 |  6873 |   132   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   2 - access(SUBSTR("A"."OBJECT_NAME",1,2)=SUBSTR("B"."OBJECT_NAME",1,2))
       filter(INSTR("A"."OBJECT_NAME","B"."OBJECT_NAME")=1)
   3 - filter(LENGTH("B"."OBJECT_NAME")>1)
   6 - filter(LENGTH("B"."OBJECT_NAME")<2)
   7 - filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJE
              CT_NAME")))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2689  consistent gets
          0  physical reads
          0  redo size
     225478  bytes sent via SQL*Net to client
       5401  bytes received via SQL*Net from client
        458  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       6843  rows processed

SQL> ed
已写入 file afiedt.buf

  1  select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2        where instr(a.object_name,b.object_name)=1
  3        and substr(a.object_name,1,2)=substr(b.object_name,1,2)
  4        and length(b.object_name)>1
  5        union all
  6        select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  7        where substr(a.object_name,1,1)=b.object_name
  8*       and length(b.object_name)=1
SQL> /

已选择6843行。

已用时间:  00: 00: 00.26

执行计划
----------------------------------------------------------
Plan hash value: 1025998422

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   656 | 61008 |   382  (51)| 00:00:05 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|*  2 |   HASH JOIN         |             |   139 | 12927 |   191   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
|*  5 |   HASH JOIN         |             |   517 | 48081 |   191   (2)| 00:00:03 |
|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |     7 |    42 |    56   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   2 - access(SUBSTR("A"."OBJECT_NAME",1,2)=SUBSTR("B"."OBJECT_NAME",1,2))
       filter(INSTR("A"."OBJECT_NAME","B"."OBJECT_NAME")=1)
   3 - filter(LENGTH("B"."OBJECT_NAME")>1)
   5 - access("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,1))
   6 - filter(LENGTH("B"."OBJECT_NAME")=1)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2106  consistent gets
          0  physical reads
          0  redo size
     224711  bytes sent via SQL*Net to client
       5401  bytes received via SQL*Net from client
        458  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       6843  rows processed

rollingpig

这是个经验值，有些时候，如果substr取得太短，就可能hash 碰撞太多，性能反而差。
很难说，得根据实际情况来判断。

原帖由 Ora-600 于 2011-4-19 17:10 发表
按钮，截取长度为什么要用4？
如果把取的长度缩短，速度会更快：
已写入 file afiedt.buf

  1  select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2        where instr(a.object_name,b.object_name)=1
  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) /

已选择6843行。

已用时间:  00: 00: 01.01

执行计划
----------------------------------------------------------
Plan hash value: 4080738151

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  2723 |   247K|  4613  (96)| 00:00:56 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|*  2 |   HASH JOIN         |             |   139 | 12927 |   191   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
|   5 |   NESTED LOOPS      |             |  2584 |   234K|  4422   (1)| 00:00:54 |
|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL| TEST_OBJECT |    79 |  6873 |   132   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   2 - access(SUBSTR("A"."OBJECT_NAME",1,4)=SUBSTR("B"."OBJECT_NAME",1,4))
       filter(INSTR("A"."OBJECT_NAME","B"."OBJECT_NAME")=1)
   3 - filter(LENGTH("B"."OBJECT_NAME")>3)
   6 - filter(LENGTH("B"."OBJECT_NAME") ed
已写入 file afiedt.buf

  1  select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2        where instr(a.object_name,b.object_name)=1
  3        and substr(a.object_name,1,2)=substr(b.object_name,1,2)
  4        and length(b.object_name)>1
  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) /

已选择6843行。

已用时间:  00: 00: 00.29

执行计划
----------------------------------------------------------
Plan hash value: 4080738151

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  2723 |   247K|  4613  (96)| 00:00:56 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|*  2 |   HASH JOIN         |             |   139 | 12927 |   191   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
|   5 |   NESTED LOOPS      |             |  2584 |   234K|  4422   (1)| 00:00:54 |
|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL| TEST_OBJECT |    79 |  6873 |   132   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   2 - access(SUBSTR("A"."OBJECT_NAME",1,2)=SUBSTR("B"."OBJECT_NAME",1,2))
       filter(INSTR("A"."OBJECT_NAME","B"."OBJECT_NAME")=1)
   3 - filter(LENGTH("B"."OBJECT_NAME")>1)
   6 - filter(LENGTH("B"."OBJECT_NAME") ed
已写入 file afiedt.buf

  1  select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  2        where instr(a.object_name,b.object_name)=1
  3        and substr(a.object_name,1,2)=substr(b.object_name,1,2)
  4        and length(b.object_name)>1
  5        union all
  6        select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b
  7        where substr(a.object_name,1,1)=b.object_name
  8*       and length(b.object_name)=1
SQL> /

已选择6843行。

已用时间:  00: 00: 00.26

执行计划
----------------------------------------------------------
Plan hash value: 1025998422

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   656 | 61008 |   382  (51)| 00:00:05 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|*  2 |   HASH JOIN         |             |   139 | 12927 |   191   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |    33 |   198 |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
|*  5 |   HASH JOIN         |             |   517 | 48081 |   191   (2)| 00:00:03 |
|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |     7 |    42 |    56   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| TEST_OBJECT | 42235 |  3588K|   134   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   2 - access(SUBSTR("A"."OBJECT_NAME",1,2)=SUBSTR("B"."OBJECT_NAME",1,2))
       filter(INSTR("A"."OBJECT_NAME","B"."OBJECT_NAME")=1)
   3 - filter(LENGTH("B"."OBJECT_NAME")>1)
   5 - access("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,1))
   6 - filter(LENGTH("B"."OBJECT_NAME")=1)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2106  consistent gets
          0  physical reads
          0  redo size
     224711  bytes sent via SQL*Net to client
       5401  bytes received via SQL*Net from client
        458  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       6843  rows processed

cow977

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 substr(a.object_name, 1, N) = substr(b.object_name, 1, N)
   and length(b.object_name) >= N
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) < N;

N=1：Elapsed: 00:01:30.91
N=2：Elapsed: 00:00:16.76
N=3：Elapsed: 00:00:09.75
N=4：Elapsed: 00:00:09.63
N=5：Elapsed: 00:00:07.17
N=6：Elapsed: 00:00:06.97
N=7：Elapsed: 00:00:11.01
N=8：Elapsed: 00:00:19.21

这是偶的结果。

nyfor

拍手叫好!

Kevin__Zhang

好帖！

yyp2009

厉害！

学习

我上面有人

substr(a.object_name, 1, length(b.object_name)) = b.object_name
——————————————————————

如果不是从1开始，会怎么样？

taobup

这个帖子集中这么多高手。。

lfree

回过头讲,这个是数据结构设计问题.

为什么这样设计字段.

[ 本帖最后由 lfree 于 2011-5-19 16:29 编辑 ]