Oracle — 数据库对象

📋 本文节选自《Oracle 面试题》，返回 查看全部章节

22. Oracle 分区在什么情况下使用

当一张表的数据量到达上亿行的时候，表的性能会严重降低，这个时候就需要用到分区了，通过划分成多个小表，并在每个小表上建立本地索引可以大大缩小索引数据文件的大小，从而更快的定位到目标数据来提升访问性能。

分区除了可以用来提升访问性能外，还因为可以指定分区所使用的表空间，因此也用来做数据的生命周期管理。当前需要频繁使用的活跃数据可以放到访问速度更快但价格也更贵的存储设备上，而 2、3 年前的历史数据，或者叫冷数据可以放到更廉价、速度更低的设备上，从而降低存储费用。

23.说一下，Oracle 的分区有几种

Oracle 的分区可以分为：列表分区、范围分区、散列分区（哈希分区）、复合分区。

列表分区：

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

范围分区：

1. 就是根据数据库表中某一字段的值的范围来划分分区。
2. 数据中有空值，Oracle 机制会自动将其规划到 maxvalue 的分区中。

散列分区（哈希分区）

1. 根据字段的 hash 值进行均匀分布，尽可能地实现各分区所散列的数据相等。
2. 散列分区即为哈希分区，Oracle 采用哈希码技术分区，具体分区如何由 Oracle 说的算，也可能我下一次搜索就不是这个数据了。

复合分区

根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区同再使用散列分区的一种分区方法。

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

分别从数据库设计方面、SQL 优化语句方面，物理优化方面

数据库设计方面

对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引；

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在 num 上设置默认值 0 ，确保表中 num 列没有 null 值，然后这样查询：select id from t where num = 0

并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，查询可能不会去利用索引如一表中有字段sex、male、female几乎各占一半，那么即使在sex上建了索引也对查询效率起不了作用；

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

应尽可能的避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源若应用系统需要频繁更新索引数据列，那么需要考虑是否应将该索引建为索引；

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些；

尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

避免频繁创建和删除临时表，以减少系统表资源的消耗；

临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

在新建临时表时，如果一次性插入数据量很大，那么可以使用selectinto代替createtable，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先createtable，然后insert；

如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table，然后 drop table，这样可以避免系统表的较长时间锁定。

从SQL 语句方面

应尽量避免在where子句中使用!=或<操作符，否则将引擎放弃使用索引而进行全表扫描；

应尽量避免在 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

in和notin也要慎用，否则会导致全表扫描，如：selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and3；

下面的查询也将导致全表扫描：select id from t where name like '%abc%'

如果在where子句中使用参数，也会导致全表扫描因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项如下面语句将进行全表扫描：selectidfromtwherenum=@num可以改为强制查询使用索引：selectidfromtwith(index(索引名)wherenum=@num；

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。 如：select id from t where num/2 = 100 应改为：select id from t where num = 100*2

应尽量避免在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 子句中的 " = " 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

如需要生成一个空表结构：select col1,col2 intot from t where1 = 0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create tablet(...)

很多时候用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)

任何地方都不要使用 select from t，用具体的字段列表代替 "" ,不要返回用不到的任何字段。

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写；

尽量避免向客户返回大数据量，若数据量过大，应该考虑相应需求是否合理。

尽量避免大事务操作，提高系统并发能力；

物理优化方面

1. Oracle 的运行环境（网络，硬件等）
2. 使用合适的优化器
3. 合理配置 oracle 实例参数
4. 建立合适的索引（减少 IO )
5. 将索引数据和表数据分开在不同的表空间上（降低O冲突）
6. 建立表分区，将数据分别存储在不同的分区上（以空间换取时间，减少 IO )

25.使用存储过程访问数据库比直接用SQL语句访问有何优点？

1. 存储过程是预编译过的，执行时不须编译，执行速度更快。
2. 存储过程封装了多条 SQL ，便于维护数据的完整性与一致性。
3. 实现代码复用。

26.索引是用来干什么的？有那些约束建立索引？

说下你怎么使用索引的？使用索引的好处和坏处？

1. 索引用于对指定字段查询时，提升查询速度。
2. 主要有 B 树索引，位图索引，函数索引。
3. 对查询频率比较高的字段做索引，但一张表不要做太多索引。
4. 索引能提升查询效率，但它占用存储空间，且在更新数据时也会影响更新效率。

27.说下内连接，左连接，右连接的区别

内连接：
指主表，从表中符合连接条件的记录全部显示
左连接：
外连接方式，主要是显示主表，从表中符合连接条件的记录，并且主表中所有不符合连接条件的记录也要显示。
右连接：
外连接方式，主要是显示主表，从表中所有符合连接条件的记录，并且从表中不符合的记录也要显示。

28.说说 Oracle 中经常使用到的函数

length 长度、lower 小写、upper 大写、to_date 转化日期、to_char 转化字符、to_number 转化数字、Ltrim 去左边空格、rtrim 去右边空格、substr 截取字符串、add month 增加或减掉月份

29.truncate 和 delete 区别

1. Truncate 和 delete 都可以将数据实体删掉，truncate 的操作并不记录到 rollback 日志，所以操作速度较快，但同时这个数据不能恢复
2. Delete 操作不腾出表空间的空间
3. Truncate 不能对视图等进行删除
4. Truncate 是数据定义语言(DDL)，而 delete 是数据操纵语言(DML)

30.存储过程、函数、游标在项目中怎么用的

存储过程：

1. 能够批量执行的一组 SQL 语句，且容易控制事务。但没有返回值，可以通过设置 in out | out 类型的参数返回结果
2. 存储过程可以没有参数，不需要返回值

函数：

与存储过程相似，函数可以没有参数，但是一定需要一个返回值

游标：

游标类以指针，游标可以执行多个不相关的操作，如果希望当产生了结果集后，对结果集中的数据进行多种不相关的数据操作

31.存储过程的操作 当它抛出异常的时候 你是如何解决的 用了什么技术

1. 中止当前语句执行，转到 exception 语句块执行。
2. 在异常处理时，捕获相应异常，并执行对应解决方案语句。

32. Oracle 的游标在存储过程里是放在 begin 与 end 的里面还是外面？

Oracle的存储过程跟函数你写没有？项目中用到没有？怎么用的？

1. 放在 begin 与 end 之间。
2. 用作多表连接查询数据返回结果查询。
3. 复杂的业务操作，涉及多表的数据操作的事务控制。
4. 预防 SQL 注入。

33. oracle 中存储过程，游标和函数的区别

1. 游标类似指针，游标可以执行多个不相关的操作如果希望当产生了结果集后，对结果集中的数据进行多种不相关的数据操作
2. 函数可以理解函数是存储过程的一种；函数可以没有参数，但是一定需要一个返回值，存储过程可以没有参数，不需要返回值；两者都可以通过 out 参数返回值，如果需要返回多个
3. 参数则建议使用存储过程；在 SQL 数据操纵语句中只能调用函数而不能调用存储过程

34.解释什么是死锁，如何解决Oracle中的死锁？

简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行

解决办法：

查找出被锁的表

java select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id a.object_id;

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id b.sid order by b.logon_time;

杀进程中的会话

java alter system kill session "sid,serial#";

35.不借助第三方工具，怎样查看 SQL 的执行计划

java set autot on explain plan set statement_id =&item_id for &sql select from table(dbms_xplan.display);

36.如何定位重要（消耗资源多）的 SQL

java select sql_text from v$sql where disk_reads 1000 or (executions 0 and buffer_gets/executions 30000);

37.触发器的作用有哪些？

1. 触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。
2. 触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。
3. 触发器还可以强制执行业务规则
4. 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

38. Oralce 怎样存储文件，能够存储哪些文件？

1. Oracle 能存储 clob、nclob、blob、bfile
2. Clob 可变长度的字符型数据，也就是其他数据库中提到的文本型数据类型
3. Nclob 可变字符类型的数据，不过其存储的是 Unicode 字符集的字符数据
4. Blob 可变长度的二进制数据
5. Bfile 数据库外面存储的可变二进制数据

39.说下如何使用 Oracle 的游标？

1. oracle 中的游标分为显示游标和隐式游标
2. 显示游标是用 cursor...is 命令定义的游标，它可以对查询语句 (select) 返回的多条记录进行处理；隐式游标是在执行插入 (insert) 、删除 (delete) 、修改 (update) 和返回单条记录的查询 (select) 语句时由 PL/SQL 自动定义的。
3. 显式游标的操作：打开游标、操作游标、关闭游标；PL/SQL 隐式地打开 SQL 游标，并在它内部处理 SQL 语句，然后关闭它

40.说下 Oracle 中 function 和 procedure 的区别？

1. 可以理解函数是存储过程的一种
2. 函数可以没有参数，但是一定需要一个返回值，存储过程可以没有参数，不需要返回值
3. 函数 return 返回值没有返回参数模式，存储过程通过 out 参数返回值，如果需要返回多个参数则建议使用存储过程
4. 在 SQL 数据操纵语句中只能调用函数而不能调用存储过程

41.说下 Oracle 的导入导出有几种方式，有何区别？

1. 使用 Oracle 工具 exp/imp
2. 使用 PLSQL 相关工具

方法1. 导入/导出的是二进制的数据
方法2. PLSQL 导入/导出的是 SQL 语句的文本文件

---