等值连接的sql为什么不走索引？

ShineCQY

  在sql优化的过程中看到同事这样写SQL:t1.a || t1.b || t1.c = t2.a || t2.b || t2.c这样导致了不走索引，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));
插入测试数据：
declare
  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<9990;; --保留10行相同数据
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;
为两张1000数据的表创建唯一索引：
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.b,t2.c from t1,t2 where t1.a||t1.b=t2.a||t2.b；
select t1.b,t2.c from t1,t2 where t1.a=t2.a and t2.b=t1.b；
SQL> set autotrace on
SQL> set timing on
SQL> select t1.b,t2.c from t1,t2 where t1.a||t1.b=t2.a||t2.b;

B          C
--------- ----------
04-OCT-39 test1
05-OCT-39 test1
06-OCT-39 test1
07-OCT-39 test1
08-OCT-39 test1
09-OCT-39 test1
10-OCT-39 test1
11-OCT-39 test1
12-OCT-39 test1
13-OCT-39 test1
14-OCT-39 test1

11 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time          |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |  1001K|    29M|    27  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |          |  1001K|    29M|    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"))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         77  consistent gets
          0  physical reads
          0  redo size
        775  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> select t1.b,t2.c from t1,t2 where t1.a=t2.a and t2.b=t1.b;

B          C
--------- ----------
04-OCT-39 test1
05-OCT-39 test1
06-OCT-39 test1
07-OCT-39 test1
08-OCT-39 test1
09-OCT-39 test1
10-OCT-39 test1
11-OCT-39 test1
12-OCT-39 test1
13-OCT-39 test1
14-OCT-39 test1

11 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time          |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          | 10000 |   302K|    24   (5)| 00:00:01 |
|*  1 |  HASH JOIN           |          | 10000 |   302K|    24   (5)| 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("T1"."A"="T2"."A" AND "T2"."B"="T1"."B")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         77  consistent gets
          0  physical reads
          0  redo size
        775  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

Maclean Liu(刘相兵

select t1.b,t2.c from t1,t2 where t1.a=t2.a and t2.b=t1.b;

==》
1.无任何谓词条件
2. 表很小

ShineCQY

多谢老大指点，加了谓词条件就行了。

Maclean Liu(刘相兵

select t1.b,t2.c from t1,t2 where t1.a=t2.a and t2.b=t1.b;

这个SQL走HASH JOIN还是很合理的

ShineCQY

我觉得之所以走hash join 是因为两张表 都是1W条左右的数据。数据量差不多。如果是一张大表然后Join一张小表估计就要 nest loops了。
例如：select b.account,
                                                 b.en_code,
                                                 b.chinese_name,
                                                 b.account_type,
                                                 b.currence_code,
                                                 case
                                                         when s.account_status = '11' then
                                                                b.open_date
                                                         else
                                                                b.importdate
                                                 end,
                                                 b.file_number,
                                                 b.instcode,
                                                 '1',
                                                 -- b.auditdate,
                                                 b.importdate,
                                                 b.businessid,
                                                 -- b.auditname,
                                                 -- b.modifyuser,
                                                 b.iscommit,
                                                 'A',
                                                 '',
                                                 b.branch_code,
                                                 b.branch_name,
                                                 s.account_status,
                                                 b.amtype,
                                                 b.account_cata,
                                                 b.limit_type,
                                                 b.remark,
                                                 b.is_handiwork,
                                                 b.account_limit
                                from tmp_t_base_account b
                                ,tmp_account_status s
                         where b.account || b.instcode || b.currence_code = s.account_num
                                 and s.account_status in ('11', '12')



ID PID Estim
Card Actual
Rows Row Source Operation BG
Consistent
Read Mode
(cr) OS
Buffer
Gets
(pr) OS
Write
Calls
(pw) Time
(secs) Obj Cost Estim
Size
(bytes)
1: 0 101 751 NESTED LOOPS  847142964 699 0 1193.496 0 108 20705
2: 1 128 414043  TABLE ACCESS FULL TMP_T_BASE_ACCOUNT 10833 692 0 4.077 17081 3 22656
3: 1 1 751  TABLE ACCESS FULL TMP_ACCOUNT_STATUS 847132131 7 0 2374.070 17056 1 28

Maclean Liu(刘相兵

和你的where 条件、 select出来的表达式都有关系

1. SQL> select t1.b,t2.c from t1,t2 where t1.a=t2.a and t2.b=t1.b;
   
2. 
   
3. 11 rows selected.
   
4. 
   
5. 
   
6. Execution Plan
   
7. ----------------------------------------------------------
   
8. Plan hash value: 1838229974
   
9. 
   
10. ---------------------------------------------------------------------------
    
11. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
12. ---------------------------------------------------------------------------
    
13. |   0 | SELECT STATEMENT   |      | 10000 |   302K|    24   (0)| 00:00:01 |
    
14. |*  1 |  HASH JOIN         |      | 10000 |   302K|    24   (0)| 00:00:01 |
    
15. |   2 |   TABLE ACCESS FULL| T1   | 10000 |   117K|    12   (0)| 00:00:01 |
    
16. |   3 |   TABLE ACCESS FULL| T2   | 10011 |   185K|    12   (0)| 00:00:01 |
    
17. ---------------------------------------------------------------------------
    
18. 
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
    
21. 
    
22.    1 - access("T1"."A"="T2"."A" AND "T2"."B"="T1"."B")
    
23. 
    
24. 
    
25. Statistics
    
26. ----------------------------------------------------------
    
27.           2  recursive calls
    
28.           0  db block gets
    
29.          83  consistent gets
    
30.           0  physical reads
    
31.           0  redo size
    
32.         791  bytes sent via SQL*Net to client
    
33.         543  bytes received via SQL*Net from client
    
34.           2  SQL*Net roundtrips to/from client
    
35.           0  sorts (memory)
    
36.           0  sorts (disk)
    
37.          11  rows processed
    
38. 
    
39. SQL> select t1.b,t2.c from t1,t2 where t1.a=t2.a ;
    
40. 
    
41. 11 rows selected.
    
42. 
    
43. 
    
44. Execution Plan
    
45. ----------------------------------------------------------
    
46. Plan hash value: 2959412835
    
47. 
    
48. ---------------------------------------------------------------------------
    
49. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
50. ---------------------------------------------------------------------------
    
51. |   0 | SELECT STATEMENT   |      | 10000 |   224K|    24   (0)| 00:00:01 |
    
52. |*  1 |  HASH JOIN         |      | 10000 |   224K|    24   (0)| 00:00:01 |
    
53. |   2 |   TABLE ACCESS FULL| T2   | 10011 |   107K|    12   (0)| 00:00:01 |
    
54. |   3 |   TABLE ACCESS FULL| T1   | 10000 |   117K|    12   (0)| 00:00:01 |
    
55. ---------------------------------------------------------------------------
    
56. 
    
57. Predicate Information (identified by operation id):
    
58. ---------------------------------------------------
    
59. 
    
60.    1 - access("T1"."A"="T2"."A")
    
61. 
    
62. 
    
63. Statistics
    
64. ----------------------------------------------------------
    
65.           1  recursive calls
    
66.           0  db block gets
    
67.          80  consistent gets
    
68.           0  physical reads
    
69.           0  redo size
    
70.         791  bytes sent via SQL*Net to client
    
71.         543  bytes received via SQL*Net from client
    
72.           2  SQL*Net roundtrips to/from client
    
73.           0  sorts (memory)
    
74.           0  sorts (disk)
    
75.          11  rows processed
    
76.                  
    
77.                  
    
78.                  
    
79.                  
    
80. 
    
81. SQL> select 1 from t1,t2 where t1.a=t2.a;
    
82. 
    
83. 11 rows selected.
    
84. 
    
85. 
    
86. Execution Plan
    
87. ----------------------------------------------------------
    
88. Plan hash value: 1184619912
    
89. 
    
90. ----------------------------------------------------------------------------------
    
91. | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    
92. ----------------------------------------------------------------------------------
    
93. |   0 | SELECT STATEMENT      |          | 10000 | 90000 |     8  (13)| 00:00:01 |
    
94. |   1 |  NESTED LOOPS         |          | 10000 | 90000 |     8  (13)| 00:00:01 |
    
95. |   2 |   INDEX FAST FULL SCAN| INX_T1_A | 10000 | 40000 |     7   (0)| 00:00:01 |
    
96. |*  3 |   INDEX UNIQUE SCAN   | INX_T2_A |     1 |     5 |     0   (0)| 00:00:01 |
    
97. ----------------------------------------------------------------------------------
    
98. 
    
99. Predicate Information (identified by operation id):
    
100. ---------------------------------------------------
     
101. 
     
102.    3 - access("T1"."A"="T2"."A")
     
103. 
     
104. 
     
105. Statistics
     
106. ----------------------------------------------------------
     
107.           1  recursive calls
     
108.           0  db block gets
     
109.          37  consistent gets
     
110.           0  physical reads
     
111.           0  redo size
     
112.         634  bytes sent via SQL*Net to client
     
113.         543  bytes received via SQL*Net from client
     
114.           2  SQL*Net roundtrips to/from client
     
115.           0  sorts (memory)
     
116.           0  sorts (disk)
     
117.          11  rows processed

复制代码