oracle中的非等值连接,关于等值连接和非等值连接效率解析。。。求解

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott@orcl

SQL> create table t_user_info(u_mobile varchar2(12));

Table created

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

Table created

SQL> insert into t_user_info select '1'||round(dbms_random.value*10000000000) from dual connect by level<10000;

9999 rows inserted

SQL> select * from t_user_info t where rownum<5;

U_MOBILE

------------

11057368763

11730323699

16972099044

14378885731

SQL> insert into t_mobile_segment select '1'||round(dbms_random.value*1000000),round(dbms_random.value*2) from dual connect by level<100000;

99999 rows inserted

SQL> set autotrace on;

Cannot SET AUTOTRACE

SQL> set timing on;

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)

----------

1059

Executed in 0.078 seconds--执行时间

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

COUNT(1)

----------

7423

Executed in 125.472 seconds-------执行时间

SQL> select distinct length(t.m_segement) from t_mobile_segment t;

LENGTH(T.M_SEGEMENT)

--------------------

6

2

5

4

7

3

6 rows selected

Executed in 0.078 seconds

----------------------------------------------------------------------------------查看实际执行计划

SQL>

SQL> SELECT SQL_ID, CHILD_NUMBER,SQL_TEXT

2    FROM V$SQL

3   WHERE SQL_TEXT LIKE '%t_mobile_segment%'

4      or SQL_TEXT LIKE '%t_user_info%';

SQL_ID        CHILD_NUMBER SQL_TEXT

------------- ------------ ----------------------------------------------------------------------------------------------------

2d6gbn9snndrs            0 select count(1) from t_user_info t1,t_mobile_segment t2 where instr(t1.u_mobile,t2.m_segement)=1

fguusb54wnrsp            0 select count(1) from t_user_info t1,t_mobile_segment t2 where substr(t1.u_mobile,1,7)=t2.m_segement

974nxqaya1da7            0 SELECT SQL_ID, CHILD_NUMBER   FROM V$SQL  WHERE SQL_TEXT LIKE '%t_mobile_segment%'     or SQL_TEXT L

IKE '%t_user_info%'

6kwgj215u5dmg            0 SELECT SQL_ID, CHILD_NUMBER,SQL_TEXT   FROM V$SQL  WHERE SQL_TEXT LIKE '%t_mobile_segment%'     or S

QL_TEXT LIKE '%t_user_info%'

543qh17nu2391            0 select distinct length(t.m_segement) from t_mobile_segment t

Executed in 0.094 seconds

SQL>

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2d6gbn9snndrs',0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  2d6gbn9snndrs, child number 0

-------------------------------------

select count(1) from t_user_info t1,t_mobile_segment t2 where

instr(t1.u_mobile,t2.m_segement)=1

Plan hash value: 208850422

--------------------------------------------------------------------------------

| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                  |       |       |    63 (100)|

|   1 |  SORT AGGREGATE     |                  |     1 |    16 |            |

|   2 |   NESTED LOOPS      |                  |     1 |    16 |    63   (0)| 00

|   3 |    TABLE ACCESS FULL| T_USER_INFO      |     1 |     8 |     8   (0)| 00

|*  4 |    TABLE ACCESS FULL| T_MOBILE_SEGMENT |     1 |     8 |    55   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

22 rows selected

Executed in 0.593 seconds

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('fguusb54wnrsp',0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  fguusb54wnrsp, child number 0

-------------------------------------

select count(1) from t_user_info t1,t_mobile_segment t2 where

substr(t1.u_mobile,1,7)=t2.m_segement

Plan hash value: 3321126271

--------------------------------------------------------------------------------

| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                  |       |       |    64 (100)|

|   1 |  SORT AGGREGATE     |                  |     1 |    16 |            |

|*  2 |   HASH JOIN         |                  |     1 |    16 |    64   (2)| 00

|   3 |    TABLE ACCESS FULL| T_USER_INFO      |     1 |     8 |     8   (0)| 00

|   4 |    TABLE ACCESS FULL| T_MOBILE_SEGMENT |     1 |     8 |    55   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T2"."M_SEGEMENT"=SUBSTR("T1"."U_MOBILE",1,7))

22 rows selected

Executed in 0.124 seconds

SQL>