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

来源：互联网发布：大疆飞控算法编辑：程序博客网时间：2024/05/29 19:19

今早在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.

0 0