Oracle Table Access Method

This article records some Oracle table access methods

For the index access method, see:

For the stored procedure of displaying the execution plan, see

(1) TABLE ACCESS FULL

Scan a table from a full table and access the table through a full table scan. Example:

SQL> exec SQL _explain ('select * from t1 ');
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 10 | 80 | 3 (0) | 00:00:01 |
| 1 | table access full | T1 | 10 | 80 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

PL/SQL procedure successfully completed.

(2) TABLE ACCESS BY INDEX ROWID

Use the ROWID obtained by the index to access the table.

Example:

SQL> exec SQL _explain ('select * from t1 where id = 1 ');
Plan hash value: 2347959165
Bytes -------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------
| 0 | select statement | 1 | 8 | 1 (0) | 00:00:01 |
| 1 | table access by index rowid | T1 | 1 | 8 | 1 (0) | 00:00:01 |
| * 2 | index unique scan | T1_PK | 1 | 0 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("ID" = 1)

PL/SQL procedure successfully completed.

(3) LOAD AS SELECT

Insert data to the table by append.

Example:

SQL> exec SQL _explain ('insert/* + append */into t1 select * from t1 ');
Plan hash value: 1069440229
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
---------------------------------------------------------------------------
| 0 | insert statement | 10 | 80 | 3 (0) | 00:00:01 |
| 1 | load as select | T1 |
| 2 | table access full | T1 | 10 | 80 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

PL/SQL procedure successfully completed.

1 2 3 4 Next Page