where条件中等值连接使用双竖杠影响SQL性能
【SQL性能下降原因】
如果表t1和t2 字段a,b 上有索引并且表数据量很大。这种写法t1.a || t1.b || t1.c = t2.a || t2.b || t2.c 是不会走索引的。应该写成t1.a=t2.a and t1.b=t2.b 这样就会根据索引检索数据，速度就很快了。
测试：
创建表1： create table t1 (a number,b date,c varchar2(10));
插入测试数据：
na integer;
nc varchar2(10) :='test1';
begin
for na in 1..10000 loop
insert into t1 (a,b,c) values (na,sysdate+na,nc);
end loop;
commit;
end;
创建表2：create table t2 as select * from t1;
为了测试方便删除一部分数据,使查询的时候走索引：
SQL> delete from t2 where rownum<9770;; --保留少量相同数据
Commit;
向T2插入更多不同数据：
declare
na integer;
nc varchar2(10) :='test1';
begin
for na in 10001..20000 loop
insert into t2 (a,b,c) values (na,sysdate-na,nc);
end loop;
commit;
end;
为两张10000数据的表创建唯一索引：
create unique index inx_t1_a on t1 (a);
create unique index inx_t2_a on t2 (a);
分析表
SQL> execute dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed
SQL> execute dbms_stats.gather_table_stats(USER,'T2',CASCADE=>TRUE);
PL/SQL procedure successfully completed
分别查看这两条语句的执行情况：
select t1.a,t2.b,t2.c from t1,t2 where t1.a=t2.a and t2.b=t1.b and t1.a = 9770 and t2.a = 9770 select t1.a,t2.b,t2.c from t1,t2 where t1.a||t1.b=t2.a||t2.b and t1.a||t2.a=97709770
SQL> set autotrace on
SQL> set timing on
SQL> select t1.a,t2.b,t2.c from t1,t2 where t1.a=t2.a and t2.b=t1.b and t1.a = 9770 and t2.a = 9770;
A B C
---------- --------- ----------
9770 14-OCT-39 test1
Elapsed: 00:00:00.02 --执行时间
Execution Plan
----------------------------------------------------------
Plan hash value: 714581961
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 31 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 19 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | INX_T2_A | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 12 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | INX_T1_A | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."A"=9770)
4 - filter("T2"."B"="T1"."B")
5 - access("T1"."A"=9770)
filter("T1"."A"="T2"."A")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets --耗用的资源
0 physical reads
0 redo size
660 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select t1.a,t2.b,t2.c from t1,t2 where t1.a||t1.b=t2.a||t2.b and t1.a||t2.a=97709770;
A B C
---------- --------- ----------
9770 14-OCT-39 test1
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10011 | 303K| 27 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 10011 | 303K| 27 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 117K| 11 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 10011 | 185K| 12 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(TO_CHAR("T1"."A")||INTERNAL_FUNCTION("T1"."B")=TO_CHAR("T2
"."A")||INTERNAL_FUNCTION("T2"."B"))
filter(TO_NUMBER(TO_CHAR("T1"."A")||TO_CHAR("T2"."A"))=97709770)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
77 consistent gets
0 physical reads
0 redo size
660 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
