oracle数据库
- Oracle是一个数据库管理系统,是Oracle公司的核心产品。Oracle在信息管理系统、企业数据处理、Internet电子商务领域使用非常广泛。其在数据库安全性控制方面的优越性能,以及夸操作系统、跨硬件平台的数据互操作能力,使得越来越多的用户将Oracle作为其应用数据的处理系统。与SQL
Server相同,两者是关系数据库,支持SQL
92标准,Oracle是目前最流行的数据库,它占有最大的实参份额,安全更高,可为大型数据库提供更好的支持。
1、SCOTT数据库对象介绍
(1)Oracle数据库是数据的物理存储.这就包括(数据文件ORA或者DBF,控制文件,联机日志,参数文件).其实Oracle数据库的概念和其它数据库不一样,这里的数据库是一个操作系统只有一个库.可以看做是Oracle就只有一个大数据库.
(2)Oracle数据库是数据的物理存储.这就包括(数据文件ORA或者DBF,控制文件,联机日志,参数文件).其实Oracle数据库的概念和其它数据库不一样,这里的数据库是一个操作系统只有一个库.可以看做是Oracle就只有一个大数据库.
(3)表空间是Oracle对物理数据库上相关数据库(ORA或者DBF文件)的逻辑映射.一个数据库在逻辑上被划分成一到若干个表空间,每个表空间包含了在逻辑上相关联的的一组结构.每个数据库至少有一个表空间(称之为system表空间).
每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文件(datafile).一个数据文件只能属于一个表空间.
**用户:**用户是在实例下创建的.不同实例中可以创建相同名字的用户.
注:表的数据,是由用户放入某一个表空间的,而这个表空间会随机把这些数据放到一个或多个数据文件中.
由于oracle的数据库不是普通的概念,oracle是由用户和表空间对数据进行管理和存放的.但是表不是由表空间去查询的,而是由用户去查的.因为不同用户可以在同一个表空间建立同一个名字的表!这里区分就是用户了!
2、Oracle数据库应用
SQL中的函数一般是在数据上执行的,可以很方便地转换和处理数据。
常用的SQL函数
内置函数分成四类:
a.算术函数
b.字符串函数:
c.日期函数:
d.转换函数:
对聚合函数不做介绍,详细内容请回顾以前学习的知识。分析函数是对一个查询结果中的每个分组进行运算,但每个分组对应的结果可以有多个,本章将解部分分析函数。
3、ORACLE数据库对象和管理
(1).同义词:
(1)简化SQL语句
(2)隐藏对象的名称和所有者
(3)为分布式数据库的远程对象提供了位置透明性
(4)提供对对象的公共访问
同义词权限管理:
与同义词相关的权限有CREATE SYNONYM、CREATE ANY SYNONYM、CREATE PUBLIC SYNONYM权限。
1:用户在自己的模式下创建私有同义词,这个用户必须拥有CREATE SYNONYM权限,否则不能创建私有同义词。
如下所示,用户DM缺少CREATE SYNONYM权限,创建同义词时会报ORA-01031错误
SQL> CREATE SYNONYM TEST FOR DM.TM_WGG_ATM_GTW_MON;
CREATE SYNONYM TEST FOR DM.TM_WGG_ATM_GTW_MON
用sys账号给DM账号赋予CREATE SYNONYM的权限
SQL> GRANT CREATE SYNONYM TO DM;
然后创建私有同义词
SQL> CREATE SYNONYM TEST FOR DM.TM_WGG_ATM_GTW_MON;
4.PL/SQL编程
(1)在运行程序时出现的错误叫做异常。发生异常后,语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。值得注意的是,PL/SQL编译错误发生在PL/SQL编程执行之前,所以不能由PL/SQL异常处理部分来处理。
异常情况处理(exception)用来处理正常执行过程中为预料的事件。
(2)Oracle预定的异常情况大约有24个。对于这种异常情况的处理,在程序中定义,可由Oracle自动引发,常见的异常如表。
BEGIN
sequence of statements;
EXCEPTION
WHEN <exception name> THEN
sequence of statements;
WHEN OTHERS THEN
sequence of statements;
END;
5.存储过程
**1、**子程序的组成,子程序是己命名的PL/SQL块,它们储存在数据库中,可以为它们指定参数,也可以从任何数据库客户端和应用程序中调用它们。子程序包括存储过程和函数。使用存储过程执行操作,使用函数执行操作并返回值,就像应用程序中的方法一样。
与匿名的PL/SQL块一样,子程序具有声明部分,可执行部分,异常处理部分(可选).
**(1)**声明部分包括类型,游标,常量,变量,异常,和嵌套子程序的声明。这些项是局部的,退出子序后不复存在。
**(2)**可执行部分包括赋值,控制执行过程以及操作Oracle数据的语句。
(3)异常处理部分
异常处理部分包括异常处理程序,负责处理执行存储过程中出现的异常。
子程序的优点如下:
(1)模块化。
(2)可重用性。
(3)可维护性。
(4)安全性。
总结
1、减少访问数据库的次数。
2、不要让数据库做得太多。
(1)SELECT子句中避免使用’ * ‘:ORACLE在解析的过程中,会将’ * ‘依次转换成所有的列名, 这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。
(2)sql语句用大写的:因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行。另外,在java代码中尽量少用连接符“+”连接字符串!
(3)使用表的别名(Alias):当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
(4) 用>=替代>:
高效: SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
(5) 用EXISTS替代IN、用NOT EXISTS替代NOT IN:在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。 在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例子:
(高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
(6)用EXISTS替换DISTINCT:例如:
低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
(7)尽量用UNION-ALL 替换UNION ( if possible)
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序。
低效:SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
(7) 避免在索引列上使用NOT:通常,避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。 当Oracle”遇到”NOT,就会停止使用索引转而执行全表扫描。
(8)用索引提高效率:索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构。 通常,通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径时,Oracle优化器将使用索引。同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。通常, 在大型表中使用索引特别有效。 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O 。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的。