二十四、Oracle访问数据三种方式

痛定思痛。 2022-04-12 07:56 162阅读 0赞

#                                              Oracle访问数据三种方式 #

Oracle访问数据主要通过三种办法实现：

通过全表扫描的方式访问数据 
           通过ROWID访问数据 
           通过索引的方式访问数据

### 1、 通过全表扫描访问表(TABLE ACCESS FULL) ###

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
      SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
      SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息 
      SET AUTOTRACE ON ----------------- 包含执行计划和统计信息 
      SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

在PL/SQL中使用F5查看执行计划：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]

### 2、通过ROWID访问表（TABLE ACCESS BY ROWID） ###

ROWID是数据存放在数据库中的物理地址，能够唯一标识表中的一条数据。
       ROWID指出了一条记录所在的数据文件、块号以及行号的位置，因此通过ROWID定位单行数据是最快的方法。
    
       ROWID作为一个伪列，其数值并不存储在数据库中，当查询时才进行计算。
       ROWID除了在同一集簇中可能不唯一外，每条记录的ROWID唯一

--查询记录的rowid
    Yumiko@sunny >select rowid,ename from scott.emp where ename='SMITH';
    
    ROWID              ENAME
    ------------------ ----------
    AAAVREAAEAAAACXAAA SMITH
    
    
    
    --利用rowid查询数据
    Yumiko@sunny >set autotrace on
    Yumiko@sunny >select empno,ename from scott.emp where rowid='AAAVREAAEAAAACXAAA';
    
         EMPNO ENAME
    ---------- ----------
          7369 SMITH
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1116584662
    
    -----------------------------------------------------------------------------------
    | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |      |     1 |    22 |     1   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    22 |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

### 3、通过INDEX访问表 ###

通过索引查找相应数据行的rowid，再根据rowid查找表中实际数据的方式称为“索引查找”或者“索引扫描”。
    
    一个rowid对应一条数据行（根据rowid查找结果，仅需要对rowid相应数据的数据块进行一次I/O操作），因此该方式属于“单块读”。
    
    对于索引，除了存储索引的数据外，还保存有该数据对应的rowid信息。
    
    索引扫描分为两步：1）扫描索引确定相应的rowid信息。    2）根据rowid从表中获得对应的数据。

--查看目标用户的索引对象及其名称
    Yumiko@sunny >select owner,object_name,object_type from dba_objects where owner='SCOTT';
    
    OWNER                          OBJECT_NAME          OBJECT_TYPE
    ------------------------------ -------------------- -------------------
    SCOTT                          PK_DEPT              INDEX
    SCOTT                          DEPT                 TABLE
    SCOTT                          EMP                  TABLE
    SCOTT                          PK_EMP               INDEX
    SCOTT                          BONUS                TABLE
    SCOTT                          SALGRADE             TABLE
    
    
    
    --查看目标索引所在的表及其列信息
    Yumiko@sunny >select index_name,table_name,column_name from dba_ind_columns where index_name='PK_EMP';
    
    INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------ --------------------
    PK_EMP                         EMP                            EMPNO
    
    
    
    --打开会话跟踪
    Yumiko@sunny >set autotrace on
    
    
    
    
    --使用索引列执行查询并查看执行计划
    Yumiko@sunny >select empno,ename from scott.emp where empno='7369';
    
         EMPNO ENAME
    ---------- ----------
          7369 SMITH
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 2949544139
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |     1 |    10 |     1   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]: /images/20220412/a706cd396adb4de389f9e5df3bc65de5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220412/363767f1409b43c19bf036527374937d.png