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>