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

heya

select a.*,b.name from a,b
where substr(a.name,1,length(b.name))=b.name;

a:3~4w条记录
b:1w条记录

没有办法，这个语句，就是如果a.name的前半部分与b.name相同就组合为1条记录。
现在性能很慢，求给指点下能否优化。

select a.*,b.name from a,b
where  a.name  like b.name||'%';
性能还不如上面的。

〇〇

添加索引

anlinew

索引意义不大

anlinew

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)<4;

已选择108612行。

已用时间:  00: 00: 21.54
执行计划
----------------------------------------------------------
Plan hash value: 4080738151

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  3468 |   365K| 90997 (100)| 00:18:12 |
|   1 |  UNION-ALL          |             |       |       |            |          |
|*  2 |   HASH JOIN         |             |    34 |  3672 |   176   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |   659 | 12521 |    35   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 52544 |  4566K|   139   (1)| 00:00:02 |
|   5 |   NESTED LOOPS      |             |  3434 |   362K| 90821   (1)| 00:18:10 |
|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |   659 | 12521 |    35   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL| TEST_OBJECT |     5 |   445 |   138   (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("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJE
              CT_NAME")))
   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
      33946  consistent gets
          0  physical reads
          0  redo size
    4491423  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

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;

已选择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

[ 本帖最后由 anlinew 于 2010-12-9 19:56 编辑 ]

anlinew

大概意思就是切片利用连接进行优化，根据你的实际情况可以再切的更细
控制到秒级应该没问题

anlinew

测试数据量情况：
SQL> select 'a rows: ' ||count(*) from test_object
  2  union all
  3  select 'b rows: ' ||count(*) from TEST_OBJ1
  4  ;

'AROWS:'||COUNT(*)
------------------------------------------------
a rows: 52544
b rows: 13176

〇〇

9i的执行计划有些不同，但结论一样

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 12月 9 21:04:38 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

已连接到空闲例程。

ORACLE 例程已经启动。

Total System Global Area  219224828 bytes
Fixed Size                   453372 bytes
Variable Size             167772160 bytes
Database Buffers           50331648 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
已连接。
SQL> select count(*) from all_objects;

  COUNT(*)
----------
     29816

已用时间:  00: 00: 01.01
SQL> create table  test_object
  2  as select * from all_objects;

表已创建。

已用时间:  00: 00: 01.01
SQL> create table test_obj1 as select * from test_object where length(object_name)<8;

表已创建。

已用时间:  00: 00: 00.02
SQL> select 'a rows: ' ||count(*) from test_object
  2  union all
  3  select 'b rows: ' ||count(*) from TEST_OBJ1
  4  ;

'AROWS:'||COUNT(*)
------------------------------------------------
a rows: 29817
b rows: 1054

已用时间:  00: 00: 00.00
SQL> insert into test_obj1 select * from test_object where length(object_name)<16 and length(object_name)>=8;

已创建4387行。

已用时间:  00: 00: 00.00
SQL> insert into test_obj1 select * from test_object where length(object_name)<24 and length(object_name)>=16;

已创建8511行。

已用时间:  00: 00: 00.03
SQL> select 'a rows: ' ||count(*) from test_object
  2  union all
  3  select 'b rows: ' ||count(*) from TEST_OBJ1
  4  ;

'AROWS:'||COUNT(*)
------------------------------------------------
a rows: 29817
b rows: 13952

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> set autot traceonly
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;

已选择40294行。

已用时间:  00: 03: 55.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'TEST_OBJ1'
   3    1     TABLE ACCESS (FULL) OF 'TEST_OBJECT'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    5725226  consistent gets
          0  physical reads
          0  redo size
    2550942  bytes sent via SQL*Net to client
      30049  bytes received via SQL*Net from client
       2688  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      40294  rows processed

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)<4;

已选择40294行。

已用时间:  00: 00: 08.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   UNION-ALL
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'TEST_OBJ1'
   5    2       FILTER
   6    5         SORT (JOIN)
   7    6           TABLE ACCESS (FULL) OF 'TEST_OBJECT'
   8    1     NESTED LOOPS
   9    8       TABLE ACCESS (FULL) OF 'TEST_OBJ1'
  10    8       TABLE ACCESS (FULL) OF 'TEST_OBJECT'




Statistics
----------------------------------------------------------
          0  recursive calls
          9  db block gets
      61917  consistent gets
       1220  physical reads
          0  redo size
    2483557  bytes sent via SQL*Net to client
      30049  bytes received via SQL*Net from client
       2688  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
      40294  rows processed

SQL>

〇〇

索引还是有点效果，减少了50秒

SQL> create index idx_obj1 on TEST_OBJ1(object_name);

索引已创建。

已用时间:  00: 00: 01.00
SQL> explain plan for 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;

已解释。

已用时间:  00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |       |
|   1 |  NESTED LOOPS        |              |       |       |       |
|   2 |   TABLE ACCESS FULL  | TEST_OBJ1    |       |       |       |
|*  3 |   TABLE ACCESS FULL  | TEST_OBJECT  |       |       |       |
---------------------------------------------------------------------

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

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

Note: rule based optimization

已选择17行。

已用时间:  00: 00: 00.00
SQL> explain plan for select /*+use index(idx_obj1)*/ 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;

已解释。

已用时间:  00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------
| Id  | Operation             |  Name        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   164K|    22M|   101K|
|   1 |  NESTED LOOPS         |              |   164K|    22M|   101K|
|   2 |   TABLE ACCESS FULL   | TEST_OBJECT  | 25443 |  3180K|    42 |
|*  3 |   INDEX FAST FULL SCAN| IDX_OBJ1     |     6 |   102 |     4 |
----------------------------------------------------------------------

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

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

Note: cpu costing is off

已选择17行。

已用时间:  00: 00: 00.00
SQL> set autot traceonly
SQL> select /*+use index(idx_obj1)*/ 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;

已选择40294行。

已用时间:  00: 03: 04.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=101814 Card=164247 B
          ytes=23815815)

   1    0   NESTED LOOPS (Cost=101814 Card=164247 Bytes=23815815)
   2    1     TABLE ACCESS (FULL) OF 'TEST_OBJECT' (Cost=42 Card=25443
           Bytes=3256704)

   3    1     INDEX (FAST FULL SCAN) OF 'IDX_OBJ1' (NON-UNIQUE) (Cost=
          4 Card=6 Bytes=102)





Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
    1824491  consistent gets
         38  physical reads
          0  redo size
    1583619  bytes sent via SQL*Net to client
      30049  bytes received via SQL*Net from client
       2688  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      40294  rows processed

anlinew

建索引走IFFS是会有点儿用，但不会有数量级的改变，所以我说意义不大

并且楼主这个要想用IFFS还需要name 字段not null，否则没法用索引

另外你这个执行计划的不同是因为走了RBO，因此走了MERGE JOIN

newkid

anlinew利用固定长度的字符串比较使得计划变成HASH JOIN, 实在巧妙。