openGauss每日一练第17天 | 学习心得体会

今日学习了openGauss DBMS索引的管理：创建索引、删除索引、查询索引的信息、修改索引的信息。

#登录数据库su - omm
gsql -r

1、创建表，在表中创建索引

#创建表
create table test(id serial primary key,testnum int not null,name char(100),score bigint);
#创建索引
create index idx_test_testnum on test(testnum);

2、通过hint使用索引

#创建复杂表
CREATE TABLE customer_t
(
ca_address_sk integer NOT NULL ,
ca_address_id character(16),
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
);
#插入记录
insert into customer_t values
(1, 'AAAAAAAABAAAAAAA', '18', 'Jackson', 'Parkway', 'Suite 280', 'Fairfield', 'Maricopa County', 'AZ', '86192' ,'United States', -7.00, 'condo'),
(2, 'AAAAAAAACAAAAAAA', '362', 'Washington 6th', 'RD', 'Suite 80', 'Fairview', 'Taos County', 'NM', '85709', 'United States', -7.00, 'condo'),
(3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington', 'Circle', 'Suite Q', 'Pleasant Valley', 'York County', 'PA', '12477', 'United States', -5.00, 'single family');

#创建索引
create index customer_t_idx on customer_t(ca_address_sk);

#使用hint走新建索引查询
explain select /*+ indexscan(customer_t customer_t_idx) */ * from 
customer_t where ca_address_sk<100;

3、rename 索引

alter index customer_t_idx rename to customer_t_idx_new;

4、重建索引

#重建单独索引
alter index customer_t_idx_new rebuild;

reindex index customer_t_idx_new;
#重建表中所有索引
reindex table customer_t;

5、移动索引到其他表空间

#创建表空间
create tablespace myindex_tbs relative location 'tablespace/myindex_tbs1';

#移动索引到新表空间
alter index customer_t_idx_new set tablespace  myindex_tbs;

6、删除索引

drop index customer_t_idx_new;