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

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

omm=# 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
omm=# create index idx_test_testnum on test(testnum);
CREATE INDEX

2.通过hint使用索引

omm=# EXPLAIN SELECT /*+ indexscan(customer customer_idx ) */ 
omm-#  * FROM customer WHERE ca_address_sk<100;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 [Bypass]
 Index Scan using customer_idx on customer  (cost=0.00..8.27 rows=1 width=788)
   Index Cond: (ca_address_sk < 100)
(3 rows)

3.rename索引

omm=# ALTER INDEX idx_test_testnum RENAME TO idx_test_testnum_new;
omm=# ALTER INDEX

4.重建索引

omm=# ALTER INDEX idx_test_testnum RENAME TO idx_test_testnum_new;
omm=# ALTER INDEX
omm=# ALTER INDEX  idx_test_testnum_new REBUILD;
omm=# REINDEX
omm=# 
REINDEX INDEX  idx_test_testnum_new;
REINDEX
omm=# reindex table test;
REINDEX

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

omm=# --创建表空间myindex_ts：
omm=#  CREATE TABLESPACE myindex_ts RELATIVE LOCATION 'tablespace/myindex_ts1';
omm=# 
omm=# --将索引idx_test_testnum_new移动到表空间myindex_ts：
omm=# CREATE TABLESPACE
  ALTER INDEX idx_test_testnum_new SET TABLESPACE myindex_ts;
ALTER INDEX

6.删除索引

omm=# drop index idx_test_testnum_new;
DROP INDEX