标量子查询优化测试

简介
        今天主要是研究了下对sql中对标量子查询的优化思路，觉得有些收获 ，特别把研究的过程发出来供爱好者们一同学习研究。

1、搭建测试环境

--db version：oracle 11g R2 单机环境

--创建测试用表

sqlplus hr/hr

SQL> create table b as select * from dba_objects;
Table created.
SQL> create table a as select * from dba_objects o where o.OBJECT_ID<10000;
Table created.

--对表a,b分别收集统计信息（ 建表oracle是不是自动收集统计信息的，建索引会自动收集...这里不多熬述！）
SQL> select count(1) from a;
  COUNT(1)
----------
      9829
SQL> select count(1) from b;
  COUNT(1)
----------
     75328

SQL> set autot trace
SQL> set linesize 250
SQL> set pages 10000
SQL> set timing on
SQL> select (select object_name from b where a.object_id = b.object_id) from a;

9829 rows selected.
Elapsed: 00:00:52.79

Execution Plan
----------------------------------------------------------
Plan hash value: 2657529235

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9520 |   120K|    32   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| B    |   782 | 61778 |     2   (0)| 00:00:01 |
|   2 |  TABLE ACCESS FULL| A    |  9520 |   120K|    32   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("B"."OBJECT_ID"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   10576782  consistent gets
   10546517  physical reads
          0  redo size
     313327  bytes sent via SQL*Net to client
       7724  bytes received via SQL*Net from client
        657  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9829  rows processed

2、对SQL进行优化

a、将标量子查询改写为左外连接（推荐）

   select (select object_name from b where a.object_id = b.object_id) from a;
       改写为:
   select a.object_name from a, (select object_id from b) c where a.object_id = c.object_id(+); 

SQL> select a.object_name from a, (select object_id from b) c where a.object_id = c.object_id(+);

9829 rows selected.
Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|    0 | SELECT STATEMENT  |      |  9520 |   855K|   276   (1)| 00:00:04 |
|*   1 |  HASH JOIN OUTER   |      |  9520 |   855K|   276   (1)| 00:00:04 |
|    2 |   TABLE ACCESS FULL| A    |  9520 |   734K|    32   (0)| 00:00:01 |
|    3 |   TABLE ACCESS FULL| B    | 78203 |   992K|   243   (1)| 00:00:03 |
---------------------------------------------------------------------------

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

   1 - access("A"."OBJECT_ID"="OBJECT_ID"(+))

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
       1987  consistent gets
       1073  physical reads
          0  redo size
     313286  bytes sent via SQL*Net to client
       7724  bytes received via SQL*Net from client
        657  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9829  rows processed
--神奇的一幕出现了 ，从sql的执行时间及逻辑和物理读已经看出了端倪！

b、优化方法二

SQL> create index idx_b on b(object_id);
Index created.

--测试效果

SQL> select (select object_name from b where a.object_id = b.object_id) from a;

9829 rows selected.
Elapsed: 00:00:00.13

Execution Plan
----------------------------------------------------------
Plan hash value: 2937924244

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  9520 |   120K|    32   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| B     |   782 | 61778 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_B |   313 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | A     |  9520 |   120K|    32   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("B"."OBJECT_ID"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
       2652  consistent gets
        144  physical reads
          0  redo size
     313327  bytes sent via SQL*Net to client
       7724  bytes received via SQL*Net from client
        657  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9829  rows processed

小结
      1、改标量子查询为左外连接后，sql的执行计划变成了Hash join，而众所周知哈希连接不论驱动表还是被驱动表都只被扫描一次。这种方法的优化思路，就是通过减少对表扫描的次数来减少IO，从而达到优化的效果。
      2、同理建立索引一样可以达到减少IO的效果，这里就不多熬述了 。

                            
                                                                                                  --End

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

转载于:http://blog.itpub.net/26727294/viewspace-1791185/