达梦聚集索引

已于 2022-08-24 15:50:55 修改
阅读量2.6k 收藏 5
点赞数
文章标签： java sql 数据库
于 2022-06-22 15:37:44 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_32856657/article/details/125410328
版权

聚集索引

1.聚集索引简介

        表（列存储表和堆表除外）都是使用 B+树(以下简称 B 树)索引结构管理的，每一个普通表都有一个聚集索引，数据通过聚集索引键排序，根据聚集索引键可以快速查询任何记录。（即表是一个索引，这个索引名称叫聚集索引，可以理解为创建一个表后，将所有字段放在一起建立一个复合索引，只不过这个不需要我们来创建，系统自动给我们维护了一个）

        当建表语句未指定聚集索引键，DM 的默认聚集索引键是ROWID，即记录默认以 ROWID在页面中排序。ROWID 是 B 树为记录生成的逻辑递增序号，表上不同记录的 ROWID 是不一样的，并且最新插入的记录 ROWID 最大。很多情况下，以 ROWID 建的默认聚集索引并不能提高查询速度，因为实际情况下很少人根据 ROWID来查找数据。

验证默认聚集索引键是ROWID

2.数据准备

create table USERS (id number(12),name varchar2(20) not null,age int not null);

insert into USERS values(0001,'Tony',20);

insert into USERS values(0002,'Bill',21);

insert into USERS values(0003,'Jack',18);

insert into USERS values(0004,'Mark',20);

insert into USERS values(0005,'Lily',24);

insert into USERS values(0006,'Kobe',28);

insert into USERS values(0007,'James',29);

insert into USERS values(0008,'Henry',30);

insert into USERS values(0009,'Lisa',20);

insert into USERS values(0010,'Mila',18);

commit;

查询此表上的索引

select id,owner,name,index_type,table_name,table_type from SYSOBJECTS ob

join DBA_INDEXES idx

on ob.name=idx.index_name

where idx.owner='SYSDBA'

and idx.table_name='USERS';

        可以看到有生成了一个名为INDEX33555473的索引，索引类型是CLUSTER。

查看索引列：

select t1.owner,t1.index_name,t3.object_id,t1.index_type,t1.uniqueness,t2.table_name,t2.column_name from dba_indexes  t1

inner join user_ind_columns t2

on t1.index_name=t2.index_name

inner join dba_objects t3

on t1.index_name = t3.object_name

where t1.owner='SYSDBA';

查询结果为空，表示该索引不是表中的字段列，所以可以怀疑索引列是rowid。

查看该索引的定义，此索引在客户端中无法查看到。

select dbms_metadata.get_ddl(OBJECT_TYPE => 'INDEX',NAME=>upper('INDEX33555473'),SCHNAME => 'SYSDBA');

CREATE CLUSTER  INDEX "INDEX33555473" ON "SYSDBA"."USERS"( STORAGE(ON "MAIN", CLUSTERBTR) ;

可以看到，该索引的定义里面也没有指定索引列。

查询

select * from users where id=2;

从执行计划可以看到该查询走了聚集扫描（CLUSTER SCAN）。从这里也能看出来，INDEX33555473存储了USERS表的全部数据。

select rowid,* from users where rowid=2;

可以看到走的是聚集索引扫描（CLUSTER INDEX SEEK）。所以就可以验证聚集索引是以rowid为索引键的。

另外默认以rowid为索引键的聚集索引无法删除。

drop index INDEX33555473;

重建聚集索引后，该默认聚集索引则会自动删除。

create  cluster index idx_users_id on users(id);

select id,owner,name,index_type,table_name,table_type from SYSOBJECTS ob

join DBA_INDEXES idx

on ob.name=idx.index_name

where idx.owner='SYSDBA'

and idx.table_name='USERS';

可以看到默认的聚集索引已经不存在。

这时聚集索引是可以删除的，删除后原先的聚集索引会自动恢复。

drop index idx_users_id;

select id,owner,name,index_type,table_name,table_type from SYSOBJECTS ob

join DBA_INDEXES idx

on ob.name=idx.index_name

where idx.owner='SYSDBA'

and idx.table_name='USERS';

                  

但是聚集索引的名字变了，由原来的INDEX33555473变成了INDEX33555475。

DM 建表时提供三种方式供用户指定聚集索引键：

1

CLUSTER PRIMARY KEY

指定列为聚集索引键，并同时指定为主键，称为聚簇主键

2

CLUSTER KEY

指定列为聚集索引键，但是是非唯一的

3

CLUSTER UNIQUE KEY

指定列为聚集索引键，并且是唯一的

第一种方式受PK_WITH_CLUSTER参数影响，该参数为1才生效，参数功能如下：

3.PK_WITH_CLUSTER

PK_WITH_CLUSTER

1

动态，会话级

在建表语句中指定主关键字时，是否缺省指定为 CLUSTER，0：不指定；1：指定

注：该参数对列存储表和堆表无效

第一种方式在PK_WITH_CLUSTER=1时CLUSTER可以不写。如果指定CLUSTER关键字，无论PK_WITH_CLUSTER是0还是1都会创建聚集索引。

drop table USERS;

SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',1);

create table USERS (

id number(12) primary key,

name varchar2(20) not null,

age int not null);

select t1.owner,t1.index_name,t3.object_id,t1.index_type,t1.uniqueness,t2.table_name,t2.column_name from dba_indexes  t1

inner join user_ind_columns t2

on t1.index_name=t2.index_name

inner join dba_objects t3

on t1.index_name = t3.object_name

where t1.owner='SYSDBA';

此时可以看到USERS表会有一个主键约束

--定义如下：

alter table "SYSDBA"."USERS" add constraint "CONS134218775" primary key("ID");

删除会报如下错误：

drop table USERS;

SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',0);

create table USERS (

id number(12) primary key,

name varchar2(20) not null,

age int not null);

select t1.owner,t1.index_name,t3.object_id,t1.index_type,t1.uniqueness,t2.table_name,t2.column_name from dba_indexes  t1

inner join user_ind_columns t2

on t1.index_name=t2.index_name

inner join dba_objects t3

on t1.index_name = t3.object_name

where t1.owner='SYSDBA';

可以看到索引类型是NORMAL，不是CLUSTER。

select id,owner,name,index_type,table_name,table_type from SYSOBJECTS ob

join DBA_INDEXES idx

on ob.name=idx.index_name

where idx.owner='SYSDBA'

and idx.table_name='USERS';

可以看到有两个索引。

drop table USERS;

SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',1);

create table USERS (

id number(12)  CLUSTER KEY,

name varchar2(20) not null,

age int not null);

select t1.owner,t1.index_name,t3.object_id,t1.index_type,t1.uniqueness,t2.table_name,t2.column_name from dba_indexes  t1

inner join user_ind_columns t2

on t1.index_name=t2.index_name

inner join dba_objects t3

on t1.index_name = t3.object_name

where t1.owner='SYSDBA';

可以看到NONUNIQUE。

drop table USERS;

SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER',1);

create table USERS (

id number(12)  CLUSTER UNIQUE KEY,

name varchar2(20) not null,

age int not null);

4.创建聚集索引的约束条件

1. 每张表中只允许有一个聚集索引，如果之前已经指定过 CLUSTER INDEX 或者指定了 CLUSTER PK，则用户新建立 CLUSTER INDEX 时系统会自动删除原先的聚集索引。但如果新建聚集索引时指定的创建方式（列，顺序）和之前的聚集索引一样，则会报错；

2. 指定 CLUSTER INDEX 操作需要重建表上的所有索引，包括 PK 索引；

3. 删除聚集索引时，缺省以 ROWID 排序，自动重建所有索引；

4. 若聚集索引是默认的 ROWID 索引，不允许删除；

5. 聚集索引不能应用到函数索引中；

6. 不能在列存储表上新建/删除聚集索引；

7. 建聚集索引语句不能含有 partition_clause 子句；

8. 在临时表上增删索引会使当前会话上临时 b 树数据丢失。

sinat_32856657
关注
达梦数据库对象管理（三）：索引
Sebastien23的博客
09-17 501
而不能在系统表、视图、临时表、列存表、外部表上定义。同一个列只能创建一个全文索引。在创建全文索引的时候，用户可以为。
达梦数据库索引实践
jiaworld的博客
12-11 995
达梦数据库索引实践 达梦数据库支持二级索引，聚集索引，唯一索引，函数索引，位图索引，分区索引等。 默认的表是索引组织表，利用rowid创建一个默认的索引，所以我们创建的索引，称为二级索引。建索引的目的是加快表的查询，对数据库做DML操作的时候，数据库会自动维护索引。索引是一棵倒置的树，使用索引，就是对这个索引树进行遍历。 建立索引的规则：经常查询的列、连接条件列、谓词...
关于达梦的聚集索引
tqtycefn的博客
08-14 890
达梦数据库默认创建的表都是索引组织表，所以如果显示的创建聚集索引，系统会自动以ROWID排序，创建聚集索引。因为聚集索引叶子节点包含所有表记录，因此堆表+聚集索引 在达梦数据库中创建一个表的空间代价是双倍的。正因为有了聚集索引，所以达梦的执行计划中没有全表扫描，而是聚集索引的全索引扫描。 新建聚集索引会重建这个表以及其所有索引，包括二级索引、函数索引，是一个代价非常大的操作。因此，最好在建表时就确定聚集索引键，或在表中数据比较少时新建聚集索引，而尽量不要对数据量非常大的表建立聚集索引。 创建聚集索引
达梦数据库-索引的作用及应用
小杨童鞋学习日记
03-20 2730
达梦数据库-索引的作用及应用
达梦索引和explain部分
最新发布
weixin_46080292的博客
05-23 1455
索引是为了快速检索和定位数据行而创建的一种数据结构。索引在逻辑上和物理上都与相关的表的数据无关，作为无关的结构，索引需要存储空间。创建或删除一个索引，不会影响基本的表、数据库应用或其他索引。当插入、更改和删除相关的表的行时，DM8 会自动管理索引。如果删除索引，所有的应用仍继续工作，但访问以前被索引了的数据时速度可能会变慢。
达梦索引管理
qq_38113458的博客
06-03 320
索引
达梦(DM)数据库表索引
csdn565973850的博客
04-23 3176
讲述达梦数据库创建表索引及索引查询相关操作
达梦数据库启用约束重建索引.sql.sql
04-19
达梦数据库
达梦数据库-索引管理
weixin_41391398的博客
06-10 2851
索引定义语句 为了提高系统的查询效率，DM 系统提供了索引。但也需要注意，索引会降低那些影响 索引列值的命令的执行效率，如INSERT、UPDATE、DELETE的性能，因为DM不但要维护 基表数据还要维护索引数据。 语法格式 CREATE [OR REPLACE] [CLUSTER|NOT PARTIAL][UNIQUE | BITMAP| SPATIAL] INDEX < 索引名> ON [<模式名>.]<表名>(<索引列定义>{,<索引列定义&gt
达梦数据库基础---索引
qq_37004878的博客
11-25 2069
索引是与表相关的可选的结构（聚簇索引除外），它能使对应于表的 SQL 语句执行得更快，因为有索引比没有索引能更快地定位信息。DM7 索引能提供访问表的数据的更快路径，可以不用重写任何查询而使用索引，其结果与不使用索引是一样的，但速度更快。 DM7 提供了几种最常见类型的索引，对不同场景有不同的功能，它们是： 聚集索引：每一个普通表有且只有一个聚集索引； 唯一索引：索引数据根据索引键唯一； 函数索引：包含函数/表达式的预先计算的值； 位图索引：对低基数的列创建位图索引； 位图连接索引：针对两个或者多个表连接
索引：“其实我是一种数据结构”
weixin_44988779的博客
09-07 1024
“人家真的 不是目录” 索引的自白引言索引概述索引的创建索引的删除索引的查询explain查询有无索引show keys查询何种索引索引的原理聚簇索引辅助索引联合索引索引存在的问题 引言 大家好，我是索引，在MySQL中你可以使用CREATE INDEX indexName ON table_name (column_name)或alter table table_name add index(column_name)来创造我！在这里我主要澄清一下，我不是目录，你可以认为我是一种排序的数据结构，实在不行你就
数据库－聚簇索引和非聚簇索引
Criss@陈磊
05-25 1816
聚簇索引和非聚簇索引 聚簇是个什么东西？ 聚簇是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中；聚簇还表示把相关的数据存储在同一个块上。利用聚簇，一个块可能包含多个表的数据。概念上就是如果两个或多个表经常做链接操作，那么可以把需要的数据预先存储在一起。聚簇还可以用于单个表，可以按某个列将数据分组存储。更加简单的说，比如说，EMP表和DEPT表，这两个表存储在不同的s
数据库索引分类 聚集索引和非聚集索引
qq_31617121的博客
05-20 1152
聚集索引：聚集索引的逻辑顺序决定了表数据行的物理存储顺序。也就是数据库中相邻的记录在物理磁盘上的地址也是相邻的。因为真实的物理存储顺序只能有一种，所以一张表只能有一个聚集索引。 主键不一定是聚集索引，可以把其他唯一行作为聚集索引的。主键索引可以直接存储指向数据行的指针。...
创建集群和索引
qq_41100433的博客
06-06 479
1.集群健康度查询为了检查集群健康状况，我们将使用_cat API。您可以在Kibana的控制台中运行命令GET /_cat/health?v，结果如图其中关于kibana的安装，首先进入elastic官网下载https://www.elastic.co/products/kibana访问端口是localhost:5601...
达梦sql执行计划、HINT、索引简单应用
BShu666的博客
07-12 4587
因为不需要用户创建，因此称为虚索引。6）Hint中指定具体对象时(比如指定表名或者索引名)，不能带上该对象所在SCHEMA的名称，即使该SQL文本中已经有对应的SCHEMA名称。（GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS）
达梦删除聚集主键及聚集主键索引
Mrkill123的博客
04-29 9596
现状描述 直接删除聚集主键，则会报错“试图删除聚集主键”。聚集主键索引无法直接删除，会提示“没有删除表[xx]上索引的权限”。 解决办法 两种办法： 第一种：在表上新建一个聚集索引，那么聚集主键索引会变为非聚集，这样可以直接删除主键和索引； 第二种：重新建表，建表时显示指定主键为非聚集；或者在建表之前修改ini参数PK_WITH_CLUSTER为0，这样默认创建主键时为非聚集型； 具体操作如下： 第一种方法： CREATE TABLE tab1 (A INT PRIMARY KEY,B VARCHAR,C
sql 创建聚集索引和非聚集索引
不以人人的意志为转义乃天道
08-30 2026
--创建聚集索引 create CLUSTERED INDEX 索引名称 ON 表名(字段名) --创建非聚集索引 create NONCLUSTERED INDEX 索引名称 ON 表名(字段名) 每个InnoDB表有一个特殊的指数称为聚集索引所在的行的数据存储。通常，聚集索引是主键的同义词。从查询，插入性能最好，和其他的数据库操作，...
ORACLE创建cluster表
孔老先生的博客
07-29 1832
ORACLE创建cluster表，说是话我觉得这个没什么用，很少用到吧。 创建cluster表： 1、创建一个集群 create cluster my_cls (deptno number(2)); 这个时候数据库做了什么？定义了一个对象，名字是my_cls,cluster key是deptno create index my_cls_idx on cluster my_
达梦数据库如何将聚集索引改为非聚集索引
02-23
达梦数据库是一种关系型数据库管理系统，它支持将聚集索引改为非聚集索引。在达梦数据库中，可以通过以下步骤将聚集索引改为非聚集索引： 1. 首先，使用ALTER TABLE语句修改表的定义，将原来的聚集索引删除。例如，可以使用以下语句删除名为index_name的聚集索引： ``` ALTER TABLE table_name DROP INDEX index_name; ``` 2. 接下来，使用CREATE INDEX语句创建一个新的非聚集索引。例如，可以使用以下语句创建一个名为index_name的非聚集索引： ``` CREATE INDEX index_name ON table_name (column_name); ``` 在上述步骤中，table_name是要修改的表的名称，index_name是要删除或创建的索引的名称，column_name是要作为索引的列名。 需要注意的是，修改索引类型可能会对数据库性能产生影响，因此在进行此类操作之前，建议先评估和测试性能影响，并确保在适当的时机进行操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值