Oracle索引游标学习

本周学习目标：

对oracle表索引要有了解，了解索引运用原理以及复杂SQL中索引的作用，为后面优化SQL以及编写复杂逻辑SQL并保证查询效率打下基础 学习内容： oracle基本索引学习，SQL窗口执行计划（F5）了解 学习要求： 掌握oracle一些基本索引，了解索引的分类以及基本功能，学会分析复杂SQL的执行过程和索引使用情况，分析查询的表是走了全表扫描还是索引

1索引

**背景：**在关系型数据库如MySQL、Oracle中，由于数据库表数据是直接存放在硬盘中，受限于硬盘的读写速度限制。当表中数据量过大时，数据的查找效率便会受到影响。如何快速根据查询条件定位到数据位置，减少IO操作，实现数据快速查询。这便是索引存在的意义。
**概念：**索引是真实存在的数据库对象，最常用的索引底层数据结构是基于B树（左子节点永远小于右子节点）实现的，类似于数据目录，是为了加快对数据的搜索速度，减少全表扫描设计（通过B-树划分范围，快速定位数据位置）。它有自己的存储空间，与表独立存放，一般会将索引建立在常被用作查询条件的一个或多个列上。
分类：
唯一索引：键值不重复的列（如主键或使用Unique约束的列） 非唯一索引：键值可以重复的列（但最好应该保证单个值的唯一性在5%以内） ---------------------------------------------------------------------------------------------------单行索引：使用多个列作为一个索引 复合索引：使用多个列作为一个索引（使用时需要保证查询条件列与索引的列顺序一致） ---------------------------------------------------------------------------------------------------反向索引：当使用某列作为索引时，该列值正向排布不容易分区（如开始的字符串值相同），可以使用反向索引，将列值倒过来进行分区。 ---------------------------------------------------------------------------------------------------位图索引：使用场景是索引列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。 --------------------------------------------------------------------------------------------------- 聚集索引：聚集索引记录的是表数据列排列的物理位置，而其他普通索引是记录的逻辑位置。且聚集索引单表内只能存在一个，聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。
语法：
创建：
隐式创建：当创建表时如果定义了主键或者以UNIQUE唯一约束对列进行定义时，Oracle会自动为该列创建唯一索引。
显式创建：CREATE INDEX 索引名 ON 表名 (字段…);也可以使用plsql工具可视化创建。
**使用：**索引一旦创建，在查询的时候就会自动使用，不需要人为指定使用（可以在查询语句执行后按F5查看索引是否生效）。
删除：DROP INDEX 索引名; 也可以使用plsql工具可视化删除。
缺陷： 1. 使用空间换时间，索引数据可能要占用大量的存储空间。 2. 索引虽然改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，数据库必须动态地更新索引。 3.要限制表中索引的数目。索引越多，在修改表时对索引做出修改的工作量越大 4. 并非所有数据都适合于索引。唯一性不好的数据列作为索引并不一定会获得显著的查询效率提升。 应用场景： 1． 对查询效率有较高要求的业务； 2． 数据库表中的数据量太多时； 3． 数据库表中增删改操作较少，索引无需经常维护； 4． 经常作为查询条件的列或列组合，或者order by字段； 5． 选择适当的索引类型来针对不同的业务场景； 6． 唯一性较好的数据列，才有显著的查询效率提升；
执行计划： 查询语句执行后，如何判断查询中是否使用索引，可以在plsql可视化编辑器中按F5查看执行计划。 Description:当前的数据库操作； 对象名称：操作的数据库对象（如果索引生效会显示索引名称）； 耗费：表示当前操作耗费的性能，越低表示性能越优异。 TABLE ACCESS FULL：表示走的是全表扫描，未使用索引； INDEX RANGE SCAN:使用了索引范围查询，当列索引为唯一索引且where条件跟的是范围条件（>,<,between等），或者索引为非唯一索引是会使用范围查询。 INDEX UNIQUE SCAN：使用的索引唯一性查询，当列索引为唯一索引且where条件后跟的是等值查询，使用索引唯一性查询，至多返回一条数据。
2游标
概念： 游标是 sql 的一个内存工作区，由系统或用户以变量的形式定义，用于临时存储从数据库中提取的结果集，然后通过指针遍历结果集。 作用：将结果集一次性查出并临时存储在内存中进行，这样可以提升数据读写的效率（减少了硬盘数据读写次数）。 分类：游标分为静态游标和REF游标，其中静态游标包括隐式游标和显式游标两种。 隐式游标：数据库增删改查和单行查询语句都会自动创建隐式游标，隐式游标会自动声明、打开和关闭。 显式游标（cursor）:显式游标使用需要经过四个步骤： 1. 声明游标：CURSOR 游标名（可带参数） IS 查询语句结果集； 2. 打开游标：OPEN 游标名（实参）； 3. 提取数据；FETCH 游标名 INTO 变量（如果需要遍历的话可以使用循环）： Loop Fetch FETCH 游标名 INTO 变量 Exit when cursorVar%notfound; （没有下一条时退出循环） 编写逻辑； End loop； 4. 关闭游标；CLOSE 游标名； 5. 涉及增删改操作后需要提交事务； 动态游标（SYS_REFCURSOR）： 1. 声明游标：游标名（可带参数）SYS_REFCURSOR； 2. 打开游标：OPEN 游标名 FOR 查询语句结果集； 3. 提取数据：与显示游标相同； 4. 关闭游标：CLOSE 游标名； 动态游标更加灵活，常用于数据集不确定的场景。