oraunix 发表于 2010-11-23 18:28:10

做了几个表连接的例子（建议分析一下）

create table t20 as select * from dba_objects;
create table t21 as select * from dba_objects;
insert into t21 select * from dba_objects;//执行多次，提交
alter table t20 add primary key(object_id);

set autotrace trace exp
SQL> select t21.object_name,t20.object_name from t20,t21 where t20.object_id=t21.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 4003806172

-----------------------------------------------------------------------------------
| Id| Operation          | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 70819 |    10M|       |   688   (1)| 00:00:09 |
|*1 |HASH JOIN OUTER   |      | 70819 |    10M|1088K|   688   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| T20| 12224 |   943K|       |    33   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T21| 81377 |6278K|       |   249   (1)| 00:00:03 |
-----------------------------------------------------------------------------------

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

   1 - access("T20"."OBJECT_ID"="T21"."OBJECT_ID"(+))

Note
-----
   - dynamic sampling used for this statement

SQL>

SQL> select t21.object_name,t20.object_name from t20,t21 where t20.object_id(+)=t21.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2925186126

--------------------------------------------------------------------------------------
| Id| Operation             | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 81377 |    12M|       |   688   (1)| 00:00:09 |
|*1 |HASH JOIN RIGHT OUTER|      | 81377 |    12M|1088K|   688   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL   | T20| 12224 |   943K|       |    33   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | T21| 81377 |6278K|       |   249   (1)| 00:00:03 |
--------------------------------------------------------------------------------------

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

   1 - access("T20"."OBJECT_ID"(+)="T21"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement

SQL>create index t21_object_name on t21(object_name);

SQL> select t21.object_name,t20.object_name from t20,t21 where t20.object_id=t21.object_id(+)
2and t21.object_name='T20';

Execution Plan
----------------------------------------------------------
Plan hash value: 2182331313

------------------------------------------------------------------------------------------------
| Id| Operation                  | Name            | Rows| Bytes | Cost (%CPU)| Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   8 |1264 |    18   (0)| 00:00:01 |
|   1 |NESTED LOOPS                |               |   8 |1264 |    18   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T21             |   8 |   632 |    11   (0)| 00:00:01 |
|*3 |    INDEX RANGE SCAN          | T21_OBJECT_NAME |   8 |       |   3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T20             |   1 |    79 |   1   (0)| 00:00:01 |
|*5 |    INDEX UNIQUE SCAN         | SYS_C004758   |   1 |       |   0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   3 - access("T21"."OBJECT_NAME"='T20')
   5 - access("T20"."OBJECT_ID"="T21"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement

SQL>create index t20_object_name on t20(object_name);

SQL> select t21.object_name,t20.object_name from t20,t21 where t20.object_id=t21.object_id(+)
2   and t20.object_name='T20';

Execution Plan
----------------------------------------------------------
Plan hash value: 4061391268

------------------------------------------------------------------------------------------------
| Id| Operation                  | Name            | Rows| Bytes | Cost (%CPU)| Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   6 |   948 |   252   (1)| 00:00:04 |
|*1 |HASH JOIN OUTER             |               |   6 |   948 |   252   (1)| 00:00:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T20             |   1 |    79 |   2   (0)| 00:00:01 |
|*3 |    INDEX RANGE SCAN          | T20_OBJECT_NAME |   1 |       |   1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | T21             | 81377 |6278K|   249   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------

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

   1 - access("T20"."OBJECT_ID"="T21"."OBJECT_ID"(+))
   3 - access("T20"."OBJECT_NAME"='T20')

Note
-----
   - dynamic sampling used for this statement

SQL>


SQL> select t21.object_name,t20.object_name from t20,t21
2where t20.object_id=t21.object_id
3and t20.object_name='T20'
4and t21.object_id >1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3305677955

------------------------------------------------------------------------------------------------
| Id| Operation                  | Name            | Rows| Bytes | Cost (%CPU)| Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |    32 |5056 |   253   (2)| 00:00:04 |
|*1 |HASH JOIN                   |               |    32 |5056 |   253   (2)| 00:00:04 |
|*2 |   TABLE ACCESS BY INDEX ROWID| T20             |   6 |   474 |   2   (0)| 00:00:01 |
|*3 |    INDEX RANGE SCAN          | T20_OBJECT_NAME |   2 |       |   1   (0)| 00:00:01 |
|*4 |   TABLE ACCESS FULL          | T21             | 73427 |5664K|   250   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------

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

   1 - access("T20"."OBJECT_ID"="T21"."OBJECT_ID")
   2 - filter("T20"."OBJECT_ID">1000)
   3 - access("T20"."OBJECT_NAME"='T20')
   4 - filter("T21"."OBJECT_ID">1000)

Note
-----
   - dynamic sampling used for this statement

SQL>create index t21_object_id on t21(object_id);


SQL> list
1select t21.object_name,t20.object_name from t20,t21
2where t20.object_id=t21.object_id
3and t20.object_name='T20'
4* and t21.object_id=1000
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2615206259

----------------------------------------------------------------------------------------------
| Id| Operation                  | Name          | Rows| Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   8 |1264 |    12   (0)| 00:00:01 |
|   1 |NESTED LOOPS                |               |   8 |1264 |    12   (0)| 00:00:01 |
|*2 |   TABLE ACCESS BY INDEX ROWID| T20         |   1 |    79 |   2   (0)| 00:00:01 |
|*3 |    INDEX UNIQUE SCAN         | SYS_C004758   |   1 |       |   1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T21         |   8 |   632 |    10   (0)| 00:00:01 |
|*5 |    INDEX RANGE SCAN          | T21_OBJECT_ID |   8 |       |   1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - filter("T20"."OBJECT_NAME"='T20')
   3 - access("T20"."OBJECT_ID"=1000)
   5 - access("T21"."OBJECT_ID"=1000)

Note
-----
   - dynamic sampling used for this statement

SQL>

SQL> select /*+ use_hash(t20 t21)*/ t21.object_name,t20.object_name from t20,t21
2where t20.object_id=t21.object_id
3and t20.object_name='T20'
4and t21.object_id=1000
5;

Execution Plan
----------------------------------------------------------
Plan hash value: 3146457093

----------------------------------------------------------------------------------------------
| Id| Operation                  | Name          | Rows| Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   8 |1264 |    13   (8)| 00:00:01 |
|*1 |HASH JOIN                   |               |   8 |1264 |    13   (8)| 00:00:01 |
|*2 |   TABLE ACCESS BY INDEX ROWID| T20         |   1 |    79 |   2   (0)| 00:00:01 |
|*3 |    INDEX UNIQUE SCAN         | SYS_C004758   |   1 |       |   1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T21         |   8 |   632 |    10   (0)| 00:00:01 |
|*5 |    INDEX RANGE SCAN          | T21_OBJECT_ID |   8 |       |   1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - access("T20"."OBJECT_ID"="T21"."OBJECT_ID")
   2 - filter("T20"."OBJECT_NAME"='T20')
   3 - access("T20"."OBJECT_ID"=1000)
   5 - access("T21"."OBJECT_ID"=1000)

Note
-----
   - dynamic sampling used for this statement

SQL>

SQL> select /*+ use_nl(t21 t20)*/ t21.object_name,t20.object_name from t20,t21
2where t20.object_id=t21.object_id
3and t20.object_name='T20'
4and t21.object_id=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2615206259

----------------------------------------------------------------------------------------------
| Id| Operation                  | Name          | Rows| Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   8 |1264 |    12   (0)| 00:00:01 |
|   1 |NESTED LOOPS                |               |   8 |1264 |    12   (0)| 00:00:01 |
|*2 |   TABLE ACCESS BY INDEX ROWID| T20         |   1 |    79 |   2   (0)| 00:00:01 |
|*3 |    INDEX UNIQUE SCAN         | SYS_C004758   |   1 |       |   1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T21         |   8 |   632 |    10   (0)| 00:00:01 |
|*5 |    INDEX RANGE SCAN          | T21_OBJECT_ID |   8 |       |   1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - filter("T20"."OBJECT_NAME"='T20')
   3 - access("T20"."OBJECT_ID"=1000)
   5 - access("T21"."OBJECT_ID"=1000)

Note
-----
   - dynamic sampling used for this statement

SQL>


SQL> select /*+ ordered use_nl(t21 t20)*/ t21.object_name,t20.object_name from t20,t21
2where t20.object_id=t21.object_id
3and t20.object_name='T20'
4and t21.object_id=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3839293158

-------------------------------------------------------------------------------------------------
| Id| Operation                     | Name            | Rows| Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   8 |1264 |    12   (0)| 00:00:01 |
|   1 |TABLE ACCESS BY INDEX ROWID| T21             |   8 |   632 |    10   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |               |   8 |1264 |    12   (0)| 00:00:01 |
|*3 |    TABLE ACCESS BY INDEX ROWID| T20             |   1 |    79 |   2   (0)| 00:00:01 |
|*4 |   INDEX RANGE SCAN          | T20_OBJECT_NAME |   2 |       |   1   (0)| 00:00:01 |
|*5 |    INDEX RANGE SCAN         | T21_OBJECT_ID   |   8 |       |   1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   3 - filter("T20"."OBJECT_ID"=1000)
   4 - access("T20"."OBJECT_NAME"='T20')
   5 - access("T21"."OBJECT_ID"=1000)

Note
-----
   - dynamic sampling used for this statement


SQL> select /*+ ordered use_nl(t20 t21)*/ t21.object_name,t20.object_name from t20,t21
2where t20.object_id=t21.object_id
3and t20.object_name='T20'
4and t21.object_id=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3839293158

-------------------------------------------------------------------------------------------------
| Id| Operation                     | Name            | Rows| Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   8 |1264 |    12   (0)| 00:00:01 |
|   1 |TABLE ACCESS BY INDEX ROWID| T21             |   8 |   632 |    10   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |               |   8 |1264 |    12   (0)| 00:00:01 |
|*3 |    TABLE ACCESS BY INDEX ROWID| T20             |   1 |    79 |   2   (0)| 00:00:01 |
|*4 |   INDEX RANGE SCAN          | T20_OBJECT_NAME |   2 |       |   1   (0)| 00:00:01 |
|*5 |    INDEX RANGE SCAN         | T21_OBJECT_ID   |   8 |       |   1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   3 - filter("T20"."OBJECT_ID"=1000)
   4 - access("T20"."OBJECT_NAME"='T20')
   5 - access("T21"."OBJECT_ID"=1000)

Note
-----
   - dynamic sampling used for this statement

SQL>


页: [1]
查看完整版本: 做了几个表连接的例子（建议分析一下）