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

Oracle数据库高频面试题及详细答案（实战版）一、数据库基础概念1、简述Oracle数据库的体系结构Oracle整体分为实例和数据库两大部分，是典型的单实例多进程架构。1）Oracle实例：由内存结构和后台进程组成，是数据库运行的核心。内存结构包含SGA（系统全局区）和PGA（程序全局区）；后台进程核心有DBWR（数据写入进程）、LGWR（日志写入进程）、CKPT（检查点进程）、SMON（系统监控进程）、PMON（进程监控进程）。2）Oracle数据库：由物理文件构成，主要包含数据文件、控制文件、重做日志文件、参数文件、归档日志文件等，永久存储数据。简单来说，实例是运行时的内存和进程，数据库是落地的物理文件，实例挂载并操作数据库文件。2、SGA和PGA的区别1）SGA：系统全局区，是所有用户进程和后台进程共享的内存区域，数据库启动时分配，关闭时释放。主要包含数据缓冲区、日志缓冲区、共享池、Java池、大池等，用于缓存数据、SQL语句、执行计划、日志信息等公共资源。2）PGA：程序全局区，是单个进程私有的内存区域，每个客户端连接、每个后台进程都会单独分配一个PGA，进程结束后自动释放。主要存储会话私有数据、排序数据、游标状态、堆栈信息等，进程之间互不干扰。3、Oracle事务的四大特性及默认隔离级别事务四大特性（ACID）：原子性：事务要么全部执行成功，要么全部回滚，不可部分执行。一致性：事务执行前后，数据库数据完整性约束不被破坏，数据状态合法。隔离性：多个事务并发执行时，相互独立、互不干扰。持久性：事务提交后，数据修改永久生效，即使宕机也不会丢失。Oracle默认隔离级别：读已提交（READCOMMITTED）。该级别可以避免脏读，允许不可重复读和幻读，是兼顾并发性能和数据准确性的最优选择，也是企业生产环境通用级别。同时Oracle支持串行化、可重复读隔离级别，一般仅特殊业务使用。4、Oracle事务的提交和回滚原理事务执行DML操作时，不会直接修改数据文件，而是先写入undo回滚段记录原始数据，同时在redo重做日志记录修改操作。提交（commit）：触发LGWR进程，将redo日志缓冲区数据写入磁盘日志文件，标记事务完成，undo段数据可覆盖，数据修改正式生效。回滚（rollback）：读取undo段中存储的原始数据，将数据恢复到事务执行前的状态，清空本次事务的修改记录。二、SQL查询与语法高频题1、where、groupby、having、orderby的执行顺序执行优先级：where→groupby→聚合函数→having→orderby解析：where是分组前过滤，筛选原始数据，不能使用聚合函数；groupby对筛选后的数据分组；聚合函数统计分组数据；having是分组后过滤，专门筛选分组后的统计结果，可以使用聚合函数；最后orderby对最终结果排序。示例：统计部门人数大于3的部门selectdept_id,count(*)fromempwheresalary>3000groupbydept_idhavingcount(*)>3orderbycount(*)desc;2、union和unionall的区别1）去重差异：union会自动去除两个结果集的重复数据，unionall直接拼接所有结果，不去重。2）性能差异：union需要排序去重，资源消耗大、速度慢；unionall无需排序，性能远高于union。3）排序规则：union默认自带排序，unionall完全按照查询结果顺序展示。生产环境中，确认数据无重复时，优先使用unionall。3、in和exists的区别及使用场景核心原理：in是外表匹配内表，exists是内表匹配外表，本质是驱动表不同。1）in：先查询子查询所有结果，再遍历外表匹配数据。子查询数据量小、外表数据量大时效率高；子查询数据量大时，匹配效率极低。2）exists：遍历外表，逐条校验子查询是否存在匹配数据。外表数据量小、子查询表数据量大时效率极高。总结：小表查大表用in，大表查小表用exists。4、null值的查询注意事项Oracle中null代表未知、空值，不等于0、空格、空字符串，且不参与等值判断。1）不能使用=null、!=null查询，结果永远为空；必须使用isnull、isnotnull。2）任何数值和null运算，结果均为null。3）groupby会将所有null值归为同一组。4）count(*)统计所有行数，count(字段)会忽略字段为null的数据。三、Oracle常用函数1、decode和casewhen的区别1）decode：Oracle独有函数，语法简洁，适合等值判断，不支持区间、大于小于判断。示例：decode(sex,'1','男','2','女','未知')2）casewhen：标准SQL语法，通用性强，支持等值、区间、多条件复杂判断，逻辑更灵活，可读性更好。生产简单等值转换用decode，复杂多条件判断统一用casewhen。2、rownum和rowid的区别1）rowid：物理伪列，每条数据唯一的物理地址，固定不变，对应数据在数据文件中的存储位置，查询速度极快，可用于精准定位、删除重复数据。2）rownum：逻辑伪列，是查询结果的行号，动态生成，查询结果变化时rownum会重新排序。主要用于分页查询、限制返回行数。重点：rownum只能使用<、<=，直接使用>、=查询无结果，分页需要嵌套子查询。3、Oracle分页查询写法（经典面试）Oracle无limit关键字，通过rownum嵌套分页，查询第10-20条数据示例：select*from(selectt.*,rownumrnfromemptwhererownum<=20)wherern>10;原理：内层先查询前20条数据并生成行号，外层过滤掉前10条，实现分页。数据量大时可结合排序和索引优化。四、索引核心知识点1、Oracle常用索引类型及适用场景1）B树索引：默认索引类型，结构平衡，适合等值查询、范围查询（><、between），日常业务90%场景均使用B树索引。2）唯一索引：约束字段唯一，禁止重复值，适用于手机号、身份证、账号等唯一字段。3）复合索引：多个字段联合索引，遵循最左前缀原则，查询条件匹配左侧字段时索引生效。4）位图索引：适合字段基数低、重复值多的字段（如性别、状态），读写性能差，仅适用于静态统计表，禁止用于频繁DML的业务表。5）函数索引：针对字段函数运算查询场景，如substr、upper、to_char，解决函数导致的索引失效问题。2、索引失效的常见场景（高频考点）1）违反最左前缀原则，复合索引跳过前置字段查询。2）索引字段使用函数、运算、类型转换，如wheresubstr(name,1,2)='张'、whereage+1>20。3）使用!=、<>、notin、isnull、isnotnull查询索引字段。4）字段隐式类型转换，字符串字段传数值、数值字段传字符串。5）or左右两侧字段均无索引，会导致索引失效。6）数据表数据量小、全表扫描成本低于索引扫描，Oracle自动放弃索引。7）索引碎片化严重、统计信息过期，导致执行计划错误。3、索引一定能提升查询速度吗？不一定，索引仅能优化查询，会降低新增、修改、删除性能。1）小表无需建索引，全表扫描更快。2）频繁DML操作的表，索引过多会增加日志写入、索引维护成本，拖慢业务速度。3）重复值极高的字段建索引，命中率极低，基本不会生效。五、数据库优化1、SQL优化的通用思路1）优先优化慢查询，通过explainplan查看执行计划，定位全表扫描、索引失效、笛卡尔积等问题。2）避免select*，只查询业务所需字段，减少数据传输和内存占用。3）合理使用索引，遵循最左前缀原则，避免索引失效场景。4）大表查询优先过滤数据，where条件尽早筛选，减少后续分组、排序、连接的数据量。5）大表分页、统计避免全表扫描，合理使用分区表、索引、物化视图。6）优先使用exists代替in，unionall代替union，提升执行效率。2、分区表的作用及常用分区方式分区表核心作用：将超大表按规则拆分为多个子分区，物理上分开存储，逻辑上仍是一张表。可以减少单次查询扫描数据量、提升查询效率，同时方便数据归档、清理、维护。常用分区方式：1）范围分区：按时间、数值范围分区，最常用，适合订单、日志、流水表。2）列表分区：按固定字段值分区，适合地区、业务类型等固定枚举值字段。3）哈希分区：按哈希值均匀分布数据，适合数据均匀拆分、无明确规则的大表。3、如何定位Oracle慢SQL1）查看数据库慢查询日志，筛选执行耗时久、逻辑读高的SQL。2）通过v$sql、v$sqlarea视图，查询执行次数多、耗时长、资源占用高的SQL。3）使用explainplanfor分析SQL执行计划，查看是否存在全表扫描、排序、低效连接。4）通过AWR、ASH报告分析时段内的TOP慢SQL，定位数据库性能瓶颈。六、锁与并发问题1、Oracle常见锁类型1）行级锁：DML操作（insert、update、delete）自动触发，仅锁定修改的单行数据，粒度最小、并发最高，不会阻塞其他行的操作。2）表级锁：锁定整张数据表，粒度大、并发低，常见于DDL操作、手动加锁，会阻塞所有表数据操作。3）共享锁：读锁，多个事务可同时加共享锁，互不阻塞，读不阻塞读。4）排他锁：写锁，独占锁，一个事务加排他锁后，其他事务无法加任何锁，写阻塞读写。2、死锁产生的原因及解决办法产生原因：两个或多个事务互相持有对方需要的锁，同时等待对方释放锁，循环等待，导致事务卡死。常见场景：多个事务交叉更新同一张表的不同行，或更新多张表的顺序不一致。解决办法：1）统一业务更新表、更新数据的顺序，从根源避免循环等待。2）缩短事务执行时间，操作完成立即提交，减少锁持有时间。3）避免长事务、大事务，拆分复杂SQL。4）出现死锁后，Oracle会自动检测并终止其中一个事务，业务侧捕获异常重试即可。七、运维与故障基础1、重做日志（redo）和回滚日志（undo）的作用redo日志：保障事务持久性。记录所有数据修改操作，数据库宕机重启后，通过redo日志重做已提交但未写入数据文件的操作，恢复数据，防止数据丢失。undo日志：保障事务原子性和读一致性。存储数据修改前的原始值，用于事务回滚、实现读已提交隔离级别，保证查询时读取稳定数据，避免脏读。2、数据库启动的三个阶段1）nomount阶段：启动实例，分配SGA、启动后台进程，读取参数文件，不关联数据库物理文件。2）mount阶段：读取控制文件，关联数据库物理文件，确认数据文件、日志文件信息，可进行数据库备份、恢复操作。3）ope