精华内容
下载资源
问答
  • oracle数据库索引
    千次阅读
    2022-01-04 15:05:22

    1.增加索引

    create index 索引名 on 表名 (字段名) --创建单一索引
      tablespace 数据库名    
       pctfree 10  
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      )
      nologging;
    
    create index 索引名 on 表名 (字段名,字段名) --创建复合索引
      tablespace 数据库名    

    pctfree:预留空间,oracle中指为数据update操作保留的空间百分比,一般默认为10,当数据占用空间超过上限值时,将不再插入数据。只做查询使用的表空间可以根据实际需求适当调小pctfree值。

    initrans:处理每个块中行级锁事务,不建议随意修改默认值

    maxtrans:控制最大并发事务

    initial :索引初始化大小

    next:索引扩展大小

    minextents 1:最少一个块
    maxextents unlimited:无限扩展

    一般情况下索引的上述属性值和建表时设置的值是一致的


    2.删除索引

    drop index 索引名;

    更多相关内容
  • oracle数据库索引失效

    2021-01-19 22:45:53
    今天一个同事突然问我索引为什么失效。说实在的,失效的原因有多种: 但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%...
  • 正在看的ORACLE教程是:Oracle数据库索引的维护。 本文只讨论Oracle中最常见的索引,即是B-tree索引。本文中涉及的数据库版本是Oracle8i。 一. 查看系统表中的用户索引 在Oracle中,SYSTEM表是安装数据库时自动建立...
  • Oracle数据库索引优化方法探析.pdf
  • Oracle数据库索引底层实现原理笔记

    千次阅读 2020-08-19 16:49:20
    当然, 有的数据库也使用哈希桶作用索引的数据结构 , 然而, 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。 我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,...
    • 为什么要给表加上主键?

    • 为什么加索引后会使查询变快?

    • 为什么加索引后会使写入、修改、删除变慢?

    • 什么情况下要同时在两个字段上建索引?
      想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉),也就是b tree或者 b+ tree,重要的事情说三遍:“平衡树,平衡树,平衡树”。当然, 有的数据库也使用哈希桶作用索引的数据结构 , 然而, 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

    我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。

    在这里插入图片描述

    上图就是带有主键的表(聚集索引)的结构图。图画的不是很好, 将就着看。其中树的所有结点(底部除外)的数据都是由主键字段中的数据构成,也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句:

    select * from table where id = 1256;

    首先根据索引定位到1256这个值所在的叶结点,然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节, 但是从上图能看出,树一共有三层, 从根节点至叶节点只需要经过三次查找就能得到结果。如下图

    在这里插入图片描述

    假如一张表有一亿条数据 ,需要查找其中某一条数据,按照常规逻辑, 一条一条的去匹配的话, 最坏的情况下需要匹配一亿次才能得到结果,用大O标记法就是O(n)最坏时间复杂度,这是无法接受的,而且这一亿条数据显然不能一次性读入内存供程序使用, 因此, 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销,以现在磁盘的IO能力和CPU的运算能力, 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树),假设这棵树有10层,那么只需要10次IO开销就能查找到所需要的数据, 速度以指数级别提升,用大O标记法就是O(log n),n是记录总树,底数是树的分叉数,结果就是树的层次数。换言之,查找次数是以树的分叉数为底,记录总数的对数,用公式来表示就是

    在这里插入图片描述

    用程序来表示就是Math.Log(100000000,10),100000000是记录数,10是树的分叉数(真实环境下分叉数远不止10), 结果就是查找次数,这里的结果从亿降到了个位数。因此,利用索引会使数据库查询有惊人的性能提升。

    然而, 事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

    讲完聚集索引 , 接下来聊一下非聚集索引, 也就是我们平时经常提起和使用的常规索引。

    非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 假如给user表的name字段加上索引 , 那么索引就是由name字段中的值构成,在数据改变时, DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。 如下图

    在这里插入图片描述

    每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

    非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,如下图

    在这里插入图片描述

    不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。

    然而, 有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为「覆盖索引」查询, 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出, 当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。

    先看下面这个SQL语句

    //建立索引

    create index index_birthday on user_info(birthday);

    //查询生日在1991年11月1日出生用户的用户名

    select user_name from user_info where birthday = ‘1991-11-1’

    这句SQL语句的执行过程如下

    首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

    然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

    最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

    我们把birthday字段上的索引改成双字段的覆盖索引

    create index index_birthday_and_user_name on user_info(birthday, user_name);

    这句SQL语句的执行过程就会变为

    通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图

    在这里插入图片描述

    数据库索引的大致工作原理就是像文中所述, 然而细节方面可能会略有偏差,这但并不会对概念阐述的结果产生影响 。

    展开全文
  • Oracle数据库索引原理分析

    千次阅读 2019-06-23 16:12:30
    统计信息:描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。 执行计划:一条查询语句在Oracle中的执行过程或访问路径的描述,即执行查询语句的逻辑或方式 HINT:开发人员控制执行计划的接口,可以给...

    1、相关概念介绍

    统计信息:描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。
    执行计划:一条查询语句在Oracle中的执行过程或访问路径的描述,即执行查询语句的逻辑或方式
    HINT:开发人员控制执行计划的接口,可以给sql语句执行具体的执行计划

    2、详细介绍

    Oracle在执行查询语句的时候,会根据统计信息对sql进行评估,选择出最有的执行计划去执行sql语句。所以是否选择使用索引,和统计信息有关。

    2.1、统计信息

    1、统计信息在工作日22:00-02:00会更新,双休日可能全天统计
    2、执行查询时,数据库会根据统计信息评估,选择出相应的执行计划

    2.2、执行计划

    在这里插入图片描述

    2.3、索引(B树)

    索引建立后,会生成一个索引表,使索引和表中数据形成映射;若表中的数据有更新而影响到了索引,那么索引表则要重新生成,所以索引不利于插入、变更频繁的表。

    在这里插入图片描述

    INDEX RANGE SCAN 范围扫描

    首先定位初始位置,然后向后遍历,查询出所有附和条件的索引
    在这里插入图片描述

    INDEX FULL SCAN 索引全扫描

    一般在统计表数据总条数时使用
    在这里插入图片描述

    注意点
    单列索引不存储null,
    多列索引允许存储null,但是不可以全部为null

    2.4、表连接算法

    2.4.1、Next Loop

    缺点:无索引的情况下,表过大,不可用
    适用于OLTP类型的交易,返回处理少量的数据

    算法结构:
    
    For I in (    语句1)
    Do
         语句2
    done
    
    2.4.2、Hash Join

    适用于OLAP型交易,返回或处理大量数据

    缺点:第一张表的关联值过多的相同,消耗CPU

    原理步骤:
    1、先访问table1,按表关联字段进行hash算法
           例如:取余(id为连接字段)
              若mod(table1.id, 1000) = 1,放在第1个桶中
              若mod(table1.id, 1000) = 2,放在第2个桶中
             若mod(table1.id, 1000) = 3,放在第3个桶中
                  …………………………
    
    2、访问table2,按照同样的规则,与对应桶中的记录进行比较,相同,即关联上
    
    

    2.5、简单案例

    场景介绍
    第一天:创建table,插入200W条数据,查询正常,20:00使用delete删除表中的所有记录
    第二天:插入200W条数据,查询时,速度相较于第一天很慢(数量级上的差别)

    分析
    第一天:由于表刚创建,所以table的统计信息为null,所以评估执行计划时,没有依据统计信息
    当天的22:00-02:00,更新table的统计信息,所以对应的统计信息变为数据行数0

    第二天,插入200W条数据后,执行查询时,数据库进行评估时,认为table中的行数为0,所以选择出了较为低效率的执行计划,才会出现上述情况

    3、Oracle结构(补充)

    在这里插入图片描述

    图解
    Database: 数据库

    Tablespace:表空间是一个逻辑容器,它和数据文件关联起来,一个表空间至少有一个数据文件与之关联。一个表空间可以有多个段,一个段只能属于一个表空间。
    Segment:段,是一种在数据库中消耗物理存储空间的任何实体(一个段可能存在于多个数据文件中, 因为物理的数据文件 是组成逻辑表空间的基本物理存储单位)如果一个表不进行分区,那么一个表就是在一个segment中。如果一个表进行多个分区,那么每一个分区就在一个segment。

    Block:块,Oracle存储的最基本单位,由DB_BLOCK_SIZE制定,通常为8KB,也可以定义为2KB,4KB,16KB,32KB,64KB等,磁盘最小存储单位是sector(512BYTE 操作系统概念),Oracle数据块由连续的sector组成,Oracle读写单位是数据块,应尽量设置BLOCK大小为磁盘数据块大小的整数倍,避免IO浪费

    Extent:区又叫盘区,是数据文件中一个连续的分配空间,它比块要大,由块组成。有些对象分配空间时可能至少需要两个盘区,比如回滚段,而这两个盘区不一定要求相连。区的大小从一个块到2GB不等

    oracle中存储的层次结构总结如下

    1、数据库由一个或多个表空间组成
    2、Tablespace可以理解为segments的逻辑容器(包含多个段),由一个或多个数据文件组成
    3、Segment是db对象占有空间的最小单位,一个对象可以包含0个、1个或多个segment,段由一个或多个区组成
    4、Extent是数据文件中一个连续的分配空间,由一个或多个块组成。Extent是空间分配的最小单位,oracle都是以extent为单位为对象分配空间的
    5、Block块是数据库中最小、最基本的空间单位,是数据库使用的最小的I/O单元,数据的读取都是以block的整数倍进行的,通常一个db block通常是一个os block的整数倍大小
    6、Schema可以理解为用户下的所有对象的集合,每个用户都有一个对应的方案

    展开全文
  • 基于Oracle数据库索引的查询优化研究.pdf
  • ORACLE数据库索引的设计与维护研究.pdf
  • 影响Oracle数据库索引的几个因素浅析.pdf
  • Oracle数据库索引讲义.pptx
  • Oracle数据库中的索引

    2021-10-08 12:16:13
    Oracle数据库中的索引


    索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数,从而提高数据访问性能。

    1. 创建索引

    单列索引:

    单列索引是基于单个列所建立的索引,比如:

    create index 索引名 on 表名(列名);

    复合索引:

    复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引,但是要求列的组合必须不同,比如:

    create index emp_idx1 on emp (ename, job);

    create index emp_idx1 on emp (job, ename);

    注意:在创建索引时,尽可能将能筛选大量数据的列放在后面。因为sql语句是从后往前扫描。

    2. 使用原则

    1. 在大表上建立索引才有意义
    2. 在where子句或是连接条件上经常引用的列上建立索引,不要建无意义的索引。
    3. 索引的层次不要超过4层

    3. 索引的缺点

    索引有一些先天不足:

    1. 建立索引,系统要占用大约为表1.2倍的硬盘和内存空间来保存索引。

    2. 更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性。

    实践表明,不恰当的索引不但于事无补,反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

    比如在如下字段建立索引应该是不恰当的:

    1. 很少或从不引用的字段;
    2. 逻辑型的字段,如男或女(是或否)等。

    综上所述,提高查询效率是以消耗一定的系统资源为代价的,索引不能盲目的建立,这是考验一个DBA是否优秀的很重要的指标。

    4. 其它索引

    按照数据存储方式,可以分为B*树、反向索引、位图索引;

    按照索引列的个数分类,可以分为单列索引、复合索引;

    按照索引列值的唯一性,可以分为唯一索引和非唯一索引。

    此外还有函数索引,全局索引,分区索引…

    注意:

    在不同的情况,我们会在不同的列上建立索引,甚至建立不同种类的索引,请记住,技术是死的,人是活的。

    比如:

    B*树索引建立在重复值很少的列上,而位图索引则建立在重复值很多、不同值相对固定的列上。

    5. 显示索引的信息

    显示表的所有索引:

    在同一张表上可以有多个索引,通过查询数据字典视图dba_indexs和user_indexs,可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息,而user_indexs用于显示当前用户的索引信息:

    select index_name, index_type from user_indexes where table_name = ‘表名’;

    显示索引列:

    通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息

    select table_name, column_name from user_ind_columns where index_name = ‘IND_ENAME’;

    可以通过pl/sql developer工具查看索引信息


    参考视频:https://www.bilibili.com/video/BV13W411H768?p=23

    展开全文
  • 通过两个图形说明了在oracle数据库中b-tree索引和位图索引的工作原理
  • Oracle数据库索引失效

    千次阅读 2021-05-03 02:25:07
    Oracle数据库中有一个表,用PL/SQL查看该表的索引没有被DROP掉, 但是表上的数据查询起来很慢(查询时间大概是原来的3倍),后Oracle数据库中有一个表,用PL/SQL查看该表的索引没有被DROP掉,, 但是表上的数据查询...
  • oracle数据库索引种类,分别什么情况下使用 关于索引有些地方我不太明白1.oracle常用的都有哪几种索引,比如唯一索引之类的,sql语句都是怎么写?2.都在什么情况下适合用?3.最正常的语句create index name on..........
  • Oracle数据库索引

    万次阅读 2013-06-14 16:54:22
    -- Oracle默认建立B-tree索引 create index 索引名 on 表名(列名) 复合索引:在两列或多列上建立的索引。在同一张表上可以有多个索引,但是这些索引所包含的列的组合必须不完全相同。如: create index
  • 参考链接 http://blog.itpub.net/14401924/viewspace-659821/
  • 一旦建立了索引,在用户表中建立、更改和删除数据库时, Oracle就自动地维护索引。创建索引时,下列准则将帮助用户做出决定:  1) 索引应该在SQL语句的"where"或"and"部分涉及的表列(也称谓词...
  • 虽然这可以通过一句话来概括优化的索引的基本准则,但是要做到这一点的话,需要数据库管理员做出很大的努力。具体的来说,要做到这个三个适当有如下几个要求。 一、 根据表的大小来创建索引。 虽然给表创建索引,...
  • 创建Oracle 索引的目的是为了避免全表扫描数据,提高查询效率,但是如果sql语句写的不好致使索引失效,反而会影响数据查询效率。以下几种情况就会导致索引失效: 1. 没有 WHERE 子句 众所周知,添加索引的字段必需要...
  • Oracle数据库如何查看表中索引

    千次阅读 2021-03-20 01:18:56
    1、查看表中有哪些索引 select * from user_indexes where table_name = '表名' 或者 select * from all_indexes where table_name = '表名' 2、查看表中索引对应哪些列 select * from user_ind_columns where ...
  • 查询oracle 数据库索引与序列

    千次阅读 2021-07-13 17:45:59
    1、查看表中有哪些索引 select * from user_indexes where table_name = '表名' 2、查看表中索引对应哪些列 select * from user_ind_columns where table_name='表名'
  • 1)概念:Oracle数据库中的索引是一种建立在表或簇基础上的数据对象,和表一样具有独立的段存储结构,需要在表空间中为其分配存储空间。 2)作用:可以提高查询表中数据的速度。 2、索引的类型(面试题) 1)B树索引...
  • oracle数据库索引与sql的优化介绍了访问oracle数据库的一些优化措施
  • Oracle数据库索引的创建

    千次阅读 2018-09-12 19:00:18
    数据库建立的时候 如果表数据和索引数据不需要分开  建表时直接指定主键索引  CREATE TABLE VOAMASTM (   "REFCODE" NUMBER(10) DEFAULT 0 NOT NULL ,   "OWNERREFCODE" NUMBER(10) ...
  • 本课程中重点讲解了关于oracle数据库的相关知识,包含但不限于,Oracle的基本概念,Oracle数据库的创建以及维护,oracle数据库表的创建以及维护,查询和视图详解,索引和权限详解,以及备份和还原,plsql的基础语法...
  • Oracle数据库创建索引

    2021-08-17 10:14:22
    单一索引:Create Index On <Table_Name>(Column_Name); 复合索引:Create Index On emp(deptno,job); —>在emp表的deptno、job列建立索引。  select * from emp where deptno=66 and job='sals' ->走索...
  • 各种Oracle索引类型介绍 逻辑上: Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引 物理上: Partitioned 分区索引 NonPartitioned ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 203,216
精华内容 81,286
关键字:

oracle数据库索引