学习目标

掌握openGauss DBMS索引的管理：创建索引、删除索引、查询索引的信息、修改索引的信息。

课程作业

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

--在testnum列上创建索引
newdb1=# create table test(id serial primary key,testnum serial);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE will create implicit sequence "test_testnum_seq" for serial column "test.testnum"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
newdb1=# create index idx_test_testnum on test(testnum);
CREATE INDEX
newdb1=# \di
                      List of relations
 Schema |       Name       | Type  | Owner | Table | Storage
--------+------------------+-------+-------+-------+---------
 testsm | idx_test_testnum | index | omm   | test  |
 testsm | test_pkey        | index | omm   | test  |
(2 rows)
复制

2.通过hint使用索引

--插入数据


newdb1=# insert into test values (1,101);
INSERT 0 1
newdb1=# insert into test values (2,201);
INSERT 0 1
newdb1=# insert into test values (3,301);
INSERT 0 1
newdb1=# insert into test values (4,401);


--hint 索引
newdb1=# explain select /*+indexscan(test idx_test_testnum) */ * from test where testnum > 1;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_test_testnum on test  (cost=0.00..56.78 rows=716 width=8)
   Index Cond: (testnum > 1)
(3 rows)

复制

3.rename索引

                                             ^
newdb1=# alter index idx_test_testnum  rename to idx_test_testnum2;
ALTER INDEX
newdb1=# \di
                        List of relations
 Schema |       Name        | Type  | Owner |  Table   | Storage
--------+-------------------+-------+-------+----------+---------
 testsm | customer_idx      | index | omm   | customer |
 testsm | idx_test_testnum2 | index | omm   | test     |
(2 rows)

newdb1=#
复制

4.重建索引

newdb1=# reindex index idx_test_testnum2;
REINDEX
复制

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

--从默认表空间移出到其他表空间
newdb1=# select * from pg_indexes where indexname = 'idx_test_testnum2';
 schemaname | tablename |     indexname     | tablespace |                                    indexdef
------------+-----------+-------------------+------------+---------------------------------------------------------------------------------
 testsm     | test      | idx_test_testnum2 |            | CREATE INDEX idx_test_testnum2 ON test USING btree (testnum) TABLESPACE newtbs1
(1 row)

newdb1=# \db
             List of tablespaces
    Name    | Owner |        Location
------------+-------+------------------------
 enmtbs     | omm   | tablespace/enmtbs_ts1
 music_tbs  | omm   | tablespace/test_ts1
 newtbs1    | omm   | tablespace/newtbs1_ts1
 pg_default | omm   |
 pg_global  | omm   |
(5 rows)

newdb1=#   ALTER INDEX idx_test_testnum2  SET TABLESPACE enmtbs;
ALTER INDEX
newdb1=#  select * from pg_indexes where indexname = 'idx_test_testnum2';
 schemaname | tablename |     indexname     | tablespace |                                    indexdef
------------+-----------+-------------------+------------+--------------------------------------------------------------------------------
 testsm     | test      | idx_test_testnum2 | enmtbs     | CREATE INDEX idx_test_testnum2 ON test USING btree (testnum) TABLESPACE enmtbs
(1 row)
复制

6.删除索引

newdb1=# drop index idx_test_testnum2
newdb1-# ;
DROP INDEX
复制