instr()函数的格式 （俗称：字符查找函数）

格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

实例

2014 年，曾遇到一个 INSTR 优化案例。因为当初 SQL 代码并非运行在 Oracle 中，所以，在 Oracle 中创建测试数据以便演示该案例，不管是 Oracle 数据库还是其他数据库，优化的思想都是一样的。

需求是这样的：查找事实表中 URL 字段包含了维度表中 URL 的记录，然后进行汇总统计。

创建事实表如下。

  1. create table T_FACT
  2. (msisdn number(11),
  3. url varchar2(50)
  4. );

插入测试数据。

  1. insert into T_FACT
  2.   select '139' || chr(dbms_random.value(48, 57)) ||
  3.          chr(dbms_random.value(48, 57)) || chr(dbms_random.value(48, 57)) ||
  4.          chr(dbms_random.value(48, 57)) || chr(dbms_random.value(48, 57)) ||
  5.          chr(dbms_random.value(48, 57)) || chr(dbms_random.value(48, 57)),
  6.          lpad(chr(dbms_random.value(97, 122)),
  7.               dbms_random.value(1, 20),
  8.               chr(dbms_random.value(97, 122))) ||
  9.          lpad(chr(dbms_random.value(97, 122)) ||
  10.               chr(dbms_random.value(97, 122)) ||
  11.               chr(dbms_random.value(97, 122)) ||
  12.               chr(dbms_random.value(97, 122)),
  13.               dbms_random.value(4, 20),
  14.               chr(dbms_random.value(97, 122)))
  15.     from dual
  16.   connect by rownum <= 10000;

反复插入数据，直到表中一共有 128 万条数据。

  1. begin
  2.   for i in 1..7 loop
  3.     insert into T_FACT
  4.       select * from T_FACT;
  5.     commit;
  6.   end loop;
  7. end;

在实际案例中事实表有上亿条数据，演示只取 100 万条数据。

创建维度表如下。

  1. create table T_DIM as
  2.     select cast(rownum as number(6)) code,cast(c1 as varchar2(50)) url 
  3. from (
  4.      select distinct substr(url, -dbms_random.value(2, length(url) - 3)) c1
  5.   from T_FACT);

创建汇总统计表。

  1. create table T_RESULT
  2. (
  3. msisdn number(11),
  4. code number(6),
  5. url varchar2(50),
  6. cnt number(6)
  7. );

现在我们要执行如下 SQL，统计 T_FACT 表中 URL 包含了 T_DIM 的记录。

  1. insert into T_RESULT
  2.   (msisdn, code, url, cnt)
  3.   select t1.msisdn, t2.code, t2.url, sum(1)
  4.     from T_FACT t1
  5.    inner join T_DIM t2 on instr(t1.url, t2.url) > 0
  6.    group by t1.msisdn, t2.code, t2.url;

因为 SQL 中关联条件是 instr，这时只能走嵌套循环，不能走 HASH 连接，也不能走排序合并连接，排序合并连接一般用于 >=，>，<，<=。以上 SQL 执行计划如下。

  1. select * from table(dbms_xplan.display);
  1. PLAN_TABLE_OUTPUT
  2. -------------------------------------------------------------------------------------
  3. Plan hash value: 2285685195
  5. -------------------------------------------------------------------------------------
  6. | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  7. -------------------------------------------------------------------------------------
  8. |   0 | INSERT STATEMENT         |          |    10G|   798G|   134M  (3)|448:22:51 |
  9. |   1 |  LOAD TABLE CONVENTIONAL | T_RESULT |       |       |            |          |
  10. |   2 |   HASH GROUP BY          |          |    10G|   798G|   134M  (3)|448:22:51 |
  11. |   3 |    NESTED LOOPS          |          |    10G|   798G|   133M  (2)|445:43:32 |
  12. |   4 |     TABLE ACCESS FULL    | T_FACT   |  1192K|    45M|  1363   (1)| 00:00:17 |
  13. |*  5 |     TABLE ACCESS FULL    | T_DIM    |  8993 |   351K|   112   (2)| 00:00:02 |
  14. -------------------------------------------------------------------------------------
  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  19.    5 - filter(INSTR("T1"."URL","T2"."URL")>0)

本书反复强调，嵌套循环被驱动表必须走索引。但是，如果执行计划是因为 INSTR，LIKE，REGEXP_LIK 等而导致的嵌套循环，这时被驱动表反而不能走索引。INSTR，LIKE，REGEXP_LIKE 会匹配所有数据，走索引的访问路径只能是 INDEX FULL SCAN，而 INDEX FULL SCAN 是单块读，全表扫描是多块读。如果 INDEX FULL SCAN 需要回表，这时效率远远不如全表扫描效率高。如果被驱动表走 INDEX FULL SCAN 不回表，这时我们也可以根据索引中的索引列，建立一个临时表，将需要的列包含在临时表中，用临时表代替 INDEX FULL SCAN，因为临时表不像索引那样需要存储根、分支、叶子节点，临时表相比索引体积反而更小，这样可以减少被驱动表每次被扫描的体积。被驱动表因为要被反复扫描多次，buffer cache 最好要有足够的空间用于存放被驱动表，从而避免被驱动表每次被扫描都需要物理 I/O。

经过上面分析，如果从执行计划方向入手，我们无法优化 SQL。我们再来看一下原始 SQL 语句。

  1. insert into T_RESULT
  2.   (msisdn, code, url, cnt)
  3.   select t1.msisdn, t2.code, t2.url, sum(1)
  4.     from T_FACT t1
  5.    inner join T_DIM t2 on instr(t1.url, t2.url) > 0
  6.    group by t1.msisdn, t2.code, t2.url;

SQL 语句中有 GROUP BY（汇总），事实表与维度表一般都是 N:1 关系，因为 SQL 语句中有汇总，我们可以先对事实表进行汇总，去掉重复数据，然后再与维度表关联。因为执行计划中事实表是驱动表，维度表是被驱动表，将事实表提前汇总可以将数据量大大减少，这样我们就可以减少嵌套循环的循环次数，从而达到优化目的。

事实表原始数据为 128 万行，我们对事实表提前汇总。

  1. create table T_MIDDLE as select 
  2.      msisdn,url,sum(1) cnt 
  3.      from  T_FACT group by msisdn,url;

提前汇总之后，数据从 128 万行减少到 1 万行。

  1. select count(*) from  T_MIDDLE;
  1.   COUNT(*)
  2. ----------
  3.      10000

改写后的 SQL 如下。

  1. insert into T_RESULT
  2.   (msisdn, code, url, cnt)
  3.   select t1.msisdn, t2.code, t2.url, sum(cnt)
  4.     from T_MIDDLE t1
  5.    inner join T_DIM t2 on instr(t1.url, t2.url) > 0
  6.    group by t1.msisdn, t2.code, t2.url;

对数据进行提前汇总之后，被驱动表 T_DIM 只需要循环 1 万次，而之前需要循环 128 万次，性能得到极大提升。

如果想要最大程度优化 INSTR，LIKE，REGEXP_LIKE 等非等值关联，我们只能从业务角度入手，设法从业务本身、数据本身着手，使其进行等值连接，从而可以走 HASH 连接。

如果业务手段无法优化，除了上面讲到的提前汇总数据，我们还可以开启并行查询（并行广播），从而优化 SQL。如果不想开启并行查询，我们可以对表进行拆分（类似并行广播），人工模拟并行查询，从而优化 SQL。我们可以对驱动表进行拆分，也可以对被驱动表进行拆分，但是最好不要同时拆分驱动表和被驱动表，因为连接条件是非等值连接，同时拆分驱动表和被驱动表会导致交叉关联（将驱动表和被驱动表都拆分为 6 份，会关联 36 次）。如果表是非分区表，我们可以利用 ROWID 进行拆分。如果表是分区表，我们可以针对分区进行拆分。关于具体的拆分方法，请大家阅读 8.5 节内容。