ORCALE学习笔记（8）（数据库对象：表空间、视图、序列、同义词、索引等等）

Oracle数据库对象

表空间：所有的数据对象都存在指定的表空间中。
同义词：就是给数据库对象一个别名。—跨账户查询的时候使用
序列：Oracle中实现增长的对象。----程序包里面使用
视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。
索引：对数据库表中的某些列进行排序，便于提高查询效率。

同义词-----跨账户查询的时候使用

同义词（Synonym）是数据库对象的一个别名，Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。

同义词有两种类型：

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

• 公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。

  创建同义词的语法是：

语法结构：创建同义词 ----创建同义词需要在系统用户下执行创建同义词语句

CREATE OR REPLACE PUBLIC SYNONYM synonym_name 
FOR schema.object_name

语法解析：

①CREATE OR REPLACE：表示在创建同义词时，如果该同义词已经存在，那么就用新创建的同义词代替旧同义词。
② PULBIC ：创建公有同义词时使用的关键字，一般情况下不需要创建公有同义词。
③Oracle中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所有数据库对象的集合称为Schema
（中文称为模式或者方案），用户名就是 Schema 名。一个数据库对象的全称是：用户名.对象名，即schema.object_name。

如果一个用户有权限访问其他用户对象时，就可以使用全称来访问。比如：

代码演示：System 用户访问Scott用户的Emp表

SELECT ENAME,JOB,SAL FROM SCOTT.EMP WHERE SAL>2000;

代码解析：
①管理员用户可以访问任何用户的数据库对象，SYSTEM 用户访问SCOTT用户的EMP表时，必须使用 SCOTT.EMP。

删除同义词使用的语法是：

语法结构：删除同义词 ----------删除的时候也要在系统用户下执行

DROP PUBLIC SYNONYM [schema.]sysnonym_name 

语法解析：
①PUBLIC：删除公共同义词。
②同义词的删除只能被拥有同义词对象的用户或者管理员删除。
③此命令只能删除同义词，不能删除同义词下的源对象。

序列—封装程序包的时候使用序列

序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的数据可以升序生成
，也可以降序生成。

创建序列的语法是：

语法结构：创建序列

CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]

语法解析：
①START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。
②INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是1，降序默认值是-1。
③MAXVALUE：指最大值。
④NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。
⑤MINVALUE：指最小值。
⑥NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。
⑦CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。
⑧NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。
⑨CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产20个序列号。
NOCACHE：不预先在内存中生成序列号。

CREATE SEQUENCE ABC;

视图 ----视图一般在分析处理复杂逻辑的时候使用
视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。
从视图中查询信息与从表中查询信息的方法完全相同。
只需要简单的 SELECT…FROM 即可。

视图具有以下优点：

1.可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表，具有安全性。
2.可以将复杂的查询保存为视图，屏蔽复杂性。

语法结构：创建视图

CREATE OR REPLACE FORCE|NOFORCE VIEW view_name
AS
SELECT 查询
WITH READ ONLY  -----

语法解析：

1. OR REPLACE：如果视图已经存在，则替换旧视图。
   2.FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。
   3.NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
2. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制
   （比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行 insert 操作）
   ，WITH READ ONLY 说明视图是只读视图，不能通过该视图进行增删改操作。
   现实开发中，基本上不通过视图对表中的数据进行增删改操作。

CREATE OR REPLACE FORCE VIEW ABC_1
AS
SELECT
      A.EMPNO
     ,A.ENAME 
FROM ABC_EMP A
WITH READ ONLY;
-----------------------
CREATE TABLE ABC_EMP AS SELECT * FROM EMP ;
SELECT EMPNO FROM ABC_1;

--------------------------
CREATE OR REPLACE VIEW EMPDETAIL
AS
SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME
FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
WITH READ ONLY
--通过视图查询
SELECT * FROM EMPDETAIL; 

语法结构：删除视图

DROP VIEW 视图名

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

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

系统表空间：存放系统数据，系统表空间在数据库创建时创建。表空间名称为SYSTEM。 存放数据字典和视图以及数据库结构等重要系统数据信息，在运行时如果SYSTEM空间不足，对数据库影响会比较大，虽然在系统运行过程中可以通过命令扩充空间，但还是会影响数据库的性能，因此有必要在创建数据库时适当的把数据文件设置大一些。
TMEP表空间：临时表空间，安装数据库时创建，可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如Order by等命令后，服务器需要对所选取数据进行排序，如果数据很大，内存的排序区可能装不下太大数据，就需要把一些中间的排序结果写在硬盘的临时表空间中。
用户自定义表空间：用户可以通过CREATE TABLESPACE命令创建表空间。

select * FROM USER_TABLES;

select object_type  FROM USER_OBJECTS GROUP BY object_type;

索引
1 索引介绍
当我们在某本书中查找特定的章节内容时，可以先从书的目录着手，找到该章节所在的页码，然后快速的定位到该页。
这种做法的前提是页面编号是有序的。如果页码无序，就只能从第一页开始，一页页的查找了。

数据库中索引（Index）的概念与目录的概念非常类似。
如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。
创建索引就是对某些特定列中的数据排序，生成独立的索引表。
在某列上创建索引后，如果该列出现在查询条件中，Oracle会比较全表扫描与索引扫描的代价， 如果索引扫描代价小，那Oracle会自动引用该索引，先从索引表中查询出符合条件记录的 ROWID，由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。 当从很多行的表中查询少数行时，也要考虑创建索引。

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

语法结构：创建索引

CREATE UNIQUE INDEX index_name ON table_name(column_name,column_name…)

语法解析：
1.UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2.index_name：指定索引名。
3.table_name：指定要为哪个表创建索引。
4.column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引称为组合索引。

----为 EMP_1 表的ENAME列创建创建唯一索引，为EMP_1表的工资列创建普通索引，把 JOB 列先变为小写再创建索引。

CREATE UNIQUE INDEX ASDF ON EMP_1(ename );
CREATE  INDEX PK_13 ON EMP_1(SAL);
CREATE  INDEX PK_14 ON EMP_1(LOWER(JOB));

SELECT * FROM EMP_1 WHERE ENAME ='SCOTT';

代码解析：
①为SCOTT.EMP表的ENAME列创建唯一索引。
②在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，
可以先对JOB列中的所有值转换为小写后创建索引，而这时需要使用lower函数，这种索引称为基于函数的索引。

在 select 语句查询时，Oracle系统会自动为查询条件上的列应用索引。
索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。
Oracle可以为一些列值重复非常多且值有限的列（比如性别列）上创建位图索引

语法结构：删除索引

DROP INDEX 索引名;

2 索引种类
唯一索引（用的最多）
1、何时创建：当某列任意两行的值都不相同
2、当建立 Primary Key(主键)或者 Unique constraint(唯一约束)时，唯一索引将被自动建立

组合索引
1、何时创建：当两个或多个列经常一起出现在 where 条件中时，则在这些列上同时创建
2、组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

位图索引
1、何时创建：列中有非常多的重复的值时候。例如某列保存了 “性别”信息。
Where 条件中包含了很多OR操作符。

基于函数的索引
1、何时创建：在WHERE条件语句中包含函数或者表达式时
2、函数包括：算数表达式、PL/SQL 函数、程序包函数、SQL函数、用户自定义函数。

反向键索引
键压缩索引
索引组织表(IOT)
分区索引

3 索引优缺点
创建索引可以大大提高系统的性能。
-----优点
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
----缺点
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

如果一张表20个索引，往里面写入100万条数据，怎么优化？
–先把索引全删了，再把记录写进去，再建立索引。

索引是建立在数据库表中的某些列的上面。
在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引：
在经常需要搜索的列上，可以加快搜索的速度；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。
这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。
相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。
这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，
结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。
增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。
这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。
当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
因此，当修改性能远远大于检索性能时，不应该创建索引。-

4 索引失效
----1、隐式转换：索引列实际的数据类型是由 VARCHAR2 转化为 NUMBER 时，会引起索引失效
–例如：

SELECT * FROM EMP A WHERE A.EMPNO='7788';--索引列由NUMBER 隐式转化成VARCHAR2 索引没有失效

------------------------------------------
CREATE TABLE EMP_1 AS   SELECT*
                       FROM EMP A ;


ALTER TABLE  EMP_1 ADD EMPNO_1 VARCHAR2(8);

UPDATE EMP_1 A SET A.EMPNO_1=TO_CHAR(A.EMPNO) WHERE A.EMPNO=(SELECT B.EMPNO FROM EMP_1 B WHERE A.EMPNO=B.EMPNO);
COMMIT;
ALTER TABLE EMP_1 ADD CONSTRAINTS PK_AS PRIMARY KEY (EMPNO_1);
---------------------------------------
SELECT * FROM EMP_1 A WHERE A.EMPNO_1=7788;--索引失效的原因是：索引列由 VARCHAR2  隐式转化成  NUMBER

------2、模糊查询引起索引失效

select * FROM EMP_1 A WHERE A.EMPNO_1 LIKE'%7%';

-----3、对索引列进行算数运算

SELECT * FROM EMP A WHERE A.EMPNO-1=7787;--索引失效
SELECT * FROM EMP A WHERE A.EMPNO=7789-1;--索引没有失效

-----4、IS NULL值判断时 索引失效

SELECT * FROM EMP_1 A WHERE A.SAL IS  NULL ;---索引失效
SELECT * FROM EMP_1 A WHERE A.SAL IS not  NULL ;---索引不失效

----5、使用 <> 、not in 、not exists、!= 会引起索引失效

SELECT * FROM EMP A WHERE A.EMPNO<>7788;