Oracle面试问题汇总

1:SqL 优化

1：尽量避免使用 select * 查询方式

 因为oracle 在解析过程中 会将*依次转化成所以的列名。

2：减小访问数据库的次数

因为每执行一条sql语句的时候，oracle内部会做许多的事情 如：解析sql ，估算索引的利用效率，绑定变量，读数据块等。因此减小了访问数据库的次

数实际就是减小了oracle的工作量

3：用where前条件查询来代替having后条件查询

因为使用having后条件查询的话，会检索出所以记录之后，才对结果集进行过滤 而使用where前条件查询的话 它首先会通过条件限制了记录的数目，这样减小了开销

4：尽量使用exists代替in 用 not exists 代替 not in

因为在相关子查询中 我们使用 in  或者 not in 的话，它会执行内部的排序与合并

如 查询一条in(1,2,3,1);它会对这里面的数据进行排序

 无论是那种情况in 或者 not in 是相当的低效的 因为 它还有对子查询相关联的表进行全表遍历，我们可以把in  notin 改成exists 或 notExists

如下面的sql语句：

select * from emp where deptno in (select deptno from dept where dnmae="jong")
select * from emp where exists(select 1 from dept where emp.deptno =deptno and dname = 'jong')

5：用Exists代替distinct

因为在相关子查询中distinct 把结果集查出来，内部，还会查找 重复的结果集
6:应该用union代替or

select * from emp where empid = 'df' or empid = '1002' 
而union会将两个结果集进行合并

8:用rowid来删除重复记录数

所用的字段都是相同的，只有rowid不是重复的，所以要删除重复记录时，我们 充分利用rowid来删除重复记录
9：使用DECODE函数来减少处理时间

使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 
10：用TRUNCATE替代DELETE来删除数据

11：使用表的别名
12：尽量多使用COMMIT

 因为comit会释放资源
13：用>=替代>

14：sql语句大写，因为oracle解析sql语句的时候，会将小写转化成大写

15：表的驱动方式也会关系的 sql优化 不同的驱动方式，结果不一样

16：用索引来提高效率

因为索引就相当于书的目录 如果我们看一本书，如果我们要查找相关联的内容，我们就可以通过书的目录快速的定位到多少页来查看书的内容

当然我们在使用索引时要注意以下问题：

a:不能在索引列上使用远算：包括算术远算，逻辑远算 这样是相当的消耗性能的

 

b:避免在索引列上使用NOT：

通常，我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT，他就会停止使用索引转而执行全表扫描。

 

 

2:根据项目中的业务讲一下你使用的存储过程

例如前台界面是“结算操作”业务，根据输入了的结算时间段和结算单位,购买额数，以及相关的控制参数 把这些参数传给后台存储过程，后台存储过程里面可以编制相关的结算处理程序，根据输入的单位、时间、份额等参数，来进行集中处理，这个过程是不需要与前台进行交互的，这里可以处理很复杂的业务，例如结算操作会涉及到多个表如单位基本信息表、单位结算信息表、明细表等等，这个是在存储过程里面集中实现处理，然后把结果返回给前台，前台根据处理结果是否成功，决定是否进行提交（COMMIT）操作。

3:oracle自动增长如何实现

 

oracle不像Mysql样创建主键的时候可以手动设置它的主键为自动增长型,而oracle需要通过

序列来生成主键，创建一个序列一般 
包括：最小值，初始值，步长，和最大值 

4：sql执行原理

user写一条sql，将会对这条sq进行 解析，在解析的过程中，首先会对语法进行分析 ，分析语句的语法是否符合规范，是否正确 然后进行对语义进行分析 检测sql中涉及到的数据库对象是否存在 分析过后，再选择相应的优化器，不同的优化器 执行的效率不同 同时生成的 计划也不同 如果选择 技入规则优化器：oracle默认执行的是计入规则优化器，是从右往左 从下到上执行 

如：selelct * from A,B 那么 oracle 会是从右至左查询，先查询B表，再查询A表。再如果牵涉到相关子查询，它会默认从下往上执行，也就是先从

最里面sql语句一路往外执行。
如果选择的是 计入成本优化器：统计表的数据 计算出使用不同计划的成本 并选择成本最低的一个计划

最后远行执行计划。返回结果集

5：oracle 乐观锁 与 悲观锁 

为了得到最大的性能，一般数据库都有并发机制，不过带来的问题就是数据访问的冲突。为了解决这个问题，大多数数据库用的方法就是数据的锁定。            并发量大 ，控制好锁 ，保证事务的一致性。 锁会影响程序的并发性，与效率 数据的锁定分为两种方法，第一种叫做悲观锁，第二种叫做乐观锁。什么叫悲观锁呢，悲观锁顾名思义，就是对数据的冲突采取一种悲观的态度，也就是说假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住。而乐观锁就是认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息，让用户决定如何去做。

例：在并发的时候，两个用户同时访问同一条sql的时候 比如说 A 用户updatte一条数据的时候，把10更新成20的时候，把commit  利用锁机锁住而

B用户在同时select 这条数据的时候，为10

注：select的时候，获得锁，update的时候， 独立锁

6：数据库表的设计原则

根据数据库3大范式来设计 建的过程中，通过e_r模型  首先把实体找出来，如 建立一个学生管理系统：会根据业务需求和实际情况与团队的讨论，来建立 表，学生表，teacher表，班级，年纪表，呀，课程表，把一个实体建立成一个表 再找出
实体与实体之间的关系，比如说一个班级有多个学生呀  属于 一对多  一个学生可以选修了多们课程，一们课程可以有多个学生来选修 这样的关系属于

多对多的关系，等  这样大致的框架出来了，再根据实际情况来设置实体的属性，如：一个学生表肯定拥有以下字段 主键 id 姓名，年龄等属性
当然在设计的过程中一定要满足数据库3大范式

7：删除重复除了用 DISTINCT 还 用 什么? 同时写出 性能最好的 删除 重复 sql语句？

rowid .

delete from employee e2 where rowid <(

        select max(e1.rowid) from employee e1 where 
        e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and

 

 

                  e1.salary=e2.salary);

 

 

8:oracle 分页？

 

 

SELECT * FROM (

SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

9：oracle rowid  与 rowNum 的区别？

rownum是行数，ROWID是物理编号  

通过唯一rowid实现删除重复记录.在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的

10：oracle 的内置函数？

1:oracle 提供一些内置函数 用于sql的结构化查询 语句 
  sql的结构查询语句分为：1：dml 语句 ：数据操作语言 insert update select delete Merge 等 操作 
                                       2: ddl 语句 ： 数据定义语言 create alter drop delete 等 
                                       3：dcl 语句： 数据 控制语言 grant ,revoke
                                       4：事务控制语句：rollback commit savapoint 

 日期函数：SysDate 得到 系统当前 时间 

分组函数:  count  max min avg 

 

 11:oracle体系结构

完整的Oracle数据库通常由两部分组成：实例和数据库。

 

1、数据库是一系列物理文件的集合（数据文件，控制文件，参数文件 日志文件  当你执行 了 insert update delete 动作的时候，日志文件会记录你的相应信息。
等）；

 

2、实例则是一组Oracle后台进程和内存区域。