ORACLE 第 3 章

心已赠人 2024-03-25 20:11 54阅读 0赞

**ORACLE 数据库**

第 3 章

ORACLE 对象

## 一、视图 ##

### （一）什么是视图 ###

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。

根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使用。

使用视图的优点：

1.简化数据操作：视图可以简化用户处理数据的方式。

2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。

3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问

权限。

4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接

口。

### （二）创建或修改视图语法 ###

CREATE [OR REPLACE] [FORCE] VIEW view_name 
    AS subquery 
    [WITH READ ONLY]

选项解释：

OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；

FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；

subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；

WITH READ ONLY ：该视图上不能进行任何 DML 操作。

### （三）删除视图语法 ###

DROP VIEW view_name

### （四）例子 ###

需求：分页逻辑过于复杂，创建一个分页视图

-- 创建视图
    create view t_user_page as 
    select * from (select id,addTime,userName,password,rownum r from (select id,addTime,userName,password from t_user order by id desc) where rownum <=20) t where t.r>10;
    
    -- 查询视图
    select * from  t_user_page;
    
    -- 创建或替换视图
    create or REPLACE FORCE VIEW t_user_page as
    select id,addTime,userName,password from t_user;

## 二、序列 ##

### （一）什么是序列 ###

序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。

### （二）创建与使用简单序列 ###

创建序列语法：

create sequence 序列名称

通过序列的伪列来访问序列的值

NEXTVAL 返回序列的下一个值

CURRVAL 返回序列的当前值

***注意：我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。***

提取下一个值

select 序列名称.nextval from dual

提取当前值

select 序列名称.currval from dual

create sequence t_user_id_seq;
    
    select t_user_id_seq.nextval from dual;
    
    select t_user_id_seq.currval from dual;

### （三）创建复杂序列 ###

语法：

CREATE SEQUENCE sequence //创建序列名称
    [INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默
    认是 1
    [START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
    [{MAXVALUE n | NOMAXVALUE}] //最大值
    [{MINVALUE n | NOMINVALUE}] //最小值
    [{CYCLE | NOCYCLE}] //循环/不循环
    
    [{CACHE n | NOCACHE}];//分配并存入到内存中

**1) INCREMENT BY：**用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

**2) START WITH：** 定义序列的初始值(即产生的第一个值)，默认为1。

**3) MAXVALUE：** 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

**4) MINVALUE：**定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

**5) CYCLE和NOCYCLE ：**表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

**6) CACHE：**(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。

**7) NEXTVAL ：**返回序列中下一个有效的值，任何用户都可以引用。

**8) CURRVAL ：**中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效。

### （四）修改和删除序列 ###

修改序列：使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START WITH 参数

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

删除序列：

DROP SEQUENCE 序列名称;

### （五）查看序列 ###

select * from user_sequences ;

可以加上查询条件

### （六）插入语句使用序列 ###

-- 使用当前序列
    insert into t_user(id,addTime,userName,password) 
    values(t_user_id_seq.currval+100,sysdate,'张三','123456');
    
    -- 使用下一个序列
    insert into t_user(id,addTime,userName,password) 
    values(t_user_id_seq.nextval+100,sysdate,'张三','123456');

## 四、索引 ##

### （一）什么是索引 ###

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于 下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提 到的伪列（ROWID）

![1b3547777f6c4e4388909a39c76b82e9.png][]

### （二）普通索引 ###

语法：

create index 索引名称 on 表名(列名);
    
    drop index 索引名称;

需求：给t\_user表的userName创建索引

create index index_t_user_userName on t_user(userName);

索引性能测试：

创建一个两个字段的表

create table T_INDEXTEST (
     ID NUMBER,
     NAME VARCHAR2(30)
    );

编写 PL/SQL 插入 100 万条记录（关于 PL/SQL 我们在第四章会学到）

BEGIN
     FOR i in 1..1000000
     loop
     INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
     end loop;
     commit;
    END;

创建完数据后，根据 name 列创建索引

CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name);

执行下面两句 SQL 执行

SELECT * from T_INDEXTEST where ID=765432;
    SELECT * from T_INDEXTEST where NAME='AA765432';

我们会发现根据 name 查询所用的时间会比根据 id 查询所用的时间要短

![efdba5af215a4b72800634c0a74512f4.png][]

![2b05b0d4d3eb4e69968477ed2c90067e.png][]

![8029bdaa4b004d7787dc11b333f5f483.png][]

![168fa3656c2b4ffa99df51eeff5dc4ab.png][]

### （三）唯一索引 ###

如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可以创建唯一索引。

语法：

create unique index 索引名称 on 表名(列名);

需求：建唯一索引

语句：

create unique index INDEX_T_INDEXTEST_ID on
    T_INDEXTEST(id);

唯一索引执行搜索效率更高

### （四）索引分类 ###

适当的使用索引可以提高数据检索速度，那Oracle有哪些类型的索引呢？

**1、b-tree索引**：Oracle数据中最常见的索引，就是b-tree索引，create index创建的normal就是b-tree索引，没有特殊的必须应用在哪些数据上。

**2、bitmap位图索引**：位图索引经常应用于列数据只有几个枚举值的情况，比如上面说到过的性别字段，或者我们经常开发中应用的代码字段。这个时候使用bitmap位图索引，查询效率将会最快。

**3、函数索引**：比如经常对某个字段做查询的时候经常是带函数操作的，那么此时建一个函数索引就有价值了。例如：trim（列名）或者substr(列名)等等字符串操作函数，这个时候可以建立函数索引来提升这种查询效率。

**4、hash索引**：hash索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。创建hash索引必须使用hash集群，相当于定义了一个hash集群键，通过这个集群键来告诉oracle来存储表。因此，需要在创建HASH集群的时候指定这个值。存储数据时，所有相关集群键的行都存储在一个数据块当中，所以只要定位到hash键，就能快速定位查询到数据的物理位置。

**5、reverse反向索引**：这个索引不经常使用到，但是在特定的情况下，是使用该索引可以达到意想不到的效果。如：某一列的值为\{10000,10001,10021,10121,11000,....\}，假如通过b-tree索引，大部分都密集分布在某一个叶子节点上，但是通过反向处理后的值将变成\{00001,10001,12001,12101,00011,...\}，很明显的发现他们的值变得比较随机，可以比较平均的分部在各个叶子节点上，而不是之前全部集中在某一个叶子节点上，这样子就可大大提高检索的效率。

**6、分区索引和分区表的全局索引**：这两个索引是应用在分区表上面的，前者的分区索引是对分区表内的单个分区进行数据索引，后者是对分区表的全表进行全局索引。分区表的介绍，可以后期再做单独详解，这里就不累述了。

[1b3547777f6c4e4388909a39c76b82e9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f6d72213673143ab9f3c57fdd62fdb86.png
[efdba5af215a4b72800634c0a74512f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/df9522e7a12e426bbef664de24f18127.png
[2b05b0d4d3eb4e69968477ed2c90067e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/38e388e9f393424bbb30ad5b7ab472b1.png
[8029bdaa4b004d7787dc11b333f5f483.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/dff4df87db88489bbd1d5149a1c58bc5.png
[168fa3656c2b4ffa99df51eeff5dc4ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/585bc8492f774ac19998515a136eb8bb.png