数据库对象
Oracle数据库对象:
数据库对象是数据库的组成部分,常常用 CREATE 命令进行创建,可以使用 ALTER 命令修改,用 DROP 执行删除操作。
种类:
(1)表空间:所有的数据对象都存在指定的表空间中。
(2)同义词:就是给数据库对象起一个别名。
(3)序列:Oracle中实现增长的对象。
(4)视图:预定义的查询,作为表一样的查询使用,是一张虚拟表。
(5)索引:对数据库表中的某些列进行排序,便于提高查询效率。
同义词
定义:给数据库对象起的别名
分类:私有同义词、公有同义词
公有同义词:系统管理员可以创建公有同义词,公有同义词可以被所有用户访问。非系统管理员用户如果要创建共有同义词,需要系统管理员授予 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 变量时。(或相反情况)