openGauss每日一练第 18 天 |学习笔记

原创 newdata 2022-12-11

450

今日目标：openGauss视图的管理.

视图管理包括：创建视图、删除视图、查询视图的信息、修改视图的信息。

视图

视图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。

语法格式

创建视图

CREATE [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, …] ) ]
AS query;

刪除视图

DROP VIEW view_name ;

查看视图

\dv[S+] [PATTERN] // list views

物化视图

物化视图是相对普通视图而言的。普通视图是虚拟表，而物化视图实际上就是存储SQL执行语句的结果，可以直接使用数据而不用重复执行查询语句，从而提升性能。

按照刷新方式物化视图分为两种：

全量物化视图：仅支持对已创建的物化视图进行全量更新，而不支持进行增量更新。创建全量物化视图语法和CREATE TABLE AS语法类似。
增量物化视图：可以对物化视图增量刷新，需要用户手动执行语句完成对物化视图在一段时间内的增量数据刷新。与全量创建物化视图的不同在于目前增量物化视图所支持场景较小。目前物化视图创建语句仅支持基表扫描语句或者UNION ALL语句。

普通视图在查询中是实时进行计算的。如果建立视图的基表数据很多，使用视图的时候，进行实时计算视图表示的结果集，将消耗很大的计算机资源，并且费时很长。
物化视图提前计算出视图的结果集，并将该结果集保存在数据库里。
如果更新了基表,物化视图将过期。也就是说，基表更新后，物化视图不能反映最新的数据情况。因此在基表发生变化的时候，需要对物化视图进行更新。

语法格式

创建物化视图

CREATE MATERIALIZED VIEW view_name AS query;

全量刷新物化视图

REFRESH MATERIALIZED VIEW [ view_name ];

删除物化视图

DROP MATERIALIZED VIEW [ view_name ];

PG_VIEWS

PG_VIEWS视图提供访问数据库中每个视图的有用信息。

名称	类型	引用	描述
schemaname	name	PG_NAMESPACE.nspname	包含视图的模式名。
viewname	name	PG_CLASS.relname	视图名。
viewowner	name	PG_AUTHID.Erolname	视图的所有者。
definition	text	-	视图的定义。

查看普通视图的信息

omm=# select * from pg_views where viewname = 'customer_detail_view_v1';
 schemaname |        viewname         | viewowner |                           definition                           
------------+-------------------------+-----------+----------------------------------------------------------------
 tpcds      | customer_detail_view_v1 | omm       | SELECT  * FROM customer WHERE (customer.c_customer_sk > 2222);
(1 row)

omm=#

GS_MATVIEWS

GS_MATVIEWS视图提供了关于数据库中每一个物化视图的信息。

名称	类型	引用	描述
schemaname	name	PG_NAMESPACE.nspname	物化视图的模式名。
matviewname	name	PG_CLASS.relname	物化视图名。
matviewowner	name	PG_AUTHID.Erolname	物化视图的所有者。
tablespace	name	PG_TABLESPACE.spcname	物化视图的表空间名（如果使用数据库默认表空间则为空）。
hasindexes	boolean	-	如果物化视图有（或者最近有过）任何索引，则此列为真。
definition	text	-	物化视图的定义（一个重构的SELECT查询）。

查看物化视图的信息


omm=# select * from gs_matview;
 matviewid | mapid | ivm | needrefresh | refreshtime 
-----------+-------+-----+-------------+-------------
     16412 |     0 | f   |             | 
(1 row)

omm=# \dv+
                                 List of relations
 Schema |          Name           | Type | Owner |  Size   | Storage | Description 
--------+-------------------------+------+-------+---------+---------+-------------
 tpcds  | customer_detail_view_v1 | view | omm   | 0 bytes |         | 
 tpcds  | part_view               | view | omm   | 0 bytes |         | 
(2 rows)

omm=#

序列号生成函数

generate_series(start, stop)

描述：生成一个数值序列，从start到stop，步长为1。

参数类型：int、bigint、numeric

课后作业

1. 创建表，创建普通视图

CREATE [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, …] ) ]
AS query;

omm=# create schema tpcds;
omm=# CREATE SCHEMA

omm=# create table tpcds.customer(
omm(# c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8)
omm(# );
CREATE TABLE
omm=# insert into tpcds.customer values
omm-# (1111,1,'Joes','hunter'),
omm-# (2222,2,'lily','carter'),
omm-# (3333,3,'ming','fuzhu'),
omm-# (4444,4,'uzi','adc');
INSERT 0 4
omm=# 
omm=# select * from tpcds.customer ;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          1111 | 1             | Joes         | hunter  
          2222 | 2             | lily         | carter  
          3333 | 3             | ming         | fuzhu   
          4444 | 4             | uzi          | adc     
(4 rows)
-- 创建视图
omm=# create view tpcds.customer_detail_view_v1 as
omm-# select * from tpcds.customer 
omm-# where c_customer_sk >2222;
omm=# CREATE VIEW
-- 查询视图内存    
omm=# 
select * from tpcds.customer_detail_view_v1 ;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3333 | 3             | ming         | fuzhu   
          4444 | 4             | uzi          | adc     
(2 rows)

omm=#

2. 使用视图创建新的视图

-- 基于视图customer_detail_view_v1创建新的视图part_view
omm=# create view tpcds.part_view as select * from tpcds.customer_detail_view_v1 where c_customer_id = 3;
CREATE VIEW
omm=# select * from tpcds.part_view ;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3333 | 3             | ming         | fuzhu   
(1 row)
-- 查看视图
omm=# \dv
                     List of relations
 Schema |          Name           | Type | Owner | Storage 
--------+-------------------------+------+-------+---------
 tpcds  | customer_detail_view_v1 | view | omm   | 
 tpcds  | part_view               | view | omm   | 
(2 rows)
-- 查看视图详细信息
omm=# \dv+ 
                                 List of relations
 Schema |          Name           | Type | Owner |  Size   | Storage | Description 
--------+-------------------------+------+-------+---------+---------+-------------
 tpcds  | customer_detail_view_v1 | view | omm   | 0 bytes |         | 
 tpcds  | part_view               | view | omm   | 0 bytes |         | 
(2 rows)

3. 创建物化视图

-- 创建基表
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=# \dt
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 tpcds  | customer | table | omm   | {orientation=row,compression=no}
 tpcds  | test     | table | omm   | {orientation=row,compression=no}
(2 rows)

omm=# insert into test (testnum) values(generate_series(1,100000));
INSERT 0 100000
omm=# select count(*) from test;
 count  
--------
 100000
(1 row)
-- 创建物化视图
omm=# create materialized view mv_test as select * from test where testnum%2=0;
CREATE MATERIALIZED VIEW
omm=# select count(*) from mv_test ;
 count 
-------
 50000
(1 row)

omm=#

4. 手动更新物化视图

refresh materialized view

-- 基表插入新的数据
omm=# insert into test(testnum) values (generate_series(1,100000));
INSERT 0 100000
omm=# select count(*) from test;
omm=#  count  
--------
 200000
(1 row)
-- 查看物化视图
omm=# select count(*) from mv_test ;
omm=#  count 
-------
 50000
(1 row)

-- 手动更新物化视图信息
omm=# refresh materialized view mv_test ;
omm=# REFRESH MATERIALIZED VIEW

-- 查看物化视图数据        
omm=# select count(*) from mv_test ;
 count  
--------
 100000
(1 row)

omm=# select * from gs_matview;
 matviewid | mapid | ivm | needrefresh |        refreshtime         
-----------+-------+-----+-------------+----------------------------
     16412 |     0 | f   |             | 2022-12-11 20:51:05.172255
(1 row)

omm=#

5. 删除创建的视图

–删除普通视图

drop view <view_name>

–删除物化视图

drop materialized view <view_name>;

--查看当前的普通视图
omm=# \dv
                     List of relations
 Schema |          Name           | Type | Owner | Storage 
--------+-------------------------+------+-------+---------
 tpcds  | customer_detail_view_v1 | view | omm   | 
 tpcds  | part_view               | view | omm   | 
(2 rows)

omm=# drop view part_view ;
DROP VIEW
omm=# \dv
                     List of relations
 Schema |          Name           | Type | Owner | Storage 
--------+-------------------------+------+-------+---------
 tpcds  | customer_detail_view_v1 | view | omm   | 
(1 row)
-- 查看物化视图
omm=# select * from gs_matview;
 matviewid | mapid | ivm | needrefresh |        refreshtime         
-----------+-------+-----+-------------+----------------------------
     16412 |     0 | f   |             | 2022-12-11 20:51:05.172255
(1 row)
-- 删除物化视图
omm=# drop materialized view mv_test ;
omm=# DROP MATERIALIZED VIEW

omm=# select * from gs_matview;
 matviewid | mapid | ivm | needrefresh | refreshtime 
-----------+-------+-----+-------------+-------------
(0 rows)

omm=#

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

openGauss每日一练第 18 天 |学习笔记

视图

语法格式

创建视图

刪除视图

查看视图

物化视图

语法格式

创建物化视图

全量刷新物化视图

删除物化视图

PG_VIEWS

查看普通视图的信息

GS_MATVIEWS

查看物化视图的信息

序列号生成函数

课后作业

1. 创建表，创建普通视图

2. 使用视图创建新的视图

3. 创建物化视图

4. 手动更新物化视图

5. 删除创建的视图

评论