一:左右连接性能测试
SELECT e.deptno FROM DEPT e,emp b WHERE e.DEPTNO=b.DEPTNO(+);
SQL> /
SQL> /
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
----------
20
30
30
20
30
30
10
20
10
30
20
DEPTNO
----------
30
20
10
40
----------
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 |
------------------------------------------------------------------------------
| 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
----------
20
30
30
20
30
30
10
20
10
30
20
DEPTNO
----------
30
20
10
40
----------
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 |
------------------------------------------------------------------------------
| 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
----------
20
30
30
20
30
30
10
20
10
30
20
DEPTNO
----------
30
20
10
40
----------
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 |
------------------------------------------------------------------------------
| 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;
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
----------
20
30
30
20
30
30
10
20
10
30
20
DEPTNO
----------
30
20
10
40
----------
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 |
---------------------------------------------------------------------------
| 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
----------
10
10
10
20
20
20
20
20
30
30
30
DEPTNO
----------
30
30
30
40
----------
30
30
30
40
15 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1693505635
--------------------------------------------------------------------------------
--------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
me |
--------------------------------------------------------------------------------
--------
--------
| 0 | SELECT STATEMENT | | 14 | 84 | 1 (0)| 00
:00:01 |
:00:01 |
| 1 | NESTED LOOPS OUTER| | 14 | 84 | 1 (0)| 00
:00:01 |
:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00
:00:01 |
:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPT_NO_INDEX | 4 | 12 | 0 (0)| 00
:00:01 |
: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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8410760/viewspace-732047/