【实验】where子句的解析顺序及执行效率

有一种声音叫做：where子句的解析顺序是从右向左（如果上下书写的话，就是从下到上），因此能够过滤掉大部分数据的条件要书写到where子句的后面，同时具有表关联的条件要放到where子句的前面。

这种说法在Oracle 10gR2的CBO和RBO模式下是什么样的呢？

这里做一个这样的实验，我们在RBO和CBO两种情况下对where子句中关于过滤记录数有悬殊区别的两个条件的顺序调整，看一下效果。

我们能得到一个结论是：
在Oracle 10.2.0.3环境下测试，无论在RBO还是在CBO优化条件下没有表连接的情况下效率是一样的。
（由于ORACLE 的自动化优化策略，根据不同的数据分析结果，可能执行计划不相同。具体以您自己的实验数据为准，本实验仅供参考。）

实验参考如下。

1.测试环境是Oracle 10.2.0.3
sec@ora10g> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

2.创建包含3千万数据的测试表
big_table的id是表的主键（索引可用）
@big_table 30000000

3.我们使用的两个条件
1）符合OWNER = 'SEC'条件的记录数大概在6万（返回小数据量的条件）
sec@ora10g> select count(*) from big_table where WNER = 'SEC';

  COUNT(*)
----------
     54595

2）符合id > 20000000条件的记录数为1千万（返回大数据量的条件）
sec@ora10g> select count(*) from big_table where id > 20000000;

  COUNT(*)
----------
  10000000

4.测试场景，及测试结论
1）CBO优化环境下，小数据量返回条件在where子句下方（where子句的最后一个条件）
2）CBO优化环境下，小数据量返回条件在where子句上方（where子句的最前一个条件）
3）RBO优化环境下，小数据量返回条件在where子句下方（where子句的最后一个条件）
4）RBO优化环境下，小数据量返回条件在where子句上方（where子句的最前一个条件）

测试结论：
以上四种场景测试效率一样。

5.具体的测试场景的展开
1）CBO优化环境下，小数据量返回条件在where子句下方（where子句的最后一个条件）
sec@ora10g> select count(*) from big_table
 where id >= 20000000
   and WNER = 'SEC';
  2    3
  COUNT(*)
----------
     18265

Elapsed: 00:00:04.26

Execution Plan
----------------------------------------------------------
Plan hash value: 599409829

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    23 | 89421   (1)| 00:17:54 |
|   1 |  SORT AGGREGATE    |           |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS FULL| BIG_TABLE |   100K|  2255K| 89421   (1)| 00:17:54 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SEC' AND "ID">=20000000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     405329  consistent gets
     320902  physical reads
        936  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2）CBO优化环境下，小数据量返回条件在where子句上方（where子句的最前一个条件）
sec@ora10g> select count(*) from big_table
 where WNER = 'SEC'
   and id >= 20000000;
  2    3
  COUNT(*)
----------
     18265

Elapsed: 00:00:04.27

Execution Plan
----------------------------------------------------------
Plan hash value: 599409829

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    23 | 89421   (1)| 00:17:54 |
|   1 |  SORT AGGREGATE    |           |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS FULL| BIG_TABLE |   100K|  2255K| 89421   (1)| 00:17:54 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SEC' AND "ID">=20000000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     405329  consistent gets
     320902  physical reads
        980  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


3）RBO优化环境下，小数据量返回条件在where子句下方（where子句的最后一个条件）
sec@ora10g> select /*+ RULE */ count(*) from big_table
 where id >= 20000000
   and WNER = 'SEC';
  2    3
  COUNT(*)
----------
     18265

Elapsed: 00:00:03.10

Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|   1 |  SORT AGGREGATE              |              |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |
|*  3 |    INDEX RANGE SCAN          | BIG_TABLE_PK |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SEC')
   3 - access("ID">=20000000)

Note
-----
   - rule based optimizer used (consider using cbo)


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

4）RBO优化环境下，小数据量返回条件在where子句上方（where子句的最前一个条件）
sec@ora10g> select /*+ RULE */ count(*) from big_table
 where WNER = 'SEC'
   and id >= 20000000;
  2    3
  COUNT(*)
----------
     18265

Elapsed: 00:00:03.09

Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|   1 |  SORT AGGREGATE              |              |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |
|*  3 |    INDEX RANGE SCAN          | BIG_TABLE_PK |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SEC')
   3 - access("ID">=20000000)

Note
-----
   - rule based optimizer used (consider using cbo)


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

6.关于复杂计算条件和关联条件的排列组合方式，如果您有兴趣，也可以进一步做一下测试。我们的原则是：用真实的实验讲话！

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-615630/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-615630/