ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle sql调优系列3(外联接)

oracle sql调优系列3(外联接)

原创 Linux操作系统 作者:alsrt 时间:2012-06-06 14:17:14 0 删除 编辑
一:左右连接性能测试
 
SELECT e.deptno FROM  DEPT e,emp b WHERE e.DEPTNO=b.DEPTNO(+);
SQL> /
    DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20
    DEPTNO
----------
        30
        20
        10
        40
15 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2702949024
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |    84 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |         |    14 |    84 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="B"."DEPTNO"(+))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed
 
----------------------------------------------------------------------------------------------
SQL> SELECT /*+ leading(emp)   */e.deptno FROM  DEPT e,emp b WHERE e.DEPTNO=b.DEPTNO(+);
    DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20
    DEPTNO
----------
        30
        20
        10
        40
15 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2702949024
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |    84 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |         |    14 |    84 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="B"."DEPTNO"(+))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed
 
--------------------------------------------------------------------------------------------------
 
SQL> SELECT /*+ ordered use_hash(emp,dept)  */e.deptno FROM  DEPT e,emp b WHERE e.DEPTNO=b.DEPTNO(+);
    DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20
    DEPTNO
----------
        30
        20
        10
        40
15 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2702949024
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |    84 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |         |    14 |    84 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="B"."DEPTNO"(+))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed
 
SQL> set autot off
SQL> alter table DEPT disable constraint PK_DEPT;
Table altered.

SQL>  select * from user_indexes where table_name='DEPT';
no rows selected

SQL> set autotr on
SQL> SELECT e.deptno FROM  DEPT e,emp b WHERE e.DEPTNO=b.DEPTNO(+);
    DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20
    DEPTNO
----------
        30
        20
        10
        40
15 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3713469723
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |    84 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |    14 |    84 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="B"."DEPTNO"(+))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processe
 

SQL> create index emp_dept_NO_index on emp(deptno);
SQL> create index emp_dept_NO_index on emp(deptno);
Index created.
SQL> exec dbms_stats.gather_table_stats('scott','EMP',cascade => TRUE);
PL/SQL procedure successfully completed.
Commit complete.
 
SQL>  SELECT /*+ leading(emp)   */e.deptno FROM  DEPT e,emp b WHERE e.DEPTNO=b.DEPTNO(+);
    DEPTNO
----------
        10
        10
        10
        20
        20
        20
        20
        20
        30
        30
        30
    DEPTNO
----------
        30
        30
        30
        40
15 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1693505635
--------------------------------------------------------------------------------
--------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT   |                   |    14 |    84 |     1   (0)| 00
:00:01 |
|   1 |  NESTED LOOPS OUTER|                   |    14 |    84 |     1   (0)| 00
:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT           |     4 |    12 |     1   (0)| 00
:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPT_NO_INDEX |     4 |    12 |     0   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."DEPTNO"="B"."DEPTNO"(+))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed
 
 
 
总结:可以看出,当连接查询是左连接的时候,驱动表是固定不变的,不管你加不加hint,因此,同时当连接条件都有索引的时候,是都可以走索引的.
 
 
二:全连接性能测试
 
待续
 
 
 
 
 

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-02

  • 博文量
    34
  • 访问量
    33699