PostgreSQL中是否区分聚簇索引与非聚簇索引的问题?
答:PostgreSQL中区分聚簇索引与非聚簇索引。
示例如下:
创建聚族索引:
CREATE INDEX test_ind
ON yy (name ASC NULLS LAST);
ALTER TABLE yy
CLUSTER ON test_ind;
创建非聚族索引:
CREATE INDEX test_uncluster_index
ON yy (name ASC NULLS LAST);
临时表被删除,临时表的索引是否也被删除?
答:临时表被删除,临时表的索引也同时被删除。
示例如下:
创建两个临时表:
test1=# create temporary table tmp_t1(id int primary key,note text);
CREATE TABLE
test1=# dt
List of relations
Schema | Name | Type | Owner
----------------------+---------
pg_temp_2 | tmp_t1 | table | postgres
public | student | table | postgres
public | testdb | table | postgres
(3 rows)
test1=# create temporary table tmp_t2(id int primary key,name char(10),note
text);
CREATE TABLE
test1=# dt
List of relations
Schema | Name | Type | Owner
----------------------+---------
pg_temp_2 | tmp_t1 | table | postgres
pg_temp_2 | tmp_t2 | table | postgres
public | student | table | postgres
public | testdb | table | postgres
(4 rows)
给两个临时表分别创建索引并查看索引:
test1=# create index idx_tmp_t1_id on tmp_t1(id);
CREATE INDEX
test1=# create index idx_tmp_t2_id on tmp_t2(id);
CREATE INDEX
test1=# di
List of relations
Schema | Name | Type | Owner | Table
-------------------------------------------
pg_temp_2 | idx_tmp_t1_id | index | postgres | tmp_t1
pg_temp_2 | idx_tmp_t2_id | index | postgres | tmp_t2
pg_temp_2 | tmp_t1_pkey | index | postgres | tmp_t1
pg_temp_2 | tmp_t2_pkey | index | postgres | tmp_t2
(4 rows)
删除临时表1:
test1=# drop table tmp_t1;
DROP TABLE
test1=# dt
List of relations
Schema | Name | Type | Owner
----------------------+---------
pg_temp_2 | tmp_t2 | table | postgres
public | student | table | postgres
public | testdb | table | postgres
(3 rows)
查看索引,临时表1的索引也被删除:
test1=# di
List of relations
Schema | Name | Type | Owner | Table
-------------------------------------------
pg_temp_2 | idx_tmp_t2_id | index | postgres | tmp_t2
pg_temp_2 | tmp_t2_pkey | index | postgres | tmp_t2
(2 rows)