Oracle开发面试题及详细答案（实战版）

Oracle开发面试题及详细答案（实战版）一、Oracle基础概念篇（高频基础题）1、简述Oracle数据库中表空间、数据文件、用户、schema的关系参考答案：这四者是Oracle存储与权限管理的核心层级关系，由底层存储到上层逻辑对象逐层对应：1.数据文件是Oracle的物理存储载体，是磁盘上真实存在的文件，所有数据库数据、索引、日志最终都存储在数据文件中，一个数据文件仅归属一个表空间。2.表空间是逻辑存储单元，用来统一管理多个数据文件，数据库所有对象（表、索引、视图等）都创建在表空间中，一个表空间可包含多个数据文件。3.用户是数据库的访问主体，拥有独立的账号、密码和登录权限，用于连接和操作数据库。4.schema是用户的对象集合，一个用户对应唯一的schema，schema名称和用户名完全一致。用户创建的所有表、视图、存储过程等对象，都归属于该用户的schema。简单总结：物理上数据文件组成表空间，逻辑上用户（schema）拥有数据库对象，对象存储在指定表空间中。2、Oracle中CHAR和VARCHAR2的区别？实际开发如何选择？参考答案：核心区别：1.存储方式：CHAR是固定长度字符类型，定义多长就占用多少存储空间，不足长度会自动补空格；VARCHAR2是可变长度类型，仅存储实际输入的字符，不补空格。2.长度限制：CHAR最大支持2000字节；VARCHAR2最大支持4000字节（11g及以前），12c及以上支持32767字节。3.查询效率：CHAR查询效率略高，无需动态计算长度；VARCHAR2更节省存储空间，适合大量可变数据。4.尾部空格：CHAR字段查询时会自动忽略尾部空格，VARCHAR2严格匹配空格。开发选择原则：固定长度、长度较短、查询频繁的字段用CHAR，比如性别、手机号、身份证号、状态码；长度不固定、内容长短差异大的字段用VARCHAR2，比如姓名、备注、地址、文本描述。3、NULL和空字符串''的区别是什么？参考答案：1.本质不同：NULL代表未知、无值，是未赋值状态；空字符串''是确定的空字符，属于有值状态。2.存储不同：NULL不占用存储空间；空字符串会占用极小的存储空间。3.判断方式不同：NULL只能用ISNULL、ISNOTNULL判断，不能用=、!=比较；空字符串可以直接用=''、!=''判断。4.函数处理不同：NVL(NULL,'默认值')会返回默认值，NVL('','默认值')不会替换，直接返回空字符串。5.索引影响：NULL值会被索引忽略，空字符串可以被索引检索到。二、SQL查询进阶篇（核心实操题）1、DISTINCT和GROUPBY去重的区别？各自适用场景参考答案：区别：1.执行逻辑：DISTINCT是查询结果返回后，对全部字段整体去重；GROUPBY是先按指定字段分组，再返回分组后的唯一数据，可配合聚合函数使用。2.功能范围：DISTINCT仅支持单纯去重，无法做统计计算；GROUPBY支持分组后求和、计数、最大值、最小值等聚合操作。3.性能差异：单字段简单去重，DISTINCT效率更高；多字段去重且需要统计数据时，GROUPBY性能更优，且可规避DISTINCT多字段排序的性能损耗。适用场景：仅需要剔除重复数据、无需统计，用DISTINCT；需要根据字段分组，同时统计每组数据的数量、合计、极值等，用GROUPBY。2、Oracle中UNION和UNIONALL的区别参考答案：1.去重差异：UNION会自动去除两个结果集中的重复数据，且对结果进行默认排序；UNIONALL直接拼接两个结果集，不去重、不排序。2.性能差异：UNION需要执行排序、去重逻辑，资源消耗大、速度慢；UNIONALL无额外运算，性能远高于UNION。3.适用场景：确定数据无重复、追求查询效率时，优先用UNIONALL；需要自动剔除重复数据时，使用UNION。开发规范：业务中绝大多数场景数据无重复，统一优先使用UNIONALL。3、WHERE和HAVING的区别，为什么分组筛选优先用HAVING参考答案：1.执行时机不同：WHERE是分组之前筛选原始数据，过滤不符合条件的单条数据；HAVING是分组之后筛选分组结果，过滤不符合条件的分组数据。2.支持语法不同：WHERE后不能使用聚合函数（SUM、COUNT、MAX等）；HAVING专门用于聚合结果的筛选，支持所有聚合函数。3.索引利用不同：WHERE筛选原始数据可命中索引，提升查询效率；HAVING针对分组后的结果集过滤，无法使用索引。开发原则：能在WHERE中过滤的条件绝不放到HAVING，尽可能提前过滤数据，减少分组计算的数据量，提升SQL性能。4、写出分页查询SQL（Oracle经典分页，含高效写法）参考答案：Oracle无LIMIT关键字，分页依赖ROWNUM伪列，分为普通分页和高效分页（大数据量推荐）。普通分页（适合小数据量）：PlainText

SELECT*FROM(

SELECTROWNUMrn,t.*FROM表名tWHEREROWNUM<=结束行号

)WHERErn>起始行号;高效分页（大数据量，先查主键再关联，避免全表扫描）：PlainText

SELECTt.*FROM表名tINNERJOIN(

SELECTidFROM表名WHEREROWNUM<=结束行号MINUS

SELECTidFROM表名WHEREROWNUM<=起始行号

)tempONt.id=temp.idORDERBYt.id;备注：12c及以上版本支持FETCH分页，语法更简洁：PlainText

SELECT*FROM表名ORDERBYidOFFSET起始行数ROWSFETCHNEXT每页行数ROWSONLY;三、Oracle函数与语法篇1、NVL、NVL2、DECODE、CASEWHEN的用法及区别参考答案：1.NVL(expr1,expr2)：单值空值替换，expr1为NULL时返回expr2，否则返回expr1，仅处理空值场景。2.NVL2(expr1,expr2,expr3)：双分支判断，expr1不为NULL返回expr2，为NULL返回expr3，比NVL多一个分支逻辑。3.DECODE：Oracle独有等值判断函数，语法简洁，仅支持等值匹配，不支持区间、大于小于判断。示例：DECODE(状态,'1','启用','0','禁用','未知')。4.CASEWHEN：通用多条件判断，支持等值、区间、逻辑运算，兼容性更强，可读性更好，是开发首选。选型原则：简单空值替换用NVL/NVL2，简单等值多分支用DECODE，复杂条件、区间判断统一用CASEWHEN。2、分析函数ROW_NUMBER、RANK、DENSE_RANK的区别参考答案：三者均用于分组排序编号，核心差异在重复值的编号规则：1.ROW_NUMBER()：连续不重复编号，无论值是否重复，编号依次递增（1、2、3、4），适合分页、取第一条数据。2.RANK()：跳跃式编号，重复值编号相同，后续编号跳过空缺（1、2、2、4），适合排名场景（如成绩排名，同分同名次）。3.DENSE_RANK()：连续式编号，重复值编号相同，后续编号连续不跳跃（1、2、2、3），适合密集排名场景。常用场景：取每组最新数据优先用ROW_NUMBER()。3、简述Oracle递归查询的用法（STARTWITH...CONNECTBY）参考答案：递归查询用于查询树形结构数据，比如部门层级、菜单层级、上下级关系。核心语法：PlainText

SELECT*FROM表名

STARTWITH根节点条件

CONNECTBYPRIOR主键=父级字段;关键说明：1.STARTWITH：指定树形查询的起始根节点；2.CONNECTBYPRIOR：指定父子关联关系；3.PRIOR在主键前：向下递归（查询所有子节点）；PRIOR在父级字段前：向上递归（查询所有父节点）。四、索引与性能调优篇（面试重点）1、Oracle常用索引类型及适用场景参考答案：1.B树索引（默认索引）：最常用，平衡二叉树结构，适合等值查询、范围查询（>、<、BETWEEN），普通字段优先创建B树索引。2.唯一索引：基于B树实现，强制字段值唯一，无重复数据，适用于手机号、身份证、唯一编码等字段。3.组合索引：多个字段联合创建索引，遵循最左前缀原则，查询条件匹配左侧字段才可命中索引。4.函数索引：针对字段使用函数查询的场景，比如WHEREUPPER(name)='ABC'，普通索引失效，需创建函数索引。5.位图索引：适合低基数列（字段值重复率极高，如性别、状态），读写效率高，但不适合频繁更新的表，并发更新易锁表。6.全文索引：用于模糊查询、文本检索，解决普通like'%值%'无法命中索引的问题。2、什么是最左前缀原则？组合索引如何设计更合理参考答案：最左前缀原则：组合索引(a,b,c)，查询条件必须包含最左侧的a字段，索引才会生效；仅查询b、c或b+c，无法命中索引。组合索引设计原则：1.高频查询字段放最左侧，低频字段后置；2.等值查询字段在前，范围查询字段在后；3.区分度高的字段在前，区分度低的字段在后；4.避免过度创建组合索引，防止影响新增、修改、删除性能。3、常见索引失效场景（面试必背）参考答案：1.对索引字段使用函数、运算：如WHERESUBSTR(name,1,1)='张'、WHEREage+10>30；2.隐式类型转换：字段为字符型，查询传数值（WHEREphone；3.使用!=、<>、NOTIN、ISNOTNULL（索引字段大量空值时失效）；4.LIKE前置模糊查询：LIKE'%测试'、LIKE'%测试%'；5.组合索引不满足最左前缀原则；6.表数据量过小，Oracle优化器认为全表扫描比索引查询更快，自动放弃索引；7.频繁增删改导致索引碎片过多，索引失效。4、如何定位慢SQL？简单说下SQL调优思路参考答案：慢SQL定位方式：1.通过ExplainPlan执行计划，查看SQL的扫描方式、索引命中、连接方式；2.查询系统视图V$SQL、V$SQLAREA，根据执行耗时、逻辑读取、物理读取筛选慢SQL；3.查看数据库AWR、ASH报告，定位高频慢查询。通用调优思路：1.优先优化WHERE条件，提前过滤数据，减少关联、分组、排序的数据量；2.合理创建索引，规避索引失效场景，优化组合索引顺序；3.避免SELECT*，只查询业务需要的字段，减少数据传输；4.大表查询优先用UNIONALL代替UNION，避免不必要的排序去重；5.大数量分页、统计查询，优化分页逻辑，避免全表扫描；6.定期整理索引碎片、统计表信息，保证优化器判断准确。五、事务、锁与并发篇1、Oracle事务四大特性及事务隔离级别参考答案：事务四大特性（ACID）：1.原子性：事务内所有操作要么全部成功提交，要么全部回滚，不可部分执行；2.一致性：事务执行前后，数据库数据完整性、约束规则保持一致；3.隔离性：多个事务并发执行时，相互隔离、互不干扰；4.持久性：事务提交成功后，数据永久落地，宕机、重启不会丢失。Oracle支持的隔离级别：1.读已提交（默认）：只能读取其他事务已提交的数据，可避免脏读，存在不可重复读、幻读；2.可重复读：保证同一事务内多次读取同一数据结果一致；3.串行化：最高隔离级别，事务串行执行，无并发问题，性能最低。2、Oracle常见锁类型及锁等待解决方案参考答案：常见锁类型：1.行锁：修改、删除单条数据触发，仅锁定当前行，并发度高，阻塞范围小；2.表锁：手动加锁、DDL操作、行锁升级触发，锁定整张表，阻塞所有增删改操作；3.共享锁、排他锁：查询加共享锁，读写不冲突；修改加排他锁，读写互斥。锁等待解决思路：1.通过V$LOCK、V$SESSION视图查询锁等待会话、阻塞会话；2.定位异常长事务，手动kill阻塞会话；3.优化业务逻辑，缩短事务执行时间，避免长事务；4.合理使用行级锁，避免手动加表锁，减少锁冲突。六、存储过程与实操问题篇1、存储过程和普通SQL的优缺点，开发使用场景参考答案：存储过程优点：1.预编译执行，多次调用无需重复解析，执行效率高；2.减少网络传输，复杂逻辑数据库端执行，无需频繁传输SQL语句；3.逻辑封装性强，统一业务规则，便于维护。存储过程缺点：1.可读性、移植性差，仅适配Oracle数据库，跨库迁移成本高；2.调试难度大，版本管理、线上问题排查不如代码SQL便捷；3.占用数据库资源，大量复杂存储过程会加重数据库压力。使用场景：高频执行、逻辑固定、批量处理数据的场景用存储过程；常规业务查询、新增修改，优先在代码中写SQL。2、批量导入大批量数据，Oracle有哪些高效方案参考答案：1.批量INSERT：使用INSERTALL批量插入，单次语句插入多条数据，减少事务提交次数；2.FORALL批量绑定：PL/SQL中替代普通FOR循环，批量执行DML，大幅提升批量