Oracle数据库面试题及答案（真实面试版）

Oracle数据库面试题及答案（真实面试版）一、Oracle基础概念（高频入门）1、简单说下Oracle数据库和MySQL的核心区别？答：事务级别：Oracle默认读已提交，无脏读；MySQLInnoDB默认可重复读，会有幻读、不可重复读问题。锁机制：Oracle行锁粒度更细，更新只锁对应行，极少锁表；MySQL行锁依赖索引，索引失效会升级为表锁。存储结构：Oracle是表空间、数据文件、段、区、块结构；MySQL是库、表、数据页结构。适用场景：Oracle主打金融、国企等对数据一致性、稳定性要求高的场景；MySQL互联网业务居多，轻量化、部署简单、开源免费。其他差异：Oracle有序列、同义词、物化视图；MySQL没有序列，自增靠主键自增。2、Oracle中表空间是什么？有什么作用？答：表空间是Oracle逻辑存储单元，是数据库和数据文件的中间层，一个表空间可以对应多个物理数据文件，一张表的数据可以存在一个或多个数据文件中。主要作用：统一管理数据、方便扩容、可以针对不同业务分配独立表空间，方便备份、迁移和权限管控，避免业务数据混杂。3、Oracle常用的系统用户和默认权限？答：SYS：最高权限用户，拥有数据字典所有对象，核心系统用户，一般不用于业务操作。SYSTEM：管理员用户，权限仅次于SYS，用于日常数据库管理、用户创建、权限分配。SCOTT：测试用户，默认自带EMP、DEPT测试表，日常学习测试使用。二、SQL查询基础（面试必问）1、WHERE和HAVING的区别？答：执行顺序：WHERE在分组（GROUPBY）之前过滤数据；HAVING在分组之后过滤分组结果。过滤内容：WHERE过滤行数据，不能用聚合函数（SUM、COUNT、MAX等）；HAVING专门过滤聚合后的结果，只能用在分组后，支持聚合函数。简单总结：先筛行用WHERE，分组筛结果用HAVING。2、UNION和UNIONALL的区别？答：UNION：合并查询结果后去重+排序，效率低。UNIONALL：直接拼接结果，不去重、不排序，效率远高于UNION。业务中如果确定数据无重复，优先用UNIONALL。3、DISTINCT去重原理，有什么性能问题？答：DISTINCT会对查询结果进行排序、比对去重，数据量大时会产生临时表，占用内存和磁盘IO，性能较差。日常开发中，能通过业务逻辑过滤重复的，尽量不用DISTINCT。4、内连接、左连接、右连接、全连接的使用场景？答：内连接（INNERJOIN）：只查两张表匹配成功的数据。左连接（LEFTJOIN）：以左表为主，左表数据全部展示，右表匹配不到显示空值。右连接（RIGHTJOIN）：以右表为主，右表数据全部展示，左表匹配不到显示空值。全连接（FULLJOIN）：展示两张表所有数据，匹配不到的字段为空。三、Oracle常用函数1、NVL、NVL2、DECODE函数用法答：NVL(字段,默认值)：字段为空则返回默认值，不为空返回原值。常用于处理空值导致的计算异常。NVL2(字段,非空值,空值)：字段非空返回第一个值，为空返回第二个值。DECODE(字段,值1,结果1,值2,结果2,默认值)：Oracle专属条件判断函数，相当于简洁版的IF-ELSE，常用于状态码翻译。2、ROW_NUMBER、RANK、DENSE_RANK排序函数区别？答：三者都是分组排序函数（必须结合OVER(PARTITIONBY分组字段ORDERBY排序字段)使用）ROW_NUMBER：连续序号，1、2、3、4，即使数值相同序号也不重复、不跳跃。适合分页、取第一条数据。RANK：跳跃排序，相同数值序号相同，后续序号跳过（1、2、2、4）。适合排名场景。DENSE_RANK：连续排序，相同数值序号相同，后续序号不跳跃（1、2、2、3）。3、TRUNC函数用法答：TRUNC可以截断数字和日期，不四舍五入。数字：TRUNC(123.456,2)=123.45，直接保留两位小数。日期：TRUNC(SYSDATE)截取当天零点，TRUNC(SYSDATE,'MONTH')截取当月第一天。四、索引相关（面试重点）1、Oracle常用索引类型及适用场景？答：B树索引：默认索引，适合等值查询、范围查询，日常业务最常用。位图索引：适合字段值重复率极高、值固定的字段（如性别、状态），不适合频繁更新的字段，更新容易锁表。唯一索引：保证字段唯一，禁止重复数据，主键默认自带唯一索引。组合索引：多个字段联合索引，遵循最左前缀原则。函数索引：对字段函数运算后建立索引，比如UPPER(name)，解决查询中函数导致索引失效问题。2、什么情况下索引会失效？（高频）答：实际工作中最常见的失效场景：索引字段使用函数、运算：比如WHERESUBSTR(name,1,2)='XX'、WHEREage+10>20。隐式类型转换：字段是字符串，查询传数字，自动转换导致失效。使用!=、<>、NOTIN、ISNOTNULL（索引对空值不存储）。组合索引不遵循最左前缀原则，跳过前导列查询。OR连接非索引字段，会导致整体索引失效。数据量过少、全表扫描成本低于索引扫描，Oracle自动放弃索引。3、索引是不是越多越好？为什么？答：不是。索引可以提升查询效率，但会降低增删改效率。每次新增、修改、删除数据时，数据库都需要同步更新索引结构，索引过多会增加数据库IO压力、占用存储空间。一般查询多、更新少的表适合建索引，频繁更新的表严控索引数量。五、事务与锁机制（核心难点）1、Oracle事务四大特性ACID？答：原子性（A）：事务要么全部成功，要么全部回滚，不可部分执行。一致性（C）：事务执行前后，数据整体状态合法、一致，不破坏数据约束。隔离性（I）：多个事务并发执行互不干扰，互相隔离。持久性（D）：事务提交后，数据永久落地，宕机也不会丢失。2、Oracle事务隔离级别？答：Oracle只支持两种隔离级别：读已提交（默认）：只能读到其他事务提交后的数据，解决脏读，存在不可重复读、幻读。串行化：最高隔离级别，事务串行执行，无并发问题，性能最差。3、Oracle常见锁类型，行锁和表锁区别？答：行锁：DML操作（UPDATE、INSERT、DELETE）默认行锁，只锁定修改的行，其他行可正常操作，并发性能高，事务提交后自动释放。表锁：锁定整张表，其他事务无法修改表数据，一般手动加锁或特殊场景触发（索引失效、DDL操作），并发极差。4、死锁产生的条件，如何避免？答：死锁四个必要条件：互斥、请求保持、不可剥夺、循环等待。避免方式：统一业务更新顺序、缩短事务执行时间、尽量避免长事务、批量更新拆分数据。Oracle会自动检测死锁并终止其中一个事务。六、数据库优化（面试加分项）1、日常SQL优化的基本思路？答：优先看执行计划，判断是否走全表扫描、索引失效。禁止使用SELECT*，只查需要的字段，减少IO。大表查询避免NOTIN、<>、模糊查询前置%。合理建立索引，避免冗余索引、无效索引。大表分页、统计尽量用批量查询，避免全表遍历。拆分长SQL、复杂关联，减少多表嵌套连接。2、大表优化方案有哪些？答：分区表：按时间、ID范围分区，查询只扫描对应分区，减少扫描数据量。分库分表：数据量千万级以上，水平/垂直拆分。冷热数据分离：历史冷数据归档，业务只查热数据。建立合适索引、函数索引，优化查询路径。使用物化视图预计算统计数据，减少实时计算压力。3、什么是物化视图？和普通视图的区别？答：普通视图：虚拟表，不存数据，每次查询实时执行SQL，数据实时最新，无存储压力，查询慢。物化视图：真实存储查询结果数据，会落地到磁盘，可以定时刷新，查询速度极快，适合报表、统计场景，缺点是数据有延迟，占用存储空间。七、实战场景面试题（手写SQL）1、查询员工表中每个部门薪资最高的员工信息答案思路：用分组排序函数ROW_NUMBER分组取第一SELECT*FROM(

SELECTemp.*,ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsalDESC)rn

FROMemp

)tWHEREt.rn=1;2、去重保留最新一条数据（重复数据删除）场景：表中存在重复手机号/账号，保留创建时间最新的数据DELETEFROMuser_infot1

WHEREt1.create_time<(

SELECTMAX(create_time)FROMuser_infot2WHEREt1.phone=t2.phone

);3、Oracle分页SQL（经典）SELECT*FROM(

SELECTA.*,ROWNUMrnFROM(

SELECT字段FROM表名WHERE条件ORDERBY排序字段

)AWHEREROWNUM<=结束行

)WHERErn>起始行;八、高频冷门简答（压轴面试）1、ROWNUM和ROWID的区别？答：ROWID：物理地址，每条数据唯一的物理标识，固定不变，查询速度最快，可用于精准删重。ROWNUM：查询结果的虚拟行号，动态生成，查询结果变了行号就变，主要用于分页。2、Oracle闪回查询了解吗？答：闪回可以