关于java:持续输出面试题系列之Oracle篇

大家好，我是 Java 最全面试题库 的提裤姐，明天这篇是数据库面试题系列的第三篇，次要总结了 Oracle 相干的面试题；在后续，会沿着第一篇开篇的常识线路始终总结上来，做到日更！如果我能做到百日百更，心愿你也能够跟着百日百刷，一百天养成一个好习惯。

存储过程（Stored Procedure）是一组为了实现特定性能的 SQL 语句集，经编译后存储在数据库中。用户
通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
长处：

容许模块化程序设计，就是说只须要创立一次过程，当前在程序中就能够调用该过程任意次。
容许更快执行，如果某操作须要执行大量 SQL 语句或反复执行，存储过程比 SQL 语句执行的要快。
缩小网络流量，例如一个须要数百行的 SQL 代码的操作有一条执行语句实现，不须要在网络中发送数百行代码。
好的平安机制，对于没有权限执行存储过程的用户，也可受权他们执行存储过程。

存储过程创立语法：

create or replace procedure 存储过程名（param1 in type，param2 out type）as
变量 1 类型(值范畴);
变量 2 类型(值范畴);
Begin
 Select count(*) into 变量 1 from 表 A where 列名 =param1；If (判断条件) then
   Select 列名 into 变量 2 from 表 A where 列名 =param1；Dbms_output。Put_line(‘打印信息’);
 Elsif (判断条件) then
   Dbms_output。Put_line(‘打印信息’);
 Else
   Raise 异样名(NO_DATA_FOUND);
 End if;
Exception
 When others then
   Rollback;
End;

注意事项：

存储过程参数不带取值范畴，in 示意传入，out 示意输入
变量带取值范畴，前面接分号
在判断语句前最好先用 count（*）函数判断是否存在该条操作记录
用 select ...into...给变量赋值
在代码中抛异样用 raise+ 异样名

特点：

一般来说，存储过程实现的性能要简单一点，而函数的实现的性能针对性比拟强。
对于存储过程来说能够返回 参数 ，而函数只能返回 值或者 表对象。
存储过程个别是作为一个 独立的局部 来执行，而函数能够作为 查问语句的一个局部 来调用，因为函数能够返回一个表对象，因而它能够在查问语句中位于 FROM 关键字的前面。

区别：

函数必须有返回值；而过程没有
函数能够独自执行；而过程必须通过 execute 执行
函数能够嵌入到 SQL 语句中执行；而过程不行

咱们能够将比较复杂的查问写成函数，而后到存储过程中去调用这些函数

Oracle 中的游标分为 显示游标 和隐式游标
显示游标：
显示游标是用 cursor...is 命令定义的游标，它能够对查问语句 (select) 返回的多条记录进行解决；
显式游标的操作：关上游标、操作游标、敞开游标；

隐式游标：
隐式游标是在执行 插入 (insert)、删除 (delete)、 批改 (update) 和返回 单条记录的查问 (select) 语句时由 PL/SQL 主动定义的。
PL/SQL 隐式地关上 SQL 游标，并在它外部解决 SQL 语句，而后敞开它。

应用 || 符号连贯字符串，如 ‘abc’ || ‘d’ 的后果是 abcd。

Oracle 中应用 rownum 来进行分页，这个是效率最好的分页办法，hibernate 也是应用 rownum 来进行 Oralce 分页的

select * from 
  (select rownum r,a from tableName where rownum <= 20)
where r>10

1、能够了解函数是存储过程的一种
2、函数能够没有参数，然而肯定须要一个返回值；存储过程能够没有参数，不须要返回值
3、函数 return 返回值没有返回参数模式，存储过程通过 out 参数返回值，如果须要返回多个参数则倡议应用存储过程
4、在 sql 数据操纵语句中只能调用函数而不能调用存储过程

触发器可通过数据库中的相干表实现级联更改；通过级联援用完整性束缚能够更无效地执行这些更改。
触发器能够强制比用 CHECK 束缚定义的束缚更为简单的束缚。与 CHECK 束缚不同，触发器能够援用其它表中的列。例如，触发器能够应用另一个表中的 SELECT 比拟插入或更新的数据，以及执行其它操作，如批改数据或显示用户定义错误信息。
触发器还能够强制执行业务规定
触发器也能够评估数据批改前后的表状态，并依据其差别采取对策。

Oracle 能存储 clob、nclob、blob、bfile

Clob 可变长度的字符型数据，也就是其余数据库中提到的文本型数据类型
Nclob 可变字符类型的数据，不过其存储的是 Unicode 字符集的字符数据
Blob 可变长度的二进制数据
Bfile 数据库里面存储的可变二进制数据

Oracle 的分区能够分为：列表分区 、 范畴分区 、 散列分区 、 复合分区 。
1、 加强可用性 ：如果表的一个分区因为系统故障而不能应用，表的其余好的分区仍能够应用；
2、 缩小敞开工夫 ：如果系统故障只影响表的一部份分区，那么只有这部份分区须要修复，可能比整个大表修复花的工夫更少；
3、 保护轻松 ：如果须要得建表，独产治理每个公区比治理单个大表要轻松得多；
4、 平衡 I /O：能够把表的不同分区调配到不同的磁盘来均衡 I / O 改善性能；
5、改善性能 ：对大表的查问、减少、批改等操作能够合成到表的不同分区来并行执行，可使运行速度更快
6、 分区对用户通明，最终用户感觉不到分区的存在。

数据库设计方面：
1. 对查问进行优化，应尽量避免全表扫描，首先应思考在 where 及 order by 波及的列上建设索引。
2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃应用索引而进行全表扫描，如：select id from t where num is null 能够在 num 上设置默认值 0，确保表中 num 列没有 null 值，而后这样查问：select id from t where num=0
3. 并不是所有索引对查问都无效，SQL 是依据表中数据来进行查问优化的，当索引列有大量数据反复时，查问可能不会去利用索引，如一表中有字段 sex，male、female 简直各一半，那么即便在 sex 上建了索引也对查问效率起不了作用。
4. 索引并不是越多越好，索引诚然能够进步相应的 select 的效率，但同时也升高了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎么建索引须要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应思考一些不常应用到的列上建的索引是否有必要。
5. 应尽可能的防止更新索引数据列，因为索引数据列的程序就是表记录的物理存储程序，一旦该列值扭转将导致整个表记录的程序的调整，会消耗相当大的资源。若利用零碎须要频繁更新索引数据列，那么须要思考是否应将该索引建为索引。
6. 尽量应用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会升高查问和连贯的性能，并会减少存储开销。这是因为引擎在解决查问和连贯时会一一比拟字符串中每一个字符，而对于数字型而言只须要比拟一次就够了。
7. 尽可能的应用 varchar/nvarchar 代替 char/nchar，因为首先变长字段存储空间小，能够节俭存储空间，其次对于查问来说，在一个绝对较小的字段内搜寻效率显然要高些。
8. 尽量应用表变量来代替长期表。如果表变量蕴含大量数据，请留神索引十分无限（只有主键索引）。
9. 防止频繁创立和删除长期表，以缩小零碎表资源的耗费。
10. 长期表并不是不可应用，适当地应用它们能够使某些例程更无效，例如，当须要反复援用大型表或罕用表中的某个数据集时。然而，对于一次性事件，最好应用导出表。
11. 在新建长期表时，如果一次性插入数据量很大，那么能够应用 select into 代替 create table，防止造成大量 log，以进步速度；如果数据量不大，为了弛缓零碎表的资源，应先 create table，而后 insert。

如果应用到了长期表，在存储过程的最初务必将所有的长期表显式删除，先 truncate table，而后 drop table，这样能够防止零碎表的较长时间锁定。

SQL 语句方面：
1. 应尽量避免在 where 子句中应用 != 或<>操作符，否则将引擎放弃应用索引而进行全表扫描。
2. 应尽量避免在 where 子句中应用 or 来连贯条件，否则将导致引擎放弃应用索引而进行全表扫描，如：select id from t where num=10 or num=20 能够这样查问：`select id from t where num=10 union all
select id from t where num=20`
3.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于间断的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
4. 上面的查问也将导致全表扫描：select id from t where name like‘%abc%’
5. 如果在 where 子句中应用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将拜访打算的抉择推延到运行时；它必须在编译时进行抉择。然而，如果在编译时建设拜访打算，变量的值还是未知的，因此无奈作为索引抉择的输出项。如上面语句将进行全表扫描：select id from t where num=@num 能够改为强制查问应用索引：select id from t with(index(索引名)) where num=@num
6. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃应用索引而进行全表扫描。
如：select id from t where num/2=100 应改为: select id from t where num=100*2
7. 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃应用索引而进行全表扫描。
如：select id from t where substring(name,1,3)=‘abc’ – name 以 abc 开头的 id
select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的 id
应改为: select id from t where name like‘abc%’，
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'.
不要在 where 子句中的“=”右边进行函数、算术运算或其余表达式运算，否则零碎将可能无奈正确应用索引。
8. 不要写一些没有意义的查问，
如须要生成一个空表构造：select col1,col2 into #t from t where 1=0 这类代码不会返回任何后果集，然而会耗费系统资源的，应改成这样：create table #t(…)
9. 很多时候用 exists 代替 in 是一个好的抉择：
select num from a where num in(select num from b)
用上面的语句替换：select num from a where exists(select 1 from b where num=a.num)
10. 任何中央都不要应用 select * from t，用具体的字段列表代替“*”，不要返回用不到的任何字段。
11. 尽量避免应用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该思考改写。
12. 尽量避免向客户端返回大数据量，若数据量过大，应该思考相应需要是否正当。
13. 尽量避免大事务操作，进步零碎并发能力。

java 方面：
1. 尽可能的少造对象。
2. 正当摆正零碎设计的地位。大量数据操作，和大量数据操作肯定是离开的。大量的数据操作，必定不是 ORM 框架搞定的。
3. 应用 JDBC 链接数据库操作数据
4. 管制好内存，让数据流起来，而不是全副读到内存再解决，而是边读取边解决；
5. 正当利用内存，有的数据要缓存

其余方面：
1.Oracle 的运行环境（网络，硬件等）
2. 应用适合的优化器
3. 合理配置 oracle 实例参数
4. 建设适合的索引（缩小 IO）
5. 将索引数据和表数据离开在不同的表空间上（升高 IO 抵触）
6. 建设表分区，将数据别离存储在不同的分区上（以空间换取工夫，缩小 IO）

冷备份 产生在数据库曾经失常敞开的状况下，将关键性文件拷贝到另外地位的一种说法
热备份 是在数据库运行的状况下，采纳归档形式备份数据的办法
冷备的长处：

是十分疾速的备份办法（只需拷贝文件）

容易归档（简略拷贝即可）
容易复原到某个工夫点上（只需将文件再拷贝回去）
能与归档办法相结合，作数据库“最新状态”的复原。
低度保护，高度平安。

冷备份毛病：

独自应用时，只能提供到“某一时间点上”的复原。
在施行备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是敞开状态。
若磁盘空间无限，只能拷贝到磁带等其它内部存储设备上，速度会很慢。
不能按表或按用户复原。

1、data block 数据块，是 oracle 最小的逻辑单位，通常 oracle 从磁盘读写的就是块
2、extent 区，是由若干个相邻的 block 组成
3、segment 段，是有一组区组成
4、tablespace 表空间，数据库中数据逻辑存储的中央，一个 tablespace 能够蕴含多个数据文件

关于java:持续输出面试题系列之Oracle篇

开篇介绍

什么是存储过程，应用存储过程的益处？

Oracle 存储过程怎么创立？

存储过程和存储函数的特点和区别

如何应用 Oracle 的游标？

Oracle 中字符串用什么连贯？

Oracle 中是如何进行分页查问的？

Oracle 中 function 和 procedure 的区别?

触发器的作用有哪些？

Oralce 怎么存储文件，可能存储哪些文件？

Oracle 分区是怎么优化数据库的?

在千万级的数据库查问中，如何提高效率？

解释冷备份和热备份的不同点以及各自的长处？

data block , extent 和 segment 的区别？