视图

视图是一个存储和命名的选择查询。默认情况下,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_typecust_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