在OLTP中，表访问的几种常规操作

在OLTP中，表访问的几种常规操作
1、table access(full)：全表扫描，通过扫描全表的方式来访问表，这样的表操作，一般情况下是不想看到的
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1391359473
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| E    |     1 |   115 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

2、load as select:以append的方式向表中插入数据，直接在hwm上直接插入数据
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |           | 13342 |  1172K|    50   (2)| 00:00:01 |
|   1 |  LOAD AS SELECT    | T_OBJECTS |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_OBJECTS | 13342 |  1172K|    50   (2)| 00:00:01 |
--------------------------------------------------------------------------------

3、load table conventional:常规路径插入会在hwm之下搜索可重用的数据块
--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |           |     1 |    90 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T_OBJECTS |       |       |            |          |
--------------------------------------------------------------------------------------

4、table access(by index rowid):通过由索引中获取到的rowid访问表
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    90 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS       |     1 |    90 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX01_T_OBJECTS |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:A))

5、table access by local index rowid:通过由本地分区索引中获取到的rowid，然后再通过rowid来访问表
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |     2 |    92 |    71   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |                       |     2 |    92 |    71   (0)| 00:00:01 |     1 |    42 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_EXP_TRACE_022       |     2 |    92 |    71   (0)| 00:00:01 |     1 |    42 |
|*  3 |    INDEX RANGE SCAN                | IDX01_T_EXP_TRACE_022 |     2 |       |    43   (0)| 00:00:01 |     1 |    42 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"=:A)

6、table access(by global index rowid):通过由全局分区索引中获取到的rowid访问表。注意，全局分区索引并不一定建立在分区表上，也可以建立非分区表上
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |     2 |    92 |    30   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_EXP_TRACE_022       |     2 |    92 |    30   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | IDX02_T_EXP_TRACE_022 |     2 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("WAYBILL_NO"=:A)
   
7、table access(by user rowid)通过rowid来访问表
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |     1 |    46 |     1   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY USER ROWID| T_EXP_TRACE_022 |     1 |    46 |     1   (0)| 00:00:01 | ROWID | ROWID |
--------------------------------------------------------------------------------------------------------------
8 rows selected.

8、table access(by rowid range),通过一个范围的rowid来访问表
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     3 |   138 |   264   (1)| 00:00:04 |       |       |
|   1 |  PARTITION RANGE ALL         |                 |     3 |   138 |   264   (1)| 00:00:04 |     1 |    42 |
|*  2 |   TABLE ACCESS BY ROWID RANGE| T_EXP_TRACE_022 |     3 |   138 |   264   (1)| 00:00:04 |     1 |    42 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(ROWID>=CHARTOROWID(:A))
   
9、table access(sample) ,table access(sample by rowid range),采样访问表，是以多块读取的方式扫描表的数据块。
--------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |    55 |  2530 |   264   (1)| 00:00:04 |       |       |
|   1 |  PARTITION RANGE ALL |                 |    55 |  2530 |   264   (1)| 00:00:04 |     1 |    42 |
|   2 |   TABLE ACCESS SAMPLE| T_EXP_TRACE_022 |    55 |  2530 |   264   (1)| 00:00:04 |     1 |    42 |
--------------------------------------------------------------------------------------------------------
9 rows selected.

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     3 |   138 |   264   (1)| 00:00:04 |       |       |
|   1 |  PARTITION RANGE ALL                |                 |     3 |   138 |   264   (1)| 00:00:04 |     1 |    42 |
|*  2 |   TABLE ACCESS SAMPLE BY ROWID RANGE| T_EXP_TRACE_022 |     3 |   138 |   264   (1)| 00:00:04 |     1 |    42 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(ROWID>=CHARTOROWID(:A))