CNDBA社区
之前我们整理了DM DCP 的相关内容：
达梦 DM8 DCP 备考笔记(1) — 理论考试 说明
https://www.cndba.cn/dave/article/108708
达梦 DM8 DCP 备考笔记(2) — 分区表操作
https://www.cndba.cn/dave/article/108710
达梦 DM8 DCP 备考笔记(3) — 外部表操作
https://www.cndba.cn/dave/article/108711
达梦 DM8 DCP 备考笔记(4) — 同义词操作
https://www.cndba.cn/dave/article/108712
达梦 DM8 DCP 备考笔记(5) — 物化视图操作
https://www.cndba.cn/dave/article/108713
达梦 DM8 DCP 备考笔记(6) — 序列操作
https://www.cndba.cn/dave/article/108714
达梦 DM8 DCP 备考笔记(7) — 审计操作
https://www.cndba.cn/dave/article/108715
达梦 DM8 DCP 备考笔记(8) — 快速加载(Fast Loader)操作
https://www.cndba.cn/dave/article/108716
本篇我们来看下达梦数据库中的索引操作。 相关理论说明参考《DM 系统管理员手册》的 <第10章：管理索引> 和 <第18章：全文检索>。
1 创建索引
唯一索引
SQL> create table cndba.anqing(id int ,name varchar(20));
executed successfully
used time: 7.144(ms). Execute id is 58300.
SQL> create unique index cndba.idx_anqing on cndba.anqing(id) tablespace cndba1;
executed successfully
used time: 12.813(ms). Execute id is 58301.
SQL> select owner,index_name from dba_indexes where owner='CNDBA' and table_name='ANQING';
LINEID OWNER INDEX_NAME
---------- ----- -------------
1 CNDBA IDX_ANQING
2 CNDBA INDEX33555526
used time: 72.066(ms). Execute id is 58302.
SQL>
函数索引
SQL> create table cndba.emp as select * from dmhr.employee;
executed successfully
used time: 6.521(ms). Execute id is 58303.
SQL> create index cndba.ind_emp on cndba.emp(upper(email)) tablespace cndba1;
executed successfully
used time: 10.584(ms). Execute id is 58304.
SQL> explain select employee_name, email from cndba.emp where upper(email)=upper('maxueming@dameng.com');
1 #NSET2: [1, 21, 104]
2 #PRJT2: [1, 21, 104]; exp_num(3), is_atom(FALSE)
3 #BLKUP2: [1, 21, 104]; IND_EMP(EMP)
4 #SSEK2: [1, 21, 104]; scan_type(ASC), IND_EMP(EMP), scan_range[exp11,exp11]
used time: 0.734(ms). Execute id is 0.
SQL>
复合索引
SQL>create index cndba.emp_ind2 on cndba.emp(employee_id,employee_name);
executed successfully
used time: 46.925(ms). Execute id is 58305.
SQL>
位图索引
SQL> create table cndba.huaining(id int,sex char(1), url varchar(20));
executed successfully
used time: 15.813(ms). Execute id is 58306.
SQL> insert into cndba.huaining values(1,0,'https://www.cndba.cn/');
affect rows 1
used time: 1.132(ms). Execute id is 58307.
SQL> commit;
executed successfully
used time: 8.277(ms). Execute id is 58308.
SQL> dbms_stats.gather_table_stats('CNDBA','HUAINING',NULL,100,FALSE,'FOR ALL COLUMNS SIZE AUTO');
DMSQL executed successfully
used time: 31.649(ms). Execute id is 58310.
SQL> dbms_stats.column_stats_show(OWNNAME='CNDBA',TABNAME='HUAINING',COLNAME='SEX');
LINEID NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
---------- -------------------- --------- ---------- -------------------- ----------- -------------------- ---------
1 1 0 0 0 1 1 FREQUENCY
used time: 1.119(ms). Execute id is 58311.
SQL>
2 索引维护
重建索引
SQL> alter index cndba.idx_anqing rebuild;
executed successfully
used time: 10.656(ms). Execute id is 58312.
SQL> alter index cndba.idx_anqing rebuild online;
executed successfully
used time: 14.998(ms). Execute id is 58313.
SQL>
查看索引的相关信息
SQL> select owner,index_name from dba_indexes where owner='CNDBA' and table_name='ANQING';
SQL> select owner,index_name from user_indexes where owner='CNDBA' and table_name='ANQING';
删除索引：
SQL> drop index cndba.idx_anqing;
executed successfully
used time: 19.217(ms). Execute id is 58314.
SQL>
3 分区索引
创建局部索引
SQL> create index cndba.idx_t1 on cndba.t1(id) storage(initial 1,next 1,minextents 1,on cndba1);
executed successfully
used time: 44.862(ms). Execute id is 58315.
SQL>
SQL>
SQL> drop index idx_t1;
executed successfully
used time: 48.410(ms). Execute id is 58316.
创建全局索引
SQL> create index cndba.idx_t2 on cndba.t1(id) global storage(initial 1,next 1,minextents 1,on cndba1);
executed successfully
used time: 42.645(ms). Execute id is 58317.
SQL>
4 全文索引
DM全文索引必须在基表上定义，而不能在系统表、视图、临时表、列存储表、 外部表上定义。同一列只能创建一个全文索引。DM 定义全文索引时，不需要在表上先建立主关键字索引。
创建全文索引：
[dave@www.cndba.cn data]$ disql dmhr/dameng123
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 1.788(ms)
disql V8
SQL> create context index DMHR.idx_street on DMHR.LOCATION(STREET_ADDRESS) lexer default_lexer;
executed successfully
used time: 50.196(ms). Execute id is 58400.
SQL>
查看全文索引
SQL> select * from ctisys.syscontextindexes;
LINEID NAME ID TABLEID COLID UPD_TIMESTAMP TIID TDID TPID TNID
---------- ---------- ----------- ----------- ----------- -------------------------- ----------- ----------- ----------- -----------
TRID WSEG_TYPE RESVD
----------- ----------- ----------
1 IDX_STREET 33555544 1107 1 2022-10-31 06:05:21.208113 1113 1115 1114 1116
NULL 0 NULL
used time: 3.728(ms). Execute id is 58405.
SQL>
完全更新全文索引
SQL> alter context index DMHR.idx_street on dmhr.location rebuild;
executed successfully
used time: 157.615(ms). Execute id is 58403.
增量更新全文索引
SQL> alter context index DMHR.idx_street on dmhr.location increment;
executed successfully
used time: 133.010(ms). Execute id is 58404.
SQL>
SQL> select * from DMHR.LOCATION where contains(STREET_ADDRESS,'东路' or '南路' or '7');
LINEID LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY_ID
---------- ----------- ---------------------------- ----------- -------
1 2 桥西区槐安东路28号 050000 SJZ
2 5 天河区体育东路122号 510000 GZ
3 10 雁塔区雁塔南路10号 710000 XA
4 4 江宁开发区迎翠路7号 210000 NJ
used time: 4.136(ms). Execute id is 58417.
SQL>
SQL> select * from DMHR.LOCATION where STREET_ADDRESS like '%东路%' or STREET_ADDRESS like '%南路%' or STREET_ADDRESS like '%7%';
LINEID LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY_ID
---------- ----------- ---------------------------- ----------- -------
1 2 桥西区槐安东路28号 050000 SJZ
2 4 江宁开发区迎翠路7号 210000 NJ
3 5 天河区体育东路122号 510000 GZ
4 9 沈河区沈阳路171号 110000 SY
5 10 雁塔区雁塔南路10号 710000 XA
used time: 2.150(ms). Execute id is 58410.
SQL>
删除全文索引
SQL> drop context index "IDX_STREET" on "DMHR"."LOCATION";
executed successfully
used time: 80.871(ms). Execute id is 58406.
SQL>
版权声明：本文为博主原创文章，未经博主允许不得转载。
