sql性能求指教！！！！

Question

如下2个sql：
sql1：
     select * from table1 t1
    inner join table2 on t1.id = t2.oid
    where t2.name like '%XXX%'
sql2:
    select * from table1 t1 
    inner join table2 on t1.id = t2.oid and t2.name like '%XXX%'
以上2个sql哪个性能有差别吗？

Answer 1

以执行计划为准
不过'%aaaa%'不会走索引，应注意一下

Answer 2

根据你的写法、做个简单测试：t1 10W行数据 、、t2 100W行数据create table t1(id number);
create table t2(oid number,name varchar2(10));
insert into t1 select rownum from dual connect by level<=100000;
insert into t2 select rownum,rownum||'a' from dual connect by level<=1000000;
commit;sys@ORCL> set autot trace exp
sys@ORCL> ed
Wrote file afiedt.buf  1  select * from t1
  2      inner join t2 on t1.id = t2.oid
  3*     where t2.name like '%XXX%'sys@ORCL> /Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    64 |  2112 |  1647   (3)| 00:00:20 |
|*  1 |  HASH JOIN         |      |    64 |  2112 |  1647   (3)| 00:00:20 |
|*  2 |   TABLE ACCESS FULL| T2   |    64 |  1280 |  1600   (2)| 00:00:20 |
|   3 |   TABLE ACCESS FULL| T1   |   103K|  1314K|    45   (5)| 00:00:01 |
---------------------------------------------------------------------------
sys@ORCL> ed
Wrote file afiedt.buf  1  select * from t1
  2*     inner join t2 on t1.id = t2.oid and t2.name like '%XXX%'
sys@ORCL> /Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    64 |  2112 |  1647   (3)| 00:00:20 |
|*  1 |  HASH JOIN         |      |    64 |  2112 |  1647   (3)| 00:00:20 |
|*  2 |   TABLE ACCESS FULL| T2   |    64 |  1280 |  1600   (2)| 00:00:20 |
|   3 |   TABLE ACCESS FULL| T1   |   103K|  1314K|    45   (5)| 00:00:01 |
---------------------------------------------------------------------------
可以看到、你的两个sql的执行计划完全一致、本质上讲这是两个完全相同的sql

Answer 3

一 两个查询的结果是不一样的，第一个查询的数据量会少，因为条件是在where中，而第二个sql语句用的左外连接
二 性能上是都不走索引的，因为在数据前都有通配符%

Answer 4

4楼的测试有些意思,因为两个sql解析之后是同样的一个plan hash value,所以结果基本不会有区别真实情况是否如此呢?也许应该加入purge关键字消除缓存造成的影响.

Answer 5

确实、两个都是 Plan hash value: 2959412835 
谢谢提醒、

Answer 6

oracle 已经足够对你的语句进行各种优化，所以只要你的语句表述清楚，oracle的执行其实都会先进行语句的调优，所以本质不会太大变化。

Answer 7

oracle可能会对你这2个sql进行查询重写，所以简单的sql有时候看不出区别。不过推荐第二种写法，因为你是内连接，属于特定表的过滤条件当然最好放在其的on后面。

Answer 8

求教版主：执行计划一样但性能未必一样是不？比如：select * from t where id = 1 and oid = 2 和 select * from t where oid = 2 and id = 1这2个sql执行计划是不是一样的，但性能往往会有差别？另外select * form table1 t1 inner join table2 t2 on t1.id = t2.oid where t2.name like '%XXX%'的写法是不是跟 select * from table1 t1,table2 t2 where t1.id = t2.oid and t2.name like '%XXX%'是一样的而第2种写法是不是也跟这个写法一样？另外，这2种方式都是先联合再过滤吗，还是第2种方式是在联合前先根据name过滤table2再联合然后再过滤出2个表对应字段相等的记录？

Answer 9

1）CBO时代、执行计划一样、意味着花费的代价也一样
2）有个原则是"让每个结果集最小"、SQL引擎的组件查询转换器会进行查询转换、先过滤、再联合；这在大多数场合都是合理有效的