Oracle数据库对象（表空间/同义词/序列/视图/索引）

数据库对象

Oracle数据库对象：

数据库对象是数据库的组成部分，常常用 CREATE 命令进行创建，可以使用 ALTER 命令修改，用 DROP 执行删除操作。

种类：

（1）表空间：所有的数据对象都存在指定的表空间中。

（2）同义词：就是给数据库对象起一个别名。

（3）序列：Oracle中实现增长的对象。

（4）视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。

（5）索引：对数据库表中的某些列进行排序，便于提高查询效率。

 

 表空间

　　在数据库系统中，存储空间是较为重要的资源，合理利用空间，不但能节省空间，还可以提高系统的效率和工作性能。Oracle可以存放海量数据，所有数据都在数据文件中存储。而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时 Oracle 是跨平台的数据库，Oracle 数据可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢？Oracle 采用表空间来解决。

　　表空间只是一个逻辑概念，若干操作系统文件（文件可以不是很大）可以组成一个表空间。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。数据空间、表空间、数据文件的关系如图所示：

 

Oracle 中所有的数据（包括系统数据），全部保存在表空间。

 

常见的表空间

（1）系统表空间：

存放系统数据，系统表空间在数据库创建时创建。表空间命名为 SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息，在运行时如果 SYSTEM 空间不足，对数据库影响会比较大，虽然在系统运行过程中可以通过命令扩充空间，但还是会影响数据库的性能，因此有必要在创建数据库时适当的把数据文件设置大一些。

（2）TEMP表空间：

临时表空间在安装数据库时创建，可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如 Order by 等命令后，服务器需要对所选取数据进行排序，如果数据很大，内存的排序区可能装不下太大数据，就需要把一些中间的排序结果写在硬盘的临时表空间中。

（3）用户自定义表空间：

用户可以通过 CREATE TABLESPACE 命令创建表空间。

 

同义词
定义：给数据库对象起的别名
分类：私有同义词、公有同义词

公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。非系统管理员用户如果要创建共有同义词，需要系统管理员授予  CREATE PUBLIC SYNONYM 权限。

私有同义词：拥有 CREATE SYNONYM 权限的用户（包括非管理员用户）即可创建私有同义词，创建的私有同义词只能由当前用户使用。

--语法结构：CREATE [OR REPLACE] [PUBLIC] SYNONYM [SCHEMA.]同义词名 FOR [SCHEMA.]数据库对象;
--对EMP表创建一个私有同义词 E
--注意：创建私有同义词需要赋予CREATE SYNONYM 权限；
CREATE OR REPLACE SYNONYM E FOR SCOTT.EMP;

--对一个表创建共有同义词 EE
--注意：创建共有同义词需要赋予CREATE PUBLIC SYNONYM权限；
CREATE OR REPLACE PUBLIC SYNONYM EE FOR SCOTT.EMP;

--查询当前用户下的系统权限
SELECT * FROM SESSION_PRIVS;

SELECT * FROM EMP;
SELECT * FROM E;
SELECT * FROM EE;

--删除同义词
--DROP SYNONYM 同义词名;
DROP SYNONYM E;
--注意：删除共有同义词需要赋予删除的权限 DROP PUBLIC SYNONYM;
DROP PUBLIC SYNONYM EE;


--给新用户JERRY授予某一些表的查询权限(如果是当前用户给新用户授权，SCOTT.表 中的SCOTT可以省略)
GRANT SELECT ON SCOTT.EMP TO JERRY;

 

序列
作用：用来生成连续的整数数据

--创建序列的语法结构：
/*
  CREATE SEQUENCE 序列名
  [START WITH X]            --默认 X = 1
  [INCREMENT BY Y]          --默认 Y = 1
  [MAXVALUE A | NOMAXVALUE] --默认 NOMAXVALUE
  [MINVALUE B | NOMINVALUE] --默认 NOMINVALUE
  [CYCLE | NOCYCLE]         --默认 NOCYCLE
  [CACHE C | NOCACHE];      --默认 CACHE 20

START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1；
INCREMENT BY：增长数。如果是正数，则升序生成，如果是负数，则降序生成。升序默认值是1，降序默认值是-1；
MAXVALUE：最大值；
NOMAXVALUE：这是最大值一栏中的默认选项，升序的最大值是：10 ** 27，降序默认值是-1；
MINVALUE：最小值；
NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是 -10**26；CYCLE：表示如果升序达到最大值后，从最小值重新开始。如果是降序序列，达到最小值后，从最大值重新开始；
NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错，默认选项是 NOCYCLE；
CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle 默认会生成 20 个序列号；
NOCACHE：不预先在内存中生成序列号。
例如：CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE MINVALUE 1 NOCYCLE CACHE 30; 
*/

CREATE SEQUENCE SEQ_TEST;


--访问序列的值
--伪列 CURRVAL : 访问序列的当前值是多少
--伪列 NEXTVAL ：访问序列的下一个值是多少
--注意点：刚创建的序列，需要通过NEXTVAL来启动序列，否则无法直接访问CURRVAL
SELECT SEQ_TEST.CURRVAL
  FROM DUAL;
SELECT SEQ_TEST.NEXTVAL
  FROM DUAL;
    
--使用ALTER SEQUENCE可以修改序列，在修改序列时有如下限制：
--1.不能修改序列的初始值。
ALTER SEQUENCE SEQ_TEST START WITH 2;

--2.最小值不能大于当前值。
ALTER SEQUENCE SEQ_TEST MINVALUE 11;

--3.最大值不能小于当前值。  
ALTER SEQUENCE SEQ_TEST MAXVALUE 8;

  
--删除序列
--DROP SEQUENCE 序列名;
DROP SEQUENCE SEQ_TEST;

 

视图
定义：一张或者多张表上的预定义查询
优点：
1、可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表，具有安全性。
2、可以将复杂的查询保存为视图，屏蔽复杂性。

--创建视图的语法结构：
--CREATE [OR REPLACE] VIEW 视图名 AS SELECT查询语句 [WITH READ ONLY];
--注意：创建视图需要赋予创建视图的权限 CREATE VIEW
CREATE OR REPLACE VIEW V_EMP AS SELECT * FROM EMP WHERE DEPTNO = 10;

SELECT * FROM V_EMP;


--对可读可写的视图来说，更新视图同时也更新了基表的数据
--创建视图时，不加 WIRH 权限 ONLY的约束，默认是拥有对基表的增删改查的权限
DELETE FROM V_EMP;

--删除视图
--DROP VIEW 视图名;
DROP VIEW V_EMP;


--基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行INSERT操作
CREATE OR REPLACE VIEW V_EMP_TEST AS SELECT ENAME,SAL FROM EMP WHERE DEPTNO = 10;

SELECT * FROM V_EMP_TEST;

INSERT INTO V_EMP_TEST(ENAME, SAL) VALUES('jerry',8888);
--相当于执行了下面的步骤(EMPNO主键没有出现在视图中，主键不能为NULL)
INSERT INTO EMP
  (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES
  (NULL,'jerry',NULL,NULL,NULL,8888,NULL,NULL);
  
DROP VIEW V_EMP_TEST;  
  
--创建一个只读视图
CREATE OR REPLACE VIEW V_EMP_READ AS SELECT * FROM EMP WITH READ ONLY;

DELETE FROM V_EMP_READ;

 

索引：相当于是目录

注意：

（1）如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引；

（2）当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。

（3）Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因为索引对增删改时会有负面影响。

 

索引分类

常用索引：

1、唯一索引（用的最多）：

（1）何时创建：当某列任意两行的值都不相同。

（2）当建立 Primary Key（主键）或者 Unique constraint（唯一约束）时，唯一索引将被自动建立。

2、组合索引：

（1）何时创建：当两个或多个列经常一起出现在 where 条件中时，则在这些列上同时创建。

（2）组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面。

3、位图索引：

（1） 何时创建：列中有非常多的重复的值时候。例如某列保存了“性别”信息。

（2）Where条件中包含了很多 OR 操作符，较少的 UPDATE 操作，因为要相应的更新所有的 bitmap；

4、基于函数的索引：

（1） 何时创建：在 WHERE 条件语句中包含函数或者表达式时。

（2）函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

不常用索引：

5、反向键索引：

6、键压缩索引：

7、索引组织表（IOT）：

8、分区索引：

--创建索引的语法结构：
--CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名);

--为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引
CREATE UNIQUE INDEX IDX_ENAME ON EMP(ENAME);  --唯一索引
CREATE INDEX IDX_SAL ON EMP(SAL);             --普通索引
CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));--基于函数的索引
CREATE INDEX IDX_DEPTNO_SAL ON EMP(DEPTNO,SAL);--组合索引

--注意：索引创建之后，ORCEAL会自动引用该索引
--组合索引中的顺序是任意的，但是建议将频繁访问的列放在列表的前面    
--删除索引
--DROP INDEX 索引名;
DROP INDEX IDX_SAL;

SELECT * FROM EMP WHERE DEPTNO = 10 AND SAL > 1000; 

 

索引优缺点：

优点：

创建索引可以大大提高系统的性能。

（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

（2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因；

（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；

（4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

缺点：

（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；

（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大；

（3）当对表中的数据进行增加、删除、修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

 

问题：如果一张表有20个索引，往里面写入100万条数据，怎么优化？

先把索引全删了，然后把记录写入进去，再建立索引。

 

应用场景：

在这些列上创建索引：

（1）经常需要搜索的列上，可以加快搜索的速度；

（2）经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

（3）经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

（4）经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

 

在这些列上不应该创建索引：

（1）对于那些在查询中很少使用或者参考的列不应该创建索引；

（2）对于那些只有很少数据值的列也不应该创建索引；

（3）对于那些定义为 text、image、bit数据类型的列不应该增加索引；

（4）当修改性能远远大于检索性能时，不应该创建索引。修改性能与检索性能是相互矛盾的。

 

索引失效的情况：

（1）隐式转换导致索引失效，这一点应当引起重视，也是开发中经常会犯的错误。

（2）对索引列进行运算导致索引失效，运算包括 + ,-,*,/,!等。

（3）使用 Oracle 内部函数导致索引失效，对于这种情况应该创建基于函数的索引。

（4）使用 <>、NOT IN、NOT EXIST、!=。

（5） LIKE '%_' 百分号在前（可采用在建立索引时用 REVERSE(COLUMN_NAME)这种方法处理）。

（6） 单独引用复合索引里非第一位置的索引列。

（7）字符型字段为数字时，在 WHERE 条件里不添加引号。

（8）当变量采用的是 TIMES 变量，而表的字段采用的是 DATE 变量时。（或相反情况）