原文：http://www.itpub.net/viewthread.php?tid=1376537

http://www.cnblogs.com/killkill/archive/2011/04/11/2013044.html

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;

一句简单的语句，join的算法注定只能走Nested Loop，执行计划如下：

55546 rows selected.

Elapsed: 00:09:28.48

----------------------------------------------------------------------------------
| Id | Operation          | Name        | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   213K|    29M| 3310K (2)| 11:02:09 |
|   1 | NESTED LOOPS      |             |   213K|    29M| 3310K (2)| 11:02:09 |
|   2 |   TABLE ACCESS FULL| TEST_OBJECT | 50806 | 6350K|   159   (3)| 00:00:02 |
|* 3 |   TABLE ACCESS FULL| TEST_OBJ1   |     4 |    68 |    65   (2)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJ
ECT_NAME")))

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
   14055414 consistent gets
          0 physical reads
          0 redo size
    2126831 bytes sent via SQL*Net to client
      41133 bytes received via SQL*Net from client
       3705 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      55546 rows processed

网友anlinew提供了一种看似不可思义的非常精妙的优化方法，将语句改写成：

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, 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;

将数据进行了切片，语句复杂了，但性能却大幅提高。

优化的核心思想是将数据“分片”，该例子中分片的依据是多少位首字母（这里是4），其中“大头”由Hash Join处理，而“小头”走Nested Loop，这种“抓大放小”的做法直接就从复杂度上进行了优化。

执行计划如下：

55546 rows selected.

Elapsed: 00:00:09.59

-----------------------------------------------------------------------------------
| Id | Operation           | Name        | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             | 2827 |   400K| 12224 (99)| 00:02:27 |
|   1 | UNION-ALL          |             |       |       |            | |
|* 2 |   HASH JOIN         |             | 2128 |   301K|   391 (44)| 00:00:05 |
|* 3 |    TABLE ACCESS FULL| TEST_OBJ1   | 22731 |   377K|    67   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 50806 | 6350K|   159   (3)| 00:00:02 |
|   5 |   NESTED LOOPS      |             |   699 |    98K| 11833   (3)| 00:02:22 |
|* 6 |    TABLE ACCESS FULL| TEST_OBJ1   |    75 | 1275 |    67   (2)| 00:00:01 |
|* 7 |    TABLE ACCESS FULL| TEST_OBJECT |     9 | 1152 |   157   (3)| 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"."OBJECT_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"."OBJECT_NAME")))

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      39190 consistent gets
          0 physical reads
          0 redo size
    2216003 bytes sent via SQL*Net to client
      41133 bytes received via SQL*Net from client
       3705 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      55546 rows processed

按照anlinew的思路，试了下：

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, 1) = substr(b.object_name, 1, 1);
55546 rows selected.

Elapsed: 00:01:30.57

----------------------------------------------------------------------------------
| Id | Operation          | Name        | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             | 2135 |   302K|   391 (44)| 00:00:05 |
|* 1 | HASH JOIN         |             | 2135 |   302K|   391 (44)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| TEST_OBJ1   | 22806 |   378K|    67   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_OBJECT | 50806 | 6350K|   159   (3)| 00:00:02 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SUBSTR("A"."OBJECT_NAME",1,1)=SUBSTR("B"."OBJECT_NAME",1,1))
filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJECT_NAME")))

Statistics
----------------------------------------------------------
          7 recursive calls
          0 db block gets
       4706 consistent gets
          0 physical reads
          0 redo size
    2136652 bytes sent via SQL*Net to client
      41133 bytes received via SQL*Net from client
       3705 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
      55546 rows processed

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

当N=1时，第二个分片可以去掉，从执行计划看，也是HashJoin，但效率不是最高，从实验结果看，N=6是最佳分片。

SQL> select avg(length(object_name)) from test_obj1;

AVG(LENGTH(OBJECT_NAME))
------------------------
17.2789778

SQL> select length(object_name),count(*) from test_obj1 group by length(object_name) order by 1;

LENGTH(OBJECT_NAME)   COUNT(*)
------------------- ----------
                  1          2
                  2          7
                  3         41
                  4         66
                  5        102
                  6        209
                  7        429
                  8        569
                  9        442
                 10        569
                 11        729

                 12        728
                 13        935
                 14        947
                 15       1010
                 16       1268
                 17       1428
                 18       1594
                 19       1864
                 20       1877
                 21       2185
                 22       2260

23 2497

23 rows selected.

性能的优化，是经验的积累，但也要靠实验来验证，因为数据是基础，基础改变了，结论也就变了。

ITPub博客

从语句繁简和性能优化想到的......

最新文章