视图与索引

最新推荐文章于 2023-02-25 21:29:01 发布
最新推荐文章于 2023-02-25 21:29:01 发布
阅读量434 收藏
点赞数
文章标签： 数据库

 

视图view是我们在开发中经常使用的数据库对象。通过视图，我们可以将复杂查询逻辑屏蔽，向上提供更具有业务含义的数据访问接口。

 

本质上说，视图是一个SQL语句构成的虚拟表。我们对视图的查询就相当于对SQL语句形成数据集合的再次查询。当我们需要提高视图查询性能的时候，是从什么地方着手呢？

 

索引可能是我们最直接的想法。借助查询索引，可以为Oracle优化器提供一个可供选择的优化路径。但是，索引是一个对象，一般设置在有实际数据的数据表上，而视图本身没有任何数据（除物化视图之外）。对普通视图列加索引是不可以的。

 

 

SQL> create or replace view v_t1 as

  2     select *

  3       from t;

 

View created

 

SQL> create index idx_v_t1_from on v_t1(SERIES_FROM);

 

create index idx_v_t1_from on v_t1(SERIES_FROM)

 

ORA-01702: 视图不适用于此处

 

 

那么，是不是说使用视图就无法进行性能方面的优化了吗？答案是否定的，我们可以从视图使用的基表入手，设置优化策略。下面我们根据视图使用的几个场景分别进行分析。

 

环境构建

 

建立数据表t，结构如下：

 

SQL> create table t

  2  ( seq_number number(13),

  3    series_from varchar2(10),

  4    series_to varchar2(10),

  5    comm varchar2(20));

 

Table created

 

SQL> desc t;

Name        Type         Nullable Default Comments

----------- ------------ -------- ------- --------

SEQ_NUMBER  NUMBER(13)                            

SERIES_FROM VARCHAR2(10)                          

SERIES_TO   VARCHAR2(10)                          

COMM        VARCHAR2(20)                          

 

//在series from 与series to列建立唯一索引（唯一约束是业务要求）

SQL> create unique index idx_t_from on t(series_from);

 

Index created

 

SQL> create unique index idx_t_to on t(series_to);

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

//之后，借助pl/sql脚本生成测试数据，与本议题无关，略过。

 

 

单数据表查询

 

业务场景：根据一个票号（10位长度）搜索出对应的票号段。

 

实验SQL：select * fromt where series_fromlpad('1000',10,'0');

 

在不使用视图的情况下，我们查询号段。

 

SQL> select * from scott.t where series_fromlpad('1000',10,'0');

 

已用时间:  00: 00: 00.14

 

执行计划

----------------------------------------------------------

Plan hash value: 1600714554

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |    17 |   476 |     2   (0)|00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |    17 |   476 |     2   (0)|00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_FROM |    18 |       |     1   (0)|00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("SERIES_TO">'0000001000')

   2 - access("SERIES_FROM"

统计信息

----------------------------------------------------------

       1388  recursive calls

          0  db block gets

        275  consistent gets

          0  physical reads

          0  redo size

        524  bytes sent via SQL*Net to client

        377  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         28  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

发现，当我们直接对表使用查询的时候，优化器选择了索引作为执行路径。

 

同名视图查询

 

首先，我们测试最简单的一种视图形式。视图对应一个数据表，而且视图列名与数据表名相同。

 

SQL> create or replace view v_t1 as

  2    select *

  3      from t;

View created

 

 

进行相同查询。

 

SQL> select * from scott.v_t1 where series_fromlpad('1000',10,'0');

 

已用时间:  00: 00: 00.14

 

执行计划

----------------------------------------------------------

Plan hash value: 1600714554

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |    17 |   476 |     2   (0)|00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |    17 |   476 |     2   (0)|00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_FROM |    18 |       |     1   (0)|00:00:01 |

------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("SERIES_TO">'0000001000')

   2 - access("SERIES_FROM"

统计信息

----------------------------------------------------------

       1596  recursive calls

          0  db block gets

        319  consistent gets

          0  physical reads

          0  redo size

        524  bytes sent via SQL*Net to client

        377  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         36  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

发现，虽然使用了视图v_t1作为查询对象，但是产生的执行计划、成本消耗和访问数据路径与对数据表t进行查询的结果一样。而且执行时间都是一样。

 

所以，在单表同列名视图的情况下，我们对视图的查询与数据表相同。对视图施加的条件可以完全映射到数据表上，并且应用适当的索引优化路径。

 

单表异名视图查询

 

那么，上面的情况是不是因为视图列表与数据表列名恰好相同。如果在定义视图的时候使用了一系列的as别名，查询视图的时候使用别名进行查询，会不会有同样的结论呢？

 

SQL> create or replace view v_t2 as

  2    select t.seq_number as a, t.series_from as b, t.series_to as c, t.comm as d

  3      from t;

 

View created

 

SQL> select * from scott.v_t2 where blpad('1000',10,'0');

 

已用时间:  00: 00: 00.03

 

执行计划

----------------------------------------------------------

Plan hash value: 1600714554

 

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |    17 |   476 |     2   (0)|00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |    17 |   476 |     2   (0)|00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_FROM |    18 |       |     1   (0)|00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("T"."SERIES_TO">'0000001000')

   2 - access("T"."SERIES_FROM"

统计信息

----------------------------------------------------------

        688  recursive calls

          0  db block gets

        146  consistent gets

          0  physical reads

          0  redo size

        494  bytes sent via SQL*Net to client

        377  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         13  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

结论显而易见，与上面的结果相同。执行计划相同，路径一致。而且值得关注的是，将对应视图的查询条件完整的映射为数据基表的列名。

 

 

Union多结果集合异列名

 

最后，我们一起看看使用union的视图 。union相当于将多个数据表数据源的对象集合合并处理。由于对应数据表不同，常常使用异列名情况，对应的逻辑也往往比较复杂。

 

SQL> create or replace view v_t3 as

  2    select t.seq_number as a, t.series_from as b, t.series_to as c, t.comm as d

  3      from t

  4    union all

  5    select t1.seq_number as a, t1.series_from as b, t1.series_to as c, t1.comm as d

  6      from t1

  7      with check option;

 

View created

 

 

 

其中，数据表t1是一张与t相同结构的数据表。应用相同的查询后，我们看结果。

 

 

SQL> select * from scott.v_t3 where blpad('1000',10,'0');

 

已用时间:  00: 00: 00.08

 

执行计划

----------------------------------------------------------

Plan hash value: 2234649797

 

----------------------------------------------------------------------------------------------

| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |             |    18 |   702 |     6  (34)| 00:00:01 |

|   1 |  VIEW                          | V_T3        |    18 |   702 |     6  (34)| 00:00:01 |

|   2 |   SORT UNIQUE                  |             |    18 |   504 |     6  (67)| 00:00:01 |

|   3 |    UNION-ALL                   |             |       |       |            |

|*  4 |     TABLE ACCESS BY INDEX ROWID| T           |    17 |   476 |     2   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN          | IDX_T_FROM  |    18 |       |     1   (0)| 00:00:01 |

|*  6 |     TABLE ACCESS BY INDEX ROWID| T1          |     1 |    28 |     2   (0)| 00:00:01 |

|*  7 |      INDEX RANGE SCAN          | IDX_T1_FROM |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter("T"."SERIES_TO">'0000001000')

   5 - access("T"."SERIES_FROM"

   6 - filter("T1"."SERIES_TO">'0000001000')

   7 - access("T1"."SERIES_FROM"

 

统计信息

----------------------------------------------------------

          8  recursive calls

          0  db block gets

          5  consistent gets

         25  physical reads

          0  redo size

        494  bytes sent via SQL*Net to client

        377  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

结论也很明显。在union的情况下，Oracle将对视图的查询条件根据列名映射规则，转为union多个数据集合的额外查询条件。并且分别应用上了数据表的索引路径。

 

有一点需要说明的是，这种情况下进行搜索的时间可能略长。主要是因为多个数据表都要进行查询和结果集合合并。这种情况一般也是业务方面能够允许的损耗。

 

 

最后，我们说的视图不能加索引，是仅限于普通视图。如果是带有数据信息的物化视图，是可以建立索引的。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-684157/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-684157/

ciqu9915
关注
Oracle---视图 & 索引 &语法结构
飞机的博客
03-09 1303
视图所对应的数据， 并不是真正的存储在 视图 中，而是 存储在所引用的数据表 中。应用场景：当某个字段的值为 连续增长的值，如果构建标准索引，会形成歪脖子树。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。合理的使用索引可以大大降低 I/O 次数，从而提高数据访问性能。索引是需要占据存储空间的，也可以理解为是一种特殊的数据。需要在某个表某个列创建索引，而这列的值是 不会重复的。建立复合索引，也就是基于两个以上的列建立一个索引。视图的结构和数据，是对数据表进行查询的结果。
Oracel sql基础常见问题
weixin_44548432的博客
03-14 1186
Oracel sql基础常见问题 1.1问题:日期函数的使用 ，无效月份的问题 1.2解决方案：需要更改 ALTER SESSION SET nls_date_language=‘american’; 2.1问题：如何将date类型更改为数字？ 2.2解决方案：to_number(to_char(st.D_LASTMODIFY,‘YYYYMMDD’)) 3.1问题：创建时有missing expression问题 3.2解决方案： （1）缺失逗号，关键词等细节问题； (2) 规范性： 需要注意的是
关于索引的几个问题
congwan1707的博客
12-30 360
索引应该是sql优化中的一员大将了，近期学习了一下索引，希望能在此梳理一下我看到的和理解的有关索引的一些知识点。 1、索引列有null值，索引会失效，这是真的吗？ 经过试验...
有关于视图
Fred Pay的专栏
04-26 322
<br />http://tech.16c.cn/mysql/200903022889.html
Hive的视图与索引
最新发布
晓晓的天空
02-25 538
Hive的视图与索引
【视图与索引】
weixin_53061888的博客
12-08 1360
数据库视图与索引 1.视图 1.1视图介绍 数据库中的视图是一个虚拟表。同真实地表格一样，包含一系列带有名称的行和列数据。行和列数据来自由定义视图查询所引用的表，并且在引用视图时，动态生成。 视图是一个虚拟表，是从数据库中的一个或多个表中导出来的表。它可以从已经存在的视图的基础上定义。一旦定义后便存储在数据库中，与其对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据知识放在基本表中的数据。对视图的操作与对表的操作一样，可以查询、修改、删除。当通过视图看到的数据进行变化时，相应的基本表中的数据也
实验七 视图与索引
05-09
### 实验七 视图与索引 #### 实验背景 在数据库系统中，视图和索引是非常重要的概念。视图可以简化复杂的查询过程，为用户提供更直观的数据展示方式，而索引则能显著提升数据检索的速度。本次实验旨在通过一系列...
Oracle之索引，视图，序列
A0911X的博客
11-21 1732
cache|no cache:指定序列缓存，默认缓存20个,oracle会预先在内存中放置一组指定大小的序列，当使用完这些序列后再生成下一组，这样会存取得快些，但当数据库关闭等情况时，下一次再生成序列时可能会使序列间断，不是一串连续的号，当不是特别需要连续的序列时最好指定；给name字段加上索引，oracle就会对全表进行一次搜索，将每条记录的name值进行升序排列，然后构建索引条目，存储在索引段中，搜索name=’scott’ ，直接找索引排序后的结果。--这样它就会使用索引。--不能直接取当前值。
Oracle --- 视图 & 索引 &语法结构
qq_57151931的博客
12-28 2037
Oracle --- 视图 & 索引 &语法结构
oracle的高阶使用【2】视图与索引
万和IT教育
05-24 370
视图是一张特殊的表，是一个虚拟表，封装了一条查询语句，视图中的数据来自于查询语句所对应的表，在引用视图时会动态生成。视图可以提高数据的安全性，可以简化复杂查询，但是不可以建立索引。 索引，可以加快查询速度，典型的以空间换时间，一般给频繁使用的字段加索引，也经常给id加索引，主键就具备索引特性。 1、系统、软件和约束条件 win10 oracle11g sql developer 2、视图操...
sqlserver 建立索引视图各问题！
11-10 8708
--我的 tab 表有 60398 条数据,大家可以用自己的任意表测试 SELECT * FROM tab GO --新建表MYTABLE,带序号 IF object_id(N'[dbo].[MYTABLE]') IS NOT NULL drop table MYTABLE SELECT IDENTITY(INT,1,1) ID,* INTO MYTABLE FROM tab GO SE
Update 视图“ORA-01733: 此处不允许虚拟列” 分享
热门推荐
N
03-09 2万+
ORA-01733: 此处不允许虚拟列 原因分析：一般对视图的更新，只能对简单视图进程更新，而不能对多表关联形成的视图进程更新。          对视图的更新，最终要反映到对表的字段的更新上。          Update 视图时，如果视图有分组函数、GROUP BY 子句、DISTINCT 关键字、ROWNUM 等伪列、常量表达式这些时，是不能修改视图的。 //把视图重新cr
Oracle insert时报错ORA-01733: 此处不允许虚拟列
weixin_30575309的博客
05-20 2604
原因分析：一般对视图的更新，只能对简单视图进程更新，而不能对多表关联形成的视图进程更新。对视图的更新，最终要反映到对表的字段的更新上。 Update 视图时，如果视图有分组函数、GROUP BY 子句、DISTINCT 关键字、ROWNUM 等伪列、常量表达式这些时，是不能修改视图的。 把视图重新create后再进行insert into。 ok! 转载于:https://www.cnblo...
数据库课程组讲解：视图与索引的应用与重要性
课程重点包括视图的概念、创建与查询，以及索引的优缺点和定义。" 在数据库管理中，视图（View）是一种非常重要的工具，它允许用户根据特定需求定制数据展示方式。视图不存储实际数据，而是基于一个或多个基本表...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值