学习目标
学习openGauss普通表索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息
1.创建表products, 分别为表创建一个unique索引1，指定b-tree索引2和表达式索引3
2.设置索引1不可用，修改索引2的表空间，重命名索引3
3.重建索引2和products的所有索引
4.使用\d+和系统视图pg_indexes查看索引信息
5.删除索引、表和表空间
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# CREATE TABLE products
omm-# (
omm(# SM_SHIP_MODE_SK INTEGER NOT NULL,
omm(# SM_SHIP_MODE_ID CHAR(16) NOT NULL,
omm(# SM_TYPE CHAR(30),
omm(# SM_CODE CHAR(10),
omm(# SM_CARRIER CHAR(20),
omm(# omm(# );SM_CONTRACT CHAR(20)
CREATE TABLE
omm=# CREATE UNIQUE INDEX products_t1_index1 ON products(SM_SHIP_MODE_SK);
CREATE INDEX
omm=#
omm=# CREATE INDEX products_t1_index4 ON products USING btree(SM_SHIP_MODE_SK);
CREATE INDEX
omm=#
omm=# CREATE INDEX products_t1_index2 ON products(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX
omm=#
omm=# ALTER INDEX products_t1_index1 UNUSABLE;
ALTER INDEX
omm=#
omm=# ALTER INDEX products_t1_index4 REBUILD;
REINDEX
omm=# reindex table products;
REINDEX
omm=#
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+--------------+-------+-------+---------+----------------------------------+-------------
public | ds_t1 | table | omm | 0 bytes | {orientation=row,compression=no} |
public | products | table | omm | 0 bytes | {orientation=row,compression=no} |
public | update_table | table | omm | 0 bytes | {orientation=row,compression=no} |
(3 rows)
omm=#
omm=# select * from pg_indexes;
schemaname | tablename | indexname | tablesp
ace | indexdef
----------------------------------------------------------------------
public | products | products_t1_index1 |
| CREATE UNIQUE INDEX products_t1_index1 ON products USING btree (sm_ship_mode_sk) TABLESPACE pg_
default
------------+-------------------------------+-----------------------------------------------+--------
----+------------------------------------------------------------------------------------------------
public | products | products_t1_index4 |
| CREATE INDEX products_t1_index4 ON products USING btree (sm_ship_mode_sk) TABLESPACE pg_default
public | products | products_t1_index2 |
| CREATE INDEX products_t1_index2 ON products USING btree (substr((sm_code)::text, 1, 4)) TABLESP
ACE pg_default
ql) TABLESPACE pg_default
pg_catalog | pg_type | pg_type_typname_nsp_index |
pg_catalog | statement_history | statement_history_time_idx |
| CREATE INDEX statement_history_time_idx ON statement_history USING btree (start_time, is_slow_s
--More-- | CREATE UNIQUE INDEX pg_type_typname_nsp_index ON pg_type USING btree (typname, typnamespace) TA
BLESPACE pg_default
| CREATE UNIQUE INDEX pg_ts_dict_dictname_index ON pg_ts_dict USING btree (dictname, dictnamespac
e) TABLESPACE pg_default
pg_catalog | pg_ts_dict | pg_ts_dict_dictname_index |
Cancel request sent
omm=#
omm=# drop table products;
DROP TABLE
omm=#
