openGauss每日一练第17天 | 索引管理

打卡第十七天，索引管理

创建索引

create index idx_test_testnum on test(testnum);
复制

通过hint使用索引

EXPLAIN SELECT /*+ indexscan(customer customer_idx ) */ 
 * FROM customer WHERE ca_address_sk<100;
复制

rename索引

ALTER INDEX idx_test_testnum RENAME TO idx_test_testnum_new;
复制

重建索引 (语法比oracle 多)

--重建一个单独索引
ALTER INDEX  idx_test_testnum_new REBUILD;

REINDEX INDEX  idx_test_testnum_new;
--重建所有索引
reindex table test;
复制

移动索引到其他表空间

  ALTER INDEX idx_test_testnum_new SET TABLESPACE myindex_ts;
复制

删除索引

drop index idx_test_testnum_new;
复制

课程作业

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

omm=# create table t (id serial,col char(20));
NOTICE:  CREATE TABLE will create implicit sequence "t_id_seq" for serial column "t.id"
CREATE TABLE
omm=# create index idx_col on t(col);
CREATE INDEX
omm=# \di idx_col 
                 List of relations
 Schema |  Name   | Type  | Owner | Table | Storage 
--------+---------+-------+-------+-------+---------
 public | idx_col | index | omm   | t     | 
(1 row)
复制

2.通过hint使用索引

omm=# explain select /*+ indexscan(t idx_col) */ * from t where col='1';
omm=#                             QUERY PLAN                             
-------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_col on t  (cost=0.00..16.30 rows=3 width=88)
   Index Cond: (col = '1'::bpchar)
(3 rows)
复制

3.rename索引

omm=# alter index idx_col rename to idx_cc;
ALTER INDEX
复制

4.重建索引

omm=# alter index idx_cc rebuild;
REINDEX
omm=# rebuil index idx_cc index idx_cc index idx_cc;^C
omm=# reindex index idx_cc ;
REINDEX
omm=# reindex table t;
REINDEX
omm=# 
复制

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

omm=# select * from pg_indexes where indexname='idx_cc';
 schemaname | tablename | indexname | tablespace |                             indexdef                           
  
------------+-----------+-----------+------------+----------------------------------------------------------------
--
 public     | t         | idx_cc    |            | CREATE INDEX idx_cc ON t USING btree (col) TABLESPACE pg_defaul
t
(1 row)

omm=# alter index idx_cc set tablespace tbs_t;
ALTER INDEX
omm=# select * from pg_indexes where indexname='idx_cc';
 schemaname | tablename | indexname | tablespace |                          indexdef                           
------------+-----------+-----------+------------+-------------------------------------------------------------
 public     | t         | idx_cc    | tbs_t      | CREATE INDEX idx_cc ON t USING btree (col) TABLESPACE tbs_t
(1 row)
复制

6.删除索引

omm=# drop index idx_cc ;
DROP INDEX
复制