大数据BI学习之数据库对象

数据库对象

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 变量时。(或相反情况)

 

大数据BI学习之数据库对象

上一篇:PostgreSQL 13 流复制配置


下一篇:大数据BI学习之SQL的简单查询