图文精华

发表新帖

广告

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

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(+)
  2  and 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
  2  where t20.object_id=t21.object_id
  3  and t20.object_name='T20'
  4  and 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
  1  select t21.object_name,t20.object_name from t20,t21
  2  where t20.object_id=t21.object_id
  3  and 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
  2  where t20.object_id=t21.object_id
  3  and t20.object_name='T20'
  4  and 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
  2  where t20.object_id=t21.object_id
  3  and t20.object_name='T20'
  4  and 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
  2  where t20.object_id=t21.object_id
  3  and t20.object_name='T20'
  4  and 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
  2  where t20.object_id=t21.object_id
  3  and t20.object_name='T20'
  4  and 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>


标签： 暂无标签
oraunix

写了 199 篇文章，拥有财富 1026，被 339 人关注

0
举报 收藏
转播转播 分享分享 分享淘帖
回复

使用道具

成为第一个吐槽的人

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

关于我们· 联系我们· 加入我们· 聚知识· 合作伙伴      

© 2020 iiDBA Powered by Discuz! X3.4 Comsenz Inc.

iiDBA / Smrz 京ICP备12024223-8号 Wxb 不良信息举报电话：13718043309

意见
反馈