Oracle 高频面试题及详细答案（实战版）

Oracle高频面试题及详细答案（实战版）一、Oracle基础概念篇1、简述Oracle数据库的体系结构Oracle核心体系结构主要分为三大模块，日常开发和运维核心关注这几部分：1）数据库实例：由内存结构（SGA、PGA）和后台进程组成，是用户操作数据库的媒介，一个实例只能对应一个数据库。2）内存结构：SGA（系统全局区）是共享内存，包含数据缓冲区、重做日志缓冲区、共享池（存放SQL、PL/SQL、数据字典）；PGA（程序全局区）是私有内存，每个客户端连接独占，存放会话私有数据。3）物理文件：核心三类文件，数据文件（存储真实业务数据）、控制文件（记录数据库结构、文件位置、日志信息）、重做日志文件（记录数据变更，用于故障恢复），辅助有参数文件、归档日志文件等。2、表空间的作用及分类表空间是Oracle逻辑存储单元，是数据文件的容器，所有表、索引、视图等对象都存放在表空间中，核心作用是统一管理存储、权限隔离、数据分层。常用分类：1）永久表空间：存储长期业务数据，如USERS、SYSTEM表空间；2）临时表空间：存放排序、分组、临时表数据，会话结束自动释放，默认TEMP；3）撤销表空间：存放数据修改前的镜像，用于事务回滚、读一致性，默认UNDOTBS。3、CHAR和VARCHAR2的区别1）长度特性：CHAR是固定长度，定义多长就占用多长空间；VARCHAR2是可变长度，占用空间为实际数据长度。2）存储效率：CHAR读取速度快，无字符拼接开销，但空间浪费大；VARCHAR2节省存储空间，读取效率略低。3）使用场景：CHAR适合固定长度字段，如手机号、身份证号、性别；VARCHAR2适合长短不一的字段，如姓名、地址、备注。4）长度限制：旧版本CHAR最大2000，VARCHAR2最大4000；12C版本后两者均可支持32767超长字符。二、SQL查询与语法篇（高频实操）1、WHERE和HAVING的区别1）执行顺序：WHERE在分组（GROUPBY）之前过滤数据；HAVING在分组之后过滤分组结果。2）筛选内容：WHERE针对单行原始数据筛选，不支持聚合函数（SUM、COUNT、MAX等）；HAVING针对分组后的聚合结果筛选，只能使用聚合函数或分组字段。3）效率：优先用WHERE过滤原始数据，减少分组数据量，提升查询效率；HAVING仅用于分组后的二次筛选。示例：统计部门人数大于3人的部门SELECTdept_id,COUNT(*)numFROMempWHEREstatus=1GROUPBYdept_idHAVINGCOUNT(*)>3;2、UNION和UNIONALL的区别1）数据去重：UNION会自动去除重复数据，UNIONALL直接拼接所有结果，不去重。2）执行效率：UNIONALL远快于UNION，因为UNION需要排序、去重，消耗大量资源。3）排序规则：UNION会默认排序，UNIONALL保持原有查询结果顺序。实战建议：业务中确认无重复数据时，一律使用UNIONALL。3、IN、EXISTS、JOIN的适用场景与区别1）IN：适合子查询结果集小的场景，会将子查询结果一次性加载，外层循环匹配；子查询数据量大时效率极低。2）EXISTS：适合子查询结果集大、外层表数据小的场景，核心是判断“是否存在数据”，找到匹配数据立即终止当前循环，无需遍历全部。3）JOIN（内外连接）：适合两张大表关联查询，利用索引优化后，效率远高于IN和EXISTS，是企业关联查询首选。4、分页查询怎么写？Oracle经典分页语法Oracle无LIMIT关键字，通过ROWNUM伪列实现分页，也是面试必考：--通用分页（查询第10-20条数据）SELECT*FROM(SELECTROWNUMrn,t.*FROMemptWHEREROWNUM<=20)WHERErn>10;12C新增简洁分页语法：SELECT*FROMempORDERBYidOFFSET10ROWSFETCHNEXT10ROWSONLY;注意：分页必须配合排序，否则ROWNUM结果随机，数据会错乱。5、DISTINCT去重的原理和注意事项DISTINCT通过排序后去重实现，执行时会对查询字段整体排序，剔除重复行。注意点：1）多个字段去重时，是组合字段整体去重，不是单个字段；2）大数据量下DISTINCT效率极低，优先用GROUPBY替代；3）NULL值会被视为相同值，多个NULL会被合并为一条。三、Oracle常用函数篇1、NVL、NVL2、DECODE、CASEWHEN用法及区别1）NVL(字段,默认值)：字段为NULL则返回默认值，否则返回原值，用于空值替换。2）NVL2(字段,非空值,空值)：字段非空返回第一个值，为空返回第二个值。3）DECODE：Oracle独有简单判断函数，语法简洁，适合固定等值判断。示例：DECODE(status,1,'正常',2,'禁用','未知')4）CASEWHEN：通用多条件判断，支持范围、大于小于、复杂逻辑，兼容性更强，优先推荐使用。2、行列转换怎么实现？1）行转列：使用MAX、SUM结合CASEWHEN或PIVOT函数（11G及以上），适用于统计汇总场景；2）列转行：使用UNIONALL或UNPIVOT函数，适用于拆分字段数据。实战中，低版本数据库优先用CASEWHEN+聚合函数，兼容性最好。3、分析函数用过哪些？简述作用分析函数是Oracle核心高阶函数，区别于普通聚合函数，分组后不合并行数，保留明细数据，常用：1）ROW_NUMBER()：连续序号，不重复、不跳跃，适合分页、取唯一排名；2）RANK()：排名，相同值同序号，后续序号跳跃（1,2,2,4）；3）DENSE_RANK()：密集排名，相同值同序号，后续序号不跳跃（1,2,2,3）；4）PARTITIONBY：分组依据，比GROUPBY更灵活，可多维度分组；5）LAG/LEAD：查询上一行、下一行数据，用于数据差值对比。四、索引篇（面试重点+优化核心）1、Oracle常用索引类型及适用场景1）B树索引：默认索引类型，适合等值、范围查询（><、BETWEEN），日常业务90%场景都用B树索引；2）唯一索引：保证字段唯一性，禁止重复数据，可用于手机号、工号等唯一字段；3）复合索引：多个字段组合索引，遵循最左前缀原则，查询需匹配左侧字段才能生效；4）位图索引：适合低基数字段（如性别、状态），数据重复度高、更新少的场景，更新频繁的字段不建议使用，容易锁表；5）函数索引：针对字段函数运算查询场景，如SUBSTR、UPPER、TRUNC后的查询。2、什么是最左前缀原则？复合索引失效场景复合索引(a,b,c)的生效规则：查询条件必须包含最左侧第一个字段a，索引才会生效，后续b、c字段可缺失。常见失效场景：1）跳过最左字段，直接查询b、c字段；2）索引字段参与运算、使用函数、隐式类型转换；3）使用!=、NOTIN、ISNOTNULL模糊查询；4）OR连接非索引字段；5）数据量过大，数据库判定全表扫描比索引更快，自动放弃索引。3、索引一定能提升查询效率吗？什么时候不建索引？不一定。索引本质是以空间换时间，查询提速的同时，会降低新增、修改、删除效率（需要同步更新索引）。不建议建索引的场景：1）表数据量小（千条以内），全表扫描效率更高；2）字段更新、删除频繁，查询极少；3）低基数字段（如状态、性别），大部分数据重复，索引无意义；4）经常参与函数运算、模糊匹配（%开头）的字段。五、事务与锁机制篇1、Oracle事务四大特性（ACID）1）原子性：事务要么全部执行成功，要么全部回滚，不可部分执行；2）一致性：事务执行前后，数据库数据状态保持合法、一致，不出现脏数据；3）隔离性：多个事务并发执行时，相互独立、互不干扰；4）持久性：事务提交后，数据永久写入数据库，断电、重启不丢失。2、Oracle事务隔离级别，默认级别是什么Oracle支持两种核心隔离级别，区别于MySQL，无读未提交、可重复读：1）读已提交（默认）：事务只能读取其他事务已提交的数据，避免脏读，会出现不可重复读、幻读；2）串行化：最高隔离级别，事务串行执行，无并发问题，无脏读、不可重复读、幻读，但并发性能极低，极少使用。3、常见锁类型及死锁产生原因、解决方式1）常用锁：行级锁（DML操作默认，粒度小、并发高）、表级锁（DDL操作、手动加锁，粒度大、并发低）；2）死锁原因：两个及以上事务，互相持有对方需要的锁，同时等待对方释放锁，循环等待导致事务卡死；3）解决方式：Oracle会自动检测死锁，几秒内自动终止其中一个事务并抛出异常；4）预防方案：统一SQL执行顺序、减少长事务、避免事务中嵌套复杂查询。六、SQL优化实战篇（重点）1、日常SQL优化常用手段1）避免SELECT*，只查询需要的字段，减少数据传输和解析开销；2）优先使用WHERE过滤数据，减少分组、关联、排序的数据量；3）杜绝索引字段运算、隐式类型转换、前置模糊查询（%xxx）；4）大表关联优先用JOIN，替代IN/EXISTS；无重复数据用UNIONALL替代UNION；5）合理建立复合索引，遵循最左前缀原则，避免冗余索引；6）大分页场景避免offset过大，可通过主键筛选优化；7）禁止在索引字段使用ISNULL、NOTIN、!=等失效语法。2、怎么定位慢SQL？1）通过数据库日志、AWR报告、ASH视图筛选耗时较长的SQL；3）查看v$sql视图，监控SQL执行耗时、逻辑读、物理读、执行次数。3、执行计划中需要重点关注的指标1）AccessType：查询类型，ALL代表全表扫描，INDEX代表走索引；2）Rows：预估扫描行数，数值越大开销越高；3）Cost：执行成本，成本越高SQL效率越低；4）Sort：是否存在文件排序，大数据量排序会严重拖慢效率，需优化索引规避排序。七、日常运维与高频问题1、DELETE、TRUNCATE、DROP的区别1）DELETE：DML语句，逐行删除数据，可回滚，不释放表空间，速度慢，可加WHERE条件精准删除；2）TRUNCATE：DDL语句，清空全表数据，不可回滚，立即释放表空间，速度极快，不触发触发器；3）DROP：DDL语句，删除整张表（数据+表结构+索引+约束），不可回滚，彻底释放空间。实战场景：精准删数据用DELETE，清空全表用TRUNCATE，废弃表用DROP。2、Oracle空字符串和NULL的区别Oracle中空字符串''等价于NULL，这是和MySQL最大的区别：1）空字符串和NULL都不占用存储空间；2）无法用=''判断空值，必须用ISNULL/ISNOTNULL；3）索引不会存储NULL值，字段存在NULL会导致索引查询失效，设计表结构时建议给字段默认值。3、快照过旧（ORA-01555）报错原因及解决报错核心：长查询执行时间过长，查询过程中，其他事务频繁修改并提交数据，Or