sql 新建立的表索引不能创建_【干货分享】达梦数据库索引建立使用小技巧

点击上方蓝字关注我们吧!

5aa46d6577e6c5a12ce6a05bfb3a4f64.png 195dc42cb72e32cb4821eaea72d30d52.png

达梦数据库支持聚集索引,复合索引,函数索引,唯一索引,位图索引等等。下面我们就一起来创建达梦数据库在各种场景中的索引。我们的测试环境是:

 操作系统:中标麒麟6 64位。

 达梦数据库:DM8.1。

7151f49800215c322e7b1850d2798a7a.png

8801139bde2d7bb0a77bff6eff41bfeb.png

7f4d59b123e8b1ac02f45f0765be7c7a.png

一.建立索引的准则

7f4d59b123e8b1ac02f45f0765be7c7a.png

1.1在表中插入数据后创建索引

一般情况下,在插入或装载了数据后,为表创建索引会更加有效率。如果在装载数据之前创建了一个或多个索引,那么在插入每行时DM数据库都必须更改和维护每个索引,使得插入效率降低。

1.2怎样创建正确的索引

(1)如果需要经常地检索大表中的少量的行,就为查询键创建索引;

(2)为了改善多个表的连接的性能,可为连接列创建索引;

(3)主键和唯一键自动具有索引,在外键上很多情况下也创建索引;

(4)小表不需要索引;

(5)列中的值相对比较唯一 ;

(6)取值范围大,适合建立索引; 

(7)CLOB和TEXT只能建立全文索引、BLOB不能建立任何索引。

1.3为性能而安排索引列

在 CREATE INDEX 语句中列的排序会影响查询的性能。通常,将最常用的列放在最前面。如果查询中有多个字段组合定位,则不应为每个字段单独创建索引,而应该创建一个组合索引。当两个或多个字段都是等值查询时,组合索引中各个列的前后关系是无关紧要的。但是如果是非等值查询时,要想有效利用组合索引,则应该按等值字段在前,非等值字段在 后的原则创建组合索引,查询时只能利用一个非等值的字段。

1.4限制每个表的索引的数量

一个表可以有任意数量的索引。但是,索引越多,修改表数据的开销就越大。当插入或删除行时,表上的所有索引也要被更改;更改一个列时,包含该列的所有索引也要被更改。因此,在从表中检索数据的速度和更新表的速度之间有一个折衷。例如,如果一个表主要仅用于读,则索引多就有好处;如果一个表经常被更新,则索引不宜多建。

7f4d59b123e8b1ac02f45f0765be7c7a.png

二.创建索引

7f4d59b123e8b1ac02f45f0765be7c7a.png

2.1创建聚集索引

DM数据库中表(列存储表和堆表除外)都是使用 B+树索引结构管理的,每一个普通表都有且仅有一个聚集索引,数据都通过聚集索引键排序,根据聚集索引键可以快速查询任何记录。

当建表语句未指定聚集索引键时,DM数据库的默认聚集索引键是ROWID。若指定索引键,表中数据都会根据指定索引键排序。建表后,DM数据库也可以用创建新聚集索引的方式来重建表数据,并按新的聚集索引排序。例如,可以对employee表以employee_name列新建聚集索引。

先建立索引表空间ind_tbs:

Sql>create tablespaceind_tbsdatafile  '/dm8/data/DAMENG/ind_tbs.dbf' size 32 autoextend on next 10  maxsize 2000; de03b595aa968843b319bbf3d2a1e350.png Sql>create index ind_emp on dmhr.employee(employee_name) tablespaceind_tbs;

1cbfc971b015a413f8b54cbb4b6b2aca.png

2.2复合索引

Sql>create index ind_emp_dep on dmhr.employee(employee_id,department_id) tablespaceind_tbs;

c66a24d30cffec0310879dffe526945a.png

2.3函数索引

基于函数的索引促进了限定函数或表达式的返回值的查询,该函数或表达式的值被预先计算出来并存储在索引中。

Sql> create index ind_emp1 on dmhr.employee(abs(salary)) tablespaceind_tbs; 6f8214b114fc18f672251739bfbe2b47.png

2.4位图索引

位图索引主要针对含有大量相同值的列而创建。位图索引被广泛引用到数据仓库中。Sql>create bitmap index ind_t on t1(id);

aeebba3651d41f8a3580c662b421ce3c.png

2.5唯一索引

索引可以是唯一的或非唯一的。唯一索引可以保证表上不会有两行数据在键列上具有相同的值。

SQL> create unique index ind_t2 on t2(id);acd06719c6acd50057ad07c7467c38d2.png

7f4d59b123e8b1ac02f45f0765be7c7a.png

三.索引重建

7f4d59b123e8b1ac02f45f0765be7c7a.png 当一个表经过大量的增删改操作后,表的数据在物理文件中可能存在大量碎片,从而影响访问速度。另外,当删除表的大量数据后,若不再对表执行插入操作,索引所处的段可能占用了大量并不使用的簇,从而浪费了存储空间。可以使用重建索引来对索引的数据进行重组,使数据更加紧凑,并释放不需要的空间,从而提高访问效率和空间效率。 DM数据库提供的重建索引的系统函数为: SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int); SCHEAM_NAME为索引所在的模式名。 INDEX_ID为索引ID。 使用说明:  1.水平分区子表,临时表和系统表上建的索引不支持重建  2.虚索引和聚集索引不支持重建 Sql>select name,id,subtype$ from sysobjects where subtype$='INDEX' and name='IND_EMP_DEP';

d1d352f81d6b80270b789a79c7a71ccf.png

Sql>sp_rebuild_index('DMHR','335555517');

465c75f4beaf2e76edd3f0178dc5faac.png

SQL> alter index DMHR.IND_EMP_DEP rebuild;

bbd674d892b6ce57fe29ea60675d893e.png

在线重建索引 SQL> alter index DMHR.IND_EMP_DEP rebuild online;

8d12d148b4285aaa32ac82aba5afffe4.png

7f4d59b123e8b1ac02f45f0765be7c7a.png

四.删除索引

7f4d59b123e8b1ac02f45f0765be7c7a.png 用户可能出于以下某项原因需要删除一个索引: 1.不再需要该索引; 2.该索引没有为针对其相关的表所发布的查询提供所期望的性能改善。例如,表可能很小,或者尽管表中有许多行但只有很少的索引项; 3.应用没有用该索引来查询数据。要想删除索引,则该索引必须包含在用户的模式中或用户必须具有DROP ANY INDEX 数据库权限。索引删除之后,该索引的段的所有簇都返回给包含它的表空间,并可用于表空间中的其他对象。 Sql>DROP INDEX dmhr.IND_EMP_DEP;

78451b8f5106abd140cb15d9ca6a55bd.png

7f4d59b123e8b1ac02f45f0765be7c7a.png

五.查看索引信息

7f4d59b123e8b1ac02f45f0765be7c7a.png 创建索引后,可以通过INDEXDEF系统函数查看索引的定义。 INDEXDEF(INDEX_ID int, PREFLAG int); INDEX_ID为索引 ID。 PREFLAG表示返回信息中是否增加模式名前缀。 例如,需要查看索引 IND_EMP的定义,那么使用以下语句查看索引定义。 Sql>select name,id,subtype$ from sysobjects where subtype$='INDEX' and name='IND_EMP';

b4f8d99fce62011719033358092f2b70.png

Sql>SELECT INDEXDEF(33555516, 0); 或 SELECT INDEXDEF(33555516, 1);

dcd3857dea86c1f47dc6b8485355097c.png

好了,今天的索引使用小技巧就分享到这里了。

大家在长假期间可以去尝试一下!

3362875d8bfa1460b2d179f4414b10ae.png

往期精彩

1

【干货分享】达梦分区表的使用 

2

【干货分享】Linux环境下配置ODBC

3

【干货分享】数据库里表太多?快速获取某张表的表定义语句有妙招

9f8590b1e6fcce05d028b33ff84c0c3d.gif

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页