当前位置:   article > 正文

ORCALE学习笔记(8)(数据库对象:表空间、视图、序列、同义词、索引等等)_数据库中的表、视图、表空间、索引、序列之间的关系

数据库中的表、视图、表空间、索引、序列之间的关系

Oracle数据库对象

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

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

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

同义词有两种类型:

  • 私有同义词:拥有 CREATE SYNONYM 权限的用户(包括非管理员用户)即可创建私有同义词,
    创建的私有同义词只能由当前用户使用。

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

    创建同义词的语法是:

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

CREATE OR REPLACE PUBLIC SYNONYM synonym_name 
FOR schema.object_name
  • 1
  • 2

语法解析:

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

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

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

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

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

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

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

DROP PUBLIC SYNONYM [schema.]sysnonym_name 
  • 1

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

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

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

创建序列的语法是:

语法结构:创建序列

CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

语法解析:
①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;
  • 1

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

视图具有以下优点:

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

语法结构:创建视图

CREATE OR REPLACE FORCE|NOFORCE VIEW view_name
AS
SELECT 查询
WITH READ ONLY  -----
  • 1
  • 2
  • 3
  • 4

语法解析:

  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; 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

语法结构:删除视图

DROP VIEW 视图名
  • 1

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

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

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

select * FROM USER_TABLES;
  • 1
select object_type  FROM USER_OBJECTS GROUP BY object_type;
  • 1

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

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

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

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

语法结构:创建索引

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

语法解析:
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';
  • 1
  • 2
  • 3
  • 4
  • 5

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

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

语法结构:删除索引

DROP INDEX 索引名;
  • 1

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 索引没有失效
  • 1
------------------------------------------
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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

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

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

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

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

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

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

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

SELECT * FROM EMP A WHERE A.EMPNO<>7788;
  • 1
声明:本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号