视图

视图是一个存储和命名的选择查询。默认情况下，hubble的视图是非实体化的：它们不存储底层查询的结果。相反，每次使用视图时都会重新执行基础查询。

其中，hubble还支持物化视图，物化视图是存储其选择查询结果的视图。

普通视图

视图可以理解为一张表或多张表的预计算，这些表称为基表。
它可以将所需要查询的结果封装成一张虚拟表，基于它创建时指定的查询语句返回的结果集。

物化视图

为了防止每次都查询，将结果集存储起来，这种有真实数据的视图，称为物化视图。

物化视图的适用情况

基表没有很多DML，且每次查询都需要耗费较大资源的情况下，可以考虑用物化视图。

视图的作用

使用视图有多种原因，包括：

安全性：只将需要的结果呈现出来，查询者不知道具体用了哪些表或哪些字段，因此比较安全；
屏蔽复杂性：下层计算可能做了很多复杂的关联操作，只需要让开发者将其实现，将结果以视图呈现给使用者。

隐藏查询复杂性

当有一个复杂的查询，例如，连接多个表或执行复杂的计算时，您可以将查询存储为视图，然后像从标准表中一样从视图中进行选择。

比如要查询每个部门下员工的人数：

select d.dname ,count(1) from 
dept d 
join emp e 
on  d.deptno=e.deptno 
group by d.dname;

    dname    | count
-------------+--------
  OPERATIONS |     1
  SALES      |     6
  BOSS       |     1
  ACCOUNTING |     8
  RESEARCH   |     9

为了更轻松地运行这个复杂的查询，可以创建一个视图：

create view dname_c (dname,num)
as
select d.dname ,count(1) from 
dept d 
join emp e 
on  d.deptno=e.deptno 
group by d.dname;

然后，执行查询就像SELECT单表语句一样简单：

select * from dname_c;

    dname    | count
-------------+--------
  OPERATIONS |     1
  SALES      |     6
  BOSS       |     1
  ACCOUNTING |     8
  RESEARCH   |     9

限制对基础数据的访问

当您不想授予用户访问一个或多个标准表中所有数据的权限时，您可以创建一个仅包含用户应该有权访问的的视图。

示例：假设有一张客户信息表cust_info：

select * from cust_info;

 cust_no  | cust_name |    cust_card_no    | cust_phoneno | cust_address | cust_type
-----------+-----------+--------------------+--------------+--------------+------------
  14435550 | 王吉      | 12022519960321531X |  15122511874 | 天津武清     | 抵押
  14435551 | 张贺      | 431256197306265320 |  15534343555 | 山西临汾     | 质押
  14435552 | 刘明      | 371452199303034312 |  18967756743 | 陕西延安     | 信用
  14435553 | 李华      | 52112119860621421X |  15833355455 | 湖北武汉     | 抵押
  14435554 | 郑青      | 213456199102275341 |  13054546567 | 江西南昌     | 质押

您希望特定用户abc，能够查看每个用户拥有的帐户类型，而无需查看每个帐户中的身份证号，手机号，因此您创建一个视图以仅显示cust_type和cust_name列：

create view users
as 
select cust_type, cust_name
from cust_info;

您授予abc视图users访问权限：

grant select on users to abc;

现在，abc在尝试访问基础cust_info表时会出现权限错误：

select * from cust_info;

pq: user abc does not have SELECT privilege on table cust_info

select * from users;

  cust_type | cust_name
------------+------------
  抵押      | 王吉
  质押      | 张贺
  信用      | 刘明
  抵押      | 李华
  质押      | 郑青

视图的工作原理

创建视图

create view dname_c (dname,num)
as
select d.dname ,count(1) from 
dept d 
join emp e 
on  d.deptno=e.deptno 
group by d.dname;

任何选择查询都可以CREATE VIEW，而不仅仅是简单的SELECT子句。

列出视图

创建后，视图将与数据库中的常规表，序列等一起列出：

show tables from ora;

public      | user_table   | table    | root   |                   6 | NULL
public      | userid_seq   | sequence | root   |                   1 | NULL
public      | users        | view     | root   |                   0 | NULL
public      | weihai       | table    | root   |                   0 | NULL

要仅列出视图，您可以查询Information Schemaviews中的表：

select * from  information_schema.views;

查询视图

要查询视图，请使用表表达式作为目标，例如使用SELECT子句，就像使用存储表一样：

select * from users;

  cust_type | cust_name
------------+------------
  抵押      | 王吉
  质押      | 张贺
  信用      | 刘明
  抵押      | 李华
  质押      | 郑青

SELECT视图执行视图存储的SELECT语句，该语句从基础表返回相关数据。要检查的SELECT视图执行的语句，请使用以下SHOW CREATE语句：

show create users;

  table_name |                                             create_statement
-------------+-----------------------------------------------------------------------------------------------------------
  users      | CREATE VIEW public.users (cust_type, cust_name) AS SELECT cust_type, cust_name FROM ora.public.cust_info

您还可以通过查询Information Schema中的表来检查SELECT视图执行的语句：

SELECT view_definition FROM information_schema.views WHERE table_name = 'users';

                     view_definition
---------------------------------------------------------
  SELECT cust_type, cust_name FROM ora.public.cust_info

查看依赖项

视图取决于其基础查询所针对的对象。因此，尝试重命名视图的存储查询中引用的对象会导致错误：

alter table cust_info rename to custs;

ERROR: cannot rename relation "cust_info" because view "users" depends on it
SQLSTATE: 2BP01
HINT: you can drop users instead.

同样，尝试删除视图的存储查询中引用的对象会导致错误：

drop table cust_info;

ERROR: cannot drop relation "cust_info" because view "users" depends on it
SQLSTATE: 2BP01
HINT: you can drop users instead.

alter table cust_info drop column cust_name;

ERROR: rejected (sql_safe_updates = true): ALTER TABLE DROP COLUMN will remove all data in that column
SQLSTATE: 01000

同样，因为没有视图依赖于表的cust_address列，所以可以删除它。要删除其中包含数据的列，您必须首先设置sql_safe_updates = false

set sql_safe_updates = false;

alter table cust_info drop column cust_address;

show  columns from cust_info;

  column_name  |  data_type  | is_nullable | column_default | generation_expression |               indices                | 
---------------+-------------+-------------+----------------+-----------------------+---------------------------------------
  cust_no      | STRING      |    false    | NULL           |                       | {cust_info_cust_card_no_idx,primary} |  
  cust_name    | VARCHAR(30) |    false    | NULL           |                       | {}                                   |  
  cust_card_no | VARCHAR(18) |    true     | NULL           |                       | {cust_info_cust_card_no_idx}         |  
  cust_phoneno | DECIMAL(15) |    true     | NULL           |                       | {}                                   |   
  cust_type    | VARCHAR(10) |    true     | NULL           |                       | {}                                   |

删除表或删除视图时，您也可以使用CASCADE关键字删除所有依赖对象：

drop table cust_info CASCADE;

重命名视图

要重命名视图，请使用以下ALTER VIEW语句：

alter view users rename to userinfo;

替换视图

要替换视图，请使用CREATE OR REPLACE VIEW：

create or  replace view user_info
as 
select cust_type, cust_name
from cust_info;

select * from user_info limit 5;

  cust_type | cust_name
------------+------------
  抵押      | 王吉
  质押      | 张贺
  信用      | 刘明
  抵押      | 李华
  质押      | 郑青

删除视图

要删除视图，请使用以下DROP VIEW语句：

drop view user_info;

物化视图

hubble支持物化视图。物化视图是存储其基础查询结果的视图。

因为物化视图存储查询结果，它们提供比标准视图更好的性能，但代价是存储查询结果所需的额外存储空间和保证结果是最新的。

用法

物化视图和标准视图在创建、显示、重命名和删除方面共享相似的语法。

要创建物化视图，请使用CREATE MATERIALIZED VIEW语句。

示例：

CREATE MATERIALIZED VIEW emppeo
as select empno, ename
  from emp where deptno = 10 ;

select * from emppeo;

 empno |  ename
--------+----------
   9988 | laihui
   7839 | KING
   1357 | FREE
   7367 | PA_I%RS
   7934 | MILLER
   7782 | CLARK
   7777 | aaa
   5555 | kettle

现在假设您更新了表

delete from emp where deptno =10;

现在查询表emp里面部门号位10的数据是不存在的

select count(1) from emp where deptno =10;

  count
---------
      0

回想一下，物化视图不会自动更新其存储的结果。此时任然返回数据

select * from emppeo;

 empno |  ename
--------+----------
   9988 | laihui
   7839 | KING
   1357 | FREE
   7367 | PA_I%RS
   7934 | MILLER
   7782 | CLARK
   7777 | aaa
   5555 | kettle

要更新物化视图的结果，请使用以下REFRESH语句：

REFRESH MATERIALIZED VIEW emppeo;

select * from emppeo;

 empno |  ename
--------+----------

要删除物化视图，请使用DROP MATERIALIZED VIEW：

DROP MATERIALIZED VIEW emppeo;

向物化视图添加索引

要加快对物化视图的查询，您可以向视图添加索引：

create index on emppeo (empno) STORING (ename);

查看添加索引后计划的变化：

explain select ename from emppeo where empno ='1234';

                info
------------------------------------
  distribution: local
  vectorized: false

  • scan
    missing stats
    table: emppeo@emppeo_empno_idx
    spans: [/1234 - /1234]

临时视图

细节

会话结束时会自动删除临时视图。
临时视图只能从创建它的会话中访问。
临时视图在同一会话中的事务中持续存在。
临时视图不能转换为持久视图。
可以在持久表和临时表上创建临时视图。
在临时表上创建视图时，视图会自动变为临时视图。

用法

要创建临时视图，请将TEMP添加到CREATE VIEW语句中。并且先设置变量SET experimental_enable_temp_tables = 'on'。

例如：

先设置变量：

SET experimental_enable_temp_tables = 'on'

创建临时视图：

create temp view dname_c (dname,num)
as
select d.dname ,count(1) from 
dept d 
join emp e 
on  d.deptno=e.deptno 
group by d.dname;

查询视图结果：

select * from dname_c;

    dname    | count
-------------+--------
  OPERATIONS |     1
  SALES      |     6
  BOSS       |     1
  ACCOUNTING |     8
  RESEARCH   |     9