substr(a.object_name, 1, length(b.object_name)) = b.object_name 优化

今早在ITPUB上面看到一贴，思路不错记录下来。

http://www.itpub.net/thread-1376537-1-1.html

SQL>  select a.*,b.object_name from test1 a,test2 b where substr(a.object_name,1,length(b.object_name)) = b.object_name;

27618 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3862800485

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           | 78405 |    16M|   142K  (1)| 00:28:29 |
|   1 |  NESTED LOOPS         |           | 78405 |    16M|   142K  (1)| 00:28:29 |
|   2 |   TABLE ACCESS FULL   | TEST1     | 28954 |  4467K|   288   (1)| 00:00:04 |
|*  3 |   INDEX FAST FULL SCAN| IND_TEST2 |     3 |   198 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

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

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     585568  consistent gets
          0  physical reads
          0  redo size
     693251  bytes sent via SQL*Net to client
      20774  bytes received via SQL*Net from client
       1843  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      27618  rows processed

一看这执行计划就特别的坑。

SQL> select a.*, b.object_name
  2    from test1 a, test2 b
  3   where substr(a.object_name, 1, length(b.object_name)) = b.object_name
  4     and substr(a.object_name, 1, 4) = substr(b.object_name, 1, 4)
  5     and length(b.object_name) > 3
  6  union all
  7  select a.*, b.object_name
  8    from test1 a, test2 b
  9   where substr(a.object_name, 1, length(b.object_name)) = b.object_name
 10     and length(b.object_name) < 4;

27618 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1525591892

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |  3751 |   820K| 50193 (100)| 00:10:03 |
|   1 |  UNION-ALL             |           |       |       |            |          |
|*  2 |   HASH JOIN            |           |   754 |   164K|   302   (3)| 00:00:04 |
|*  3 |    INDEX FAST FULL SCAN| IND_TEST2 |  4378 |   282K|     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | TEST1     | 28954 |  4467K|   288   (1)| 00:00:04 |
|   5 |   NESTED LOOPS         |           |  2997 |   655K| 49891   (1)| 00:09:59 |
|*  6 |    INDEX FAST FULL SCAN| IND_TEST2 |   174 | 11484 |     7   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL   | TEST1     |    17 |  2686 |   287   (1)| 00:00:04 |
------------------------------------------------------------------------------------

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"."OBJEC
              T_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"."OBJEC
              T_NAME")))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
     183117  consistent gets
          0  physical reads
          0  redo size
    1006639  bytes sent via SQL*Net to client
      20774  bytes received via SQL*Net from client
       1843  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      27618  rows processed

这里由于将数据分片，引入了谓词substr(a.object_name, 1, 4) = substr(b.object_name, 1, 4)，从而可以使用HASH JOIN。当然这里的4是根据具体情况来确定的。总得原则就是根据实际情况确定此值的大小，使得结果集大的部分可以做HASH_JOIN，而小的结果集交给NL.