关于等值连接和非等值连接效率解析。。。求解

〇〇

like能用上索引,instr用不上

create table t_user_info(u_mobile varchar2(12));

create table t_mobile_segment(m_segement varchar2(12),m_carrier varchar2(2));

insert into t_user_info select '1'||x from
(select substr(dbms_random.random,1,10)x from dual connect by level<=1e5)
where length(x)=10;



insert into t_mobile_segment select distinct '1'||x ,1 from
(select substr(dbms_random.random,1,6)x from dual connect by level<=1e5)
where length(x)=6;

explain plan for select count(1) from t_user_info t1,t_mobile_segment t2 where instr(t1.u_mobile,t2.m_segement)=1;

explain plan for select count(1) from t_user_info t1,t_mobile_segment t2 where substr(t1.u_mobile,1,7)=t2.m_segement;

explain plan for select count(1) from t_user_info t1,t_mobile_segment t2 where t1.u_mobile like t2.m_segement||'%';

SQL> create index tui on t_user_info(u_mobile);

索引已创建。

已用时间:  00: 00: 00.20
SQL> explain plan for select count(1) from t_user_info t1,t_mobile_segment t2 where instr(t1.u_mobile,t2.m_segement)=1;

已解释。

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 75945502

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    16 |  5004K  (1)| 16:40:54 |
|   1 |  SORT AGGREGATE     |                  |     1 |    16 |            |          |
|   2 |   NESTED LOOPS      |                  |    63M|   964M|  5004K  (1)| 16:40:54 |
|   3 |    TABLE ACCESS FULL| T_MOBILE_SEGMENT | 74982 |   585K|    69   (2)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T_USER_INFO      |   843 |  6744 |    67   (2)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - filter(INSTR("T1"."U_MOBILE","T2"."M_SEGEMENT")=1)

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

已选择20行。

已用时间:  00: 00: 00.07
SQL> explain plan for select count(1) from t_user_info t1,t_mobile_segment t2 where t1.u_mobile like t2.m_segement||'%';

已解释。

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 178076189

----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    16 |   225K  (1)| 00:45:07 |
|   1 |  SORT AGGREGATE     |                  |     1 |    16 |            |          |
|   2 |   NESTED LOOPS      |                  |   316M|  4824M|   225K  (1)| 00:45:07 |
|   3 |    TABLE ACCESS FULL| T_MOBILE_SEGMENT | 74982 |   585K|    69   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | TUI              |  4217 | 33736 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("T1"."U_MOBILE" LIKE "T2"."M_SEGEMENT"||'%')
       filter("T1"."U_MOBILE" LIKE "T2"."M_SEGEMENT"||'%')

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

已选择21行。

已用时间:  00: 00: 00.07
SQL> select count(1) from t_user_info t1,t_mobile_segment t2 where t1.u_mobile like t2.m_segement||'%';

  COUNT(1)
----------
     37176

已用时间:  00: 00: 00.48

SQL> select count(1) from t_user_info t1,t_mobile_segment t2 where substr(t1.u_mobile,1,7)=t2.m_segement;

  COUNT(1)
----------
     37176

已用时间:  00: 00: 00.04

xuhao167

〇〇 发表于 2016-11-18 20:11
like能用上索引,instr用不上

create table t_user_info(u_mobile varchar2(12));

多谢指教，下面这个造数据的语句很受用

1. insert into t_user_info select '1'||x from
   
2. (select substr(dbms_random.random,1,10)x from dual connect by level<=1e5)
   
3. where length(x)=10;

复制代码