Oracle访问数据的方法

佚名 6年前 (2019-04-11) 随笔 942人围观抢沙发百度已收录

Oracle表中数据的访问方法分如下几种

1、全表扫描（TABLE ACCESS FULL）

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

　　全表扫描是指Oracle在访问目标表中的数据时，会从该表所占用第一个区（EXTENT）的第一个块（BLOCK）开始扫描，一直扫到该表的高水位（HWM,High Water Mark），这段范围内所有的数据块Oracle都会读到，这期间Oracle会对读到的所有数据施加目标SQL的where条件中指定的过滤条件，最后只返回满足过滤条件的数据。

　　全表扫描的特定是使用多块读，多块读一次I/O能读取多个数据块(db_file_multiblock_read_count参数设定)

　　--示例

　　sql>select * from scott.emp;

　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 3956160932

　　--------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　--------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |
　　| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
　　--------------------------------------------------------------------------

2、ROWID扫描(TABLE ACCESS BY USER ROWID)

　　ROWID扫描是指Oracle在访问目标表中的数据时，直接通过数据所在的ROWID去定位并访问这些数据，是Oracle存取单行数据的最快方法。ROWID表示的是Oracle中数据行记录所在的物理存储地址，其组成为：数据对象编号（6位）+ 相关数据文件编号（3位）+ 数据块编号（6位）+ 数据块中行编号（3位），可以通过dbms_rowid包中的相关方法（dbms_rowid.rowid_relative_fno,dbms_rowid.rowid_block_number,dbms_rowid.rowid_row_number）将ROWID的值翻译成对应数据行的实际物理存储地址。

　　从严格意义上来说，Oracle中的ROWID扫描有两层含义：一种是根据用户在SQL语句中输入的ROWID值直接访问对应的数据行记录；另一种是先访问相关索引，然后根据访问索引得到的ROWID再回表访问对应的数据行记录。

　　--示例

　　sql>select * from scott.emp where rowid='AAAVREAAEAAAACXAAE';

　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 1116584662

　　-----------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　-----------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
　　| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
　　-----------------------------------------------------------------------------------

3、索引扫描

3.1.索引唯一扫描（INDEX UNIQUE SCAN）

　　索引唯一扫描是针对唯一索引的扫描，仅仅适用于where条件是等值查询的目标sql，至多返回一条记录　　

　　--示例

　　sql>select * from scott.emp where empno = 7369;

　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2949544139

　　--------------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　--------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
　　| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
　　|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

3.2.索引范围扫描（INDEX RANGE SCAN）

　　索引范围扫描适用于所有类型的B树索引，可能会返回多条记录。

　　扫描对象是唯一索引时，此时目标SQL的where条件一定是范围查询（谓词条件为BETWEEN、<、>等）

　　扫描对象是非唯一索引时，对目标SQL的where条件没有限制

　　--示例

　　sql>select * from scott.emp where empno < 7369;

　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 169057108

　　--------------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　--------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
　　| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
　　|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
　　--------------------------------------------------------------------------------------

3.3.索引全扫描（INDEX FULL SCAN）

　　索引全扫描也适用于所有类型的B树索引，所谓的索引全扫描就是指要扫描目标索引所有叶子块的所有索引行

　　默认情况下，索引全扫描要从左到右顺序扫描目标索引所有叶子块的所有索引行，而索引是有序的，故索引全扫描的执行结果是有序的，是按照该索引的索引键值列来排序的

　　索引全扫描适用于目标SQL的查询列全部是目标索引的索引键值列的情况，且目标索引至少有一个索引键值列的属性是NOT NULL，因为针对B树索引而言，所有键值列全为NULL值时，这些NULL值是不会在B树索引中存在的

　　--示例

　　sql>select empno from scott.emp;

　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 179099197

　　---------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　---------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
　　| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
　　---------------------------------------------------------------------------

3.4.索引快速全扫描（INDEX FAST FULL SCAN）

　　索引快速全扫描和索引全扫描比较类似，区别如下：

　　　　a.索引快速全扫描只适用于CBO

　　　　b.索引快速全扫描可以使用多块读，也可以并行执行

　　　　c.索引快速全扫描的执行结果不一定是有序的。原因是索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描的

　　--示例

　　sql>select /*+ index_ffs(emp,PK_EMP)*/empno from scott.emp;

　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 366039554

　　-------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　-------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 14 | 56 | 2 (0)| 00:00:01 |
　　| 1 | INDEX FAST FULL SCAN| PK_EMP | 14 | 56 | 2 (0)| 00:00:01 |
　　-------------------------------------------------------------------------------

3.5.索引跳跃式扫描（INDEX SKIP SCAN）

　　索引跳跃式扫描适用于复合索引，where条件中没有对目标索引的前导列指定查询条件但是对该索引的非前导列指定了查询条件的目标SQL会用到

　　Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少，索引非前导列的可选择性又非常好的情况

　　--示例

　　sql>create table t1(id number,sex varchar2(1),age number);

　　sql>alter table t1 modify id not null;

　　sql>create index idx_t1 on t1(sex,id);

　　sql>begin
　　for i in 1 .. 50000
　　loop
　　insert into t1 values(i, 'F');
　　end loop;
　　commit;
　　end;
　　/
　　sql>begin
　　for i in 50001 .. 100000
　　loop
　　insert into t1 values(i, 'M');
　　end loop;
　　commit;
　　end;
　　/

　　sql>analyze table t1 compute statistics for table;

　　sql>analyze table t1 compute statistics for all indexes;

　　sql>analyze table t1 compute statistics for all indexed columns;

　　sql>select * from id = 1000;

　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2416351759

　　--------------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　--------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 10 | 4 (0)| 00:00:01 |
　　| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 4 (0)| 00:00:01 |
　　|* 2 | INDEX SKIP SCAN | IDX_T1 | 1 | | 3 (0)| 00:00:01 |
　　--------------------------------------------------------------------------------------