首页 > 其他分享 >4.访问索引的方法

4.访问索引的方法

时间：2023-04-02 11:13:07浏览次数：45  
标签：02 10 00 04 访问 索引 scott 2023 方法

1.索引唯一性扫描

   仅适用于where条件里是等值查询的目标SQL，因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果之多只会返回一条记录

2.索引范围扫描

   目标SQL的where条件一定是范围查询（谓词条件为BETWEEN,<,>等）,当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制。索引范围扫描的结果可能会返回多条记录，其实就是索引范围扫描中范围，在同等条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读至少会比响应的索引唯一性扫描的逻辑读多1

scott@ORCLPDB01 2023-04-02 10:15:22> create table emp_temp as select * from emp;

Table created.

Elapsed: 00:00:00.14
scott@ORCLPDB01 2023-04-02 10:15:45> create unique index idx_emp_temp on emp_temp(empno);

Index created.

Elapsed: 00:00:00.01

scott@ORCLPDB01 2023-04-02 10:19:44> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.34

--清空库缓存
sys@ORCL 2023-04-02 10:15:30> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.16
sys@ORCL 2023-04-02 10:16:41> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02


scott@ORCLPDB01 2023-04-02 10:19:52> set autot trace
scott@ORCLPDB01 2023-04-02 10:20:20> select * from emp_temp where empno = 7369;

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3451700904

--------------------------------------------------------------------------------------------
| 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_TEMP	   |	 1 |	38 |	 1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | IDX_EMP_TEMP |	 1 |	   |	 0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7369)


Statistics
----------------------------------------------------------
	150  recursive calls
	 43  db block gets
	217  consistent gets
	 32  physical reads
       6604  redo size
	961  bytes sent via SQL*Net to client
	613  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	 13  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

scott@ORCLPDB01 2023-04-02 10:20:34> drop index IDX_EMP_TEMP;

Index dropped.

Elapsed: 00:00:00.12
scott@ORCLPDB01 2023-04-02 10:20:52> create index idx_emp_temp on emp_temp(empno);

Index created.

Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 10:21:02> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26

--清空库缓存
sys@ORCL 2023-04-02 10:15:30> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.16
sys@ORCL 2023-04-02 10:16:41> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02


scott@ORCLPDB01 2023-04-02 10:21:23> select * from emp_temp where empno = 7369;

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 1638992559

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7369)


Statistics
----------------------------------------------------------
	169  recursive calls
	  0  db block gets
	275  consistent gets
	 36  physical reads
	  0  redo size
       1105  bytes sent via SQL*Net to client
	407  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	 27  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

3.索引全扫描

   扫描目标索引所有叶子快的所有索引行。

scott@ORCLPDB01 2023-04-02 10:32:41> set autot trace
scott@ORCLPDB01 2023-04-02 10:32:46> select empno from emp;

14 rows selected.

Elapsed: 00:00:00.00

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 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  2  consistent gets
	  0  physical reads
	  0  redo size
	710  bytes sent via SQL*Net to client
	383  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 14  rows processed

sorts（memory）和sorts（disk）的值均为0，说明已经按照empno排序好了

4.索引快速全扫描

   4.1索引快速扫描只适用于CBO

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

   4.3索引快速全扫描的执行结果并不一定是有序的

scott@ORCLPDB01 2023-04-02 10:42:19> create table emp_test(empno number,col1 char(2000),col2 char(2000),col3 char(2000));

Table created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:42:29> alter table emp_test add constraint pk_emp_test primary key (empno,col1,col2,col3);

Table altered.

Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:42:37> insert into emp_test select empno,ename,job,'A' from emp;

14 rows created.

Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 10:43:13> insert into emp_test select empno,ename,job,'B' from emp;

14 rows created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:20> insert into emp_test select empno,ename,job,'C' from emp;

14 rows created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:28> insert into emp_test select empno,ename,job,'D' from emp;

14 rows created.

Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:43:33> insert into emp_test select empno,ename,job,'E' from emp;

14 rows created.

Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:43:37> insert into emp_test select empno,ename,job,'F' from emp;

14 rows created.

Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:43:46> insert into emp_test select empno,ename,job,'G' from emp;

14 rows created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:49> commit;

Commit complete.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:43:52> select count(*) from emp_test;

  COUNT(*)
----------
	98

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:44:08> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
scott@ORCLPDB01 2023-04-02 10:45:40> set autot on;
scott@ORCLPDB01 2023-04-02 10:48:20> select /* index_ffs(emp_test pk_emp_test) */ empno from emp_test;

     EMPNO
----------
      7499
      7521
      7369
      7839
      7844
      7566
      7654
      7698
      7782
      7788
      7876
      7900
      7934
      7369
      7499
      7902
      7521
      7566
      7654
      7698
      7782
      7788
      7876
      7839
      7844
      7900
      7902
      7934
      7369
      7499
      7654
      7566
      7521
      7698
      7782
      7844
      7839
      7788
      7876
      7900
      7369
      7499
      7521
      7934
      7902
      7698
      7876
      7782
      7788
      7839
      7844
      7566
      7654
      7902
      7934
      7369
      7900
      7521
      7499
      7782
      7788
      7839
      7844
      7876
      7566
      7900
      7654
      7902
      7698
      7934
      7934
      7844
      7934
      7698
      7369
      7369
      7499
      7521
      7499
      7876
      7566
      7782
      7654
      7521
      7698
      7782
      7900
      7788
      7566
      7788
      7839
      7844
      7876
      7902
      7900
      7654
      7839
      7902

98 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3550420785

------------------------------------------------------------------------------------
| Id  | Operation	     | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		   |	98 |   392 |	28   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| PK_EMP_TEST |	98 |   392 |	28   (0)| 00:00:01 |
------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	251  consistent gets
	  0  physical reads
	  0  redo size
       2296  bytes sent via SQL*Net to client
	492  bytes received via SQL*Net from client
	  8  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 98  rows processed

5.索引跳跃扫描

  5.1.扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描

scott@ORCLPDB01 2023-04-02 10:50:00> create table employee(gender varchar2(1),employee_id number);

Table created.

Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:50:34> alter table employee modify(employee_id not null);

Table altered.

Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 10:51:06> create index idx_employee on employee(gender,employee_id);

Index created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 10:51:42> begin
  2  for i in 1..5000 loop
  3  insert into employee values('F',i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
scott@ORCLPDB01 2023-04-02 10:52:35> begin
  2  for i in 5001..10000 loop
  3  insert into employee values('M',i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
scott@ORCLPDB01 2023-04-02 10:54:11> set autot trace;
scott@ORCLPDB01 2023-04-02 10:54:15> select * from employee where employee_id = 10;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728

------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	   1 |	  15 |	   7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE |	   1 |	  15 |	   7   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEE_ID"=10)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  5  recursive calls
	  0  db block gets
	 49  consistent gets
	  0  physical reads
	  0  redo size
	631  bytes sent via SQL*Net to client
	411  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

scott@ORCLPDB01 2023-04-02 10:54:31> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 10:55:39> select * from employee where employee_id = 10;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 461756150

---------------------------------------------------------------------------------
| Id  | Operation	 | Name 	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		|     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_EMPLOYEE |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEE_ID"=10)
       filter("EMPLOYEE_ID"=10)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 24  consistent gets
	  0  physical reads
	  0  redo size
	631  bytes sent via SQL*Net to client
	411  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

 

标签：02,10,00,04,访问,索引,scott,2023,方法
From： https://www.cnblogs.com/yuanzijian/p/17280079.html

相关文章

  • java面向对象编程-方法回顾
    方法回顾和加深方法的定义修饰符返回类型方法名：注意规范，见名知意参数列表：参数类型参数名异常抛出：后面讲解  方法的调用静态方法非静态方法形参和实参值传递和引用传递this关键字    ......
  • 使用mybatis-plus方法自动生成代码（1）
    首先，在项目的pom.xml文件中添加如下依赖：<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-generator</artifactId><version>3.4.3</version></dependency><dependency><groupId&......
  • 使用vue四种方法写一个计算器
    第一种：使用computed计算属性1.创建项目，引入vue<scripttype="text/javascript"src="js/vue.js"></script>2.实例化vue<divid="app"></div><script>varvm=newVue({el:"#app",//通过el与di......
  • springmvc中前端调用controller方法路径问题
          以前学习springmvc时没有理解前端访问controller中方法路径问题，做项目时刚好遇到，百度了一下没有找到想要的答案，后来突然就顿悟了。。。。。，虽然很基础但还是记录了一下，大佬请见谅。controller中主要用@RequestMapping注解来定义访问路径，一般定义一个类加载路径......
  • springmvc中前端调用controller方法路径问题
          以前学习springmvc时没有理解前端访问controller中方法路径问题，做项目时刚好遇到，百度了一下没有找到想要的答案，后来突然就顿悟了。。。。。，虽然很基础但还是记录了一下，大佬请见谅。controller中主要用@RequestMapping注解来定义访问路径，一般定义一个类加载路径......
  • 防止表单重复提交的4种方法
    幂等性效果：系统对某接口的多次请求，都应该返回同样的结果！（网络访问失败的场景除外）目的：避免因为各种原因，重复请求导致的业务重复处理重复请求场景案例：1，客户端第一次请求后，网络异常导致收到请求执行逻辑但是没有返回给客户端，客户端的重新发起请求2，客户端迅速点击按钮提交，导致同一......
  • 防止表单重复提交的4种方法
    幂等性效果：系统对某接口的多次请求，都应该返回同样的结果！（网络访问失败的场景除外）目的：避免因为各种原因，重复请求导致的业务重复处理重复请求场景案例：1，客户端第一次请求后，网络异常导致收到请求执行逻辑但是没有返回给客户端，客户端的重新发起请求2，客户端迅速点击按钮提交，导致同一......
  • 建模学习方法
    如何在全国大学生数学建模中胜出 CAJ下载 PDF下载永久保存本文,请下载至本地李顺勇 闫卫平 张晓琴山西大学数学科学学院导出/参考文献 分享 打印摘    要：全国大学生数学建模竞赛已经成为高等院校学生在校期间展示自身能力的一个平台。这项高水平的竞赛引起......
  • JAVA-方法
    1.1方法的定义[修饰符列表] 返回值类型方法名（第一个首字母小写，后边单词大写）（形参列表）{方法体}；ps：方法遵循自上而下运行1.2方法调用类名.方法名（实参列表）方法调用时，压栈！结束时弹栈！先进后出！   1.2方法重载1.2.1定义......
  • java方法-稀疏数组
    稀疏数组当一个数组中大部分元素为0，或者为同一值的数组时，可以使用稀疏数组来保存该数组稀疏数组的处理方式是：记录数组一共有几行几列，有多少个不同值把具体不同值的元素和行列及值记录在一个小规模的数组中，从而缩小程序的规模如图：左原始数组，右稀疏数组 ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99