SQL面试宝典

# 数据库面试常见题

# 一、数据库基础类

# 1. 数据抽象包括哪几个？

答：物理抽象、概念抽象、视图级抽象；内模式、模式、外模式。

# 2. SQL语言包括数据哪几个方面？

答：数据定义、数据操纵（Data Manipulation）、数据控制（Data Control）

数据定义： Create Table, Alter Table, Drop Table, Create/Drop Index
数据操纵： Select, Insert, Update, Delete
数据控制： Grant, Revoke

# 3. SQL常用命令

-- 建表
CREATE TABLE Student (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL
);

-- 建视图
CREATE VIEW view_name AS
SELECT * FROM Table_name;

-- 建索引
CREATE UNIQUE INDEX index_name ON TableName(col_name);

-- 插入
INSERT INTO tablename (column1, column2, ...) 
VALUES (exp1, exp2, ...);

-- 插入视图（实际影响基表）
INSERT INTO Viewname (column1, column2, ...) 
VALUES (exp1, exp2, ...);

-- 更新数据
UPDATE tablename SET name='zang 3' WHERE condition;

-- 删除
DELETE FROM Tablename WHERE condition;

-- 授权
GRANT (Select, delete, ...) ON (对象) TO USER_NAME [WITH GRANT OPTION];

-- 撤权
REVOKE (权限表) ON (对象) FROM USER_NAME [WITH REVOKE OPTION];

**列出工作人员及其领导的名字：**
```sql
SELECT E.NAME, S.NAME 
FROM EMPLOYEE E, EMPLOYEE S 
WHERE E.SUPERName = S.Name;

# 4. 视图

定义： 视图是从一个或多个基本表（或其他视图）导出的虚拟表，其内容由查询定义。视图不存储实际数据，只保存查询定义（SELECT语句）。

主要作用：

简化查询： 将复杂的SQL查询封装成视图，用户只需查询视图即可获得所需数据，无需编写复杂的多表连接
数据安全性： 可以限制用户只能访问视图中暴露的特定列和行，隐藏敏感数据（如薪资、密码等字段）
逻辑独立性： 当基本表结构改变时（如增加列、修改列名），可以通过调整视图定义保持应用程序查询语句不变
数据定制： 不同用户可以看到同一数据的不同子集，实现数据的个性化展示

特点：

视图是虚拟表，不占用物理存储空间（仅保存定义）
对视图的更新操作（INSERT/UPDATE/DELETE）会受到限制，通常只能更新基于单表且包含主键的简单视图
视图可以嵌套定义（视图基于视图）

# 5. 完整性约束

实体完整性、参照完整性、用户定义完整性。

# 6. 第三范式

1NF： 每个属性是不可分的。
2NF： 若关系R是1NF，且每个非主属性都完全函数依赖于R的键。
- 例：SLC(SID#, CourceID#, SNAME, Grade) 则不是2NF（存在部分依赖）。
3NF： 若R是2NF，且它的任何非键属性都不传递依赖于任何候选键。

# 7. ER（实体/联系）模型

定义： ER模型（Entity-Relationship Model，实体-联系模型）是数据库概念设计阶段使用的主要工具，用于描述现实世界的概念模型。

核心概念：

实体（Entity）： 客观存在并可相互区别的事物，如学生、课程、部门。在数据库中通常对应一张表。
属性（Attribute）： 实体的特征描述，如学生的学号、姓名、年龄。在数据库中对应表的列。
联系（Relationship）： 实体之间的关联，如"学生选修课程"、"员工属于部门"。

联系的类型：

一对一（1:1）： 如一个班级只有一个班长，一个班长只属于一个班级
一对多（1:N）： 如一个部门有多个员工，一个员工只属于一个部门
多对多（M:N）： 如一个学生可以选修多门课程，一门课程可以被多个学生选修（需要引入中间表/关联实体转换为两个1:N关系）

ER图符号：

矩形：表示实体
椭圆：表示属性
菱形：表示联系
连线：连接实体与属性、实体与联系，并标注联系类型（1:1、1:N、M:N）

# 8. 索引作用

定义： 索引是数据库表中一种特殊的查询表，它保存了表中一列或多列值的排序副本，并包含指向实际数据行的指针（如ROWID）。

主要作用：

加速数据检索： 类似书籍的目录，通过索引可以快速定位到满足查询条件的行，避免全表扫描（Full Table Scan），显著减少磁盘I/O操作
保证数据唯一性： 唯一索引（UNIQUE INDEX）确保索引列的值不重复，实现实体完整性约束
加速表连接： 在外键列上创建索引可以显著提高JOIN操作的速度
加速排序和分组： 如果查询的ORDER BY或GROUP BY字段已建立索引，数据库可以直接利用索引的有序性，避免额外的排序操作

索引的工作原理： 当执行查询时，数据库优化器会评估是否使用索引：

如果WHERE条件包含索引列，数据库会先在索引结构中查找匹配的值
通过索引项中的指针（如ROWID）直接访问磁盘上的实际数据行
对于范围查询（如BETWEEN、>、<），B-Tree索引特别有效，因为数据是有序存储的

注意事项（缺点）：

降低写操作性能： INSERT、UPDATE、DELETE操作时需要同时维护索引结构（更新索引页），会增加额外开销
占用存储空间： 索引需要独立的存储空间，特别是对于大表的多列索引
需要维护： 随着数据增删改，索引可能产生碎片，需要定期重建（REBUILD）以保持性能

常见索引类型：

B-Tree索引： 默认类型，适合等值查询和范围查询，平衡树结构
位图索引（Bitmap）： 适合低基数列（如性别、状态），适合数据仓库
函数索引： 基于表达式或函数创建的索引（如UPPER(name)）
复合索引： 基于多列创建的索引，遵循最左前缀原则

# 9. 事务

定义： 是一系列的数据库操作，是数据库应用的基本逻辑单位。

事务性质（ACID）：

原子性： 即不可分割性，事务要么全部被执行，要么就全部不被执行。
一致性或可串性： 事务的执行使得数据库从一种正确状态转换成另一种正确状态。
隔离性： 在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务。
持久性： 事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

# 10. 锁

类型： 共享锁、互斥锁

两段锁协议：

阶段1：加锁阶段
阶段2：解锁阶段

# 11. 死锁及处理

定义： 事务循环等待数据锁，则会死锁。

死锁处理： 预防死锁协议，死锁恢复机制。

# 12. 存储过程

定义： 存储过程就是编译好了的一些SQL语句。

优点：

存储过程因为SQL语句已经预编译过了，因此运行的速度比较快。
可保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
可以降低网络的通信量。存储过程主要是在服务器上运行，减少对客户机的压力。
存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。
存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。

# 13. 触发器

定义： 当满足触发器条件，则系统自动执行触发器的触发体。

触发时间： BEFORE, AFTER
触发事件： INSERT, UPDATE, DELETE
触发类型： 行触发、语句触发

# 14. 内联接与外联接区别？

内连接： 是保证两个表中所有的行都要满足连接条件。

外连接： 在外连接中，某些不满足条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种。

# 二、SQL基础实战

# 1. 行转列问题

源数据：

教师号	星期号	是否有课
1	2	有
1	3	有
2	1	有
3	2	有
1	2	有

目标结果：

教师号	星期一	星期二	星期三
1		2	1
2	1
3		1

各星期下的数字表示：对应的教师在星期几已经排的课数

SQL语句：

SELECT 教师号,
    SUM(CASE WHEN 星期号 = 1 THEN 1 ELSE 0 END) AS 星期一,
    SUM(CASE WHEN 星期号 = 2 THEN 1 ELSE 0 END) AS 星期二,
    SUM(CASE WHEN 星期号 = 3 THEN 1 ELSE 0 END) AS 星期三
FROM 课程表
GROUP BY 教师号;

# 2. 多表关联统计

表结构：

书表(books)： book_id, book_name, creatdate, Lastmodifydate, decription
- 示例：001, 三个人的世界, 2005-02-02, 2005-07-07, NULL
作者表(authors)： A_id, A_name
- 示例：01 王纷, 02 李尚, 03 泰和
部门表(depts)： d_id, d_name
- 示例：001 编辑一部, 002 编辑二部, 003 编辑三部
书和作者关联表(bookmap)： book_id, A_id
- 示例：001,01, 001,02, 001,03
部门和作者关联表(depmap)： d_id, a_id
- 示例：001,01, 002,02, 003,03

需求： 找出每个部门的所写的总书量（比如，一本书有3个人写，如果三个人在不同的部门，则每个部门的总数量就是1）

结果：

部门	书量
编辑一部	1
编辑二部	1
编辑三部	1

SQL：

SELECT d.d_name AS 部门, COUNT(DISTINCT b.book_id) AS 书量
FROM depts d
JOIN depmap dp ON d.d_id = dp.d_id
JOIN authors a ON dp.a_id = a.A_id
JOIN bookmap bm ON a.A_id = bm.A_id
JOIN books b ON bm.book_id = b.book_id
GROUP BY d.d_id, d.d_name;

# 3. 复杂查询

表名：wu_plan

ID	plan	model	corp_code	plannum	prixis
1	00001	exx22	nokia	2000	0
2	00002	1c001	sony	3000	0

表名：wu_bom

ID	plan	pact	amount
1	00001	aa1	300
2	00001	aa2	200
3	00002	bb1	500
4	00002	bb2	800
5	00002	bb3	400

需求： 查询这两个表中plan唯一，每一个plan中amount最少的，plannum大于prixis的记录

结果： （关联后的特定记录）

# 4. Top N问题

表1： 部门、条码、品名、销售额、销售数量、销售日期

表2： 课别、部门

要求： 先按部门排序，再按销售额、销售数量排序检索出某个课别每个部门一个时期内的商品销售额的前三名。

如查询01课别2007年4月15日到2007年4月22日每个部门一个周内的商品销售额合计的前三名。

# 三、常见SQL面试大题类

# 1. 触发器的作用？

答：触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

# 2. 什么是存储过程？用什么来调用？

答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

# 3. 索引的作用？和它的优点缺点是什么？

答：索引是一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。

缺点： 它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

# 4. 什么是内存泄漏？

答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

# 5. 维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

答：尽可能使用约束，如check、主键、外键、非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整性和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低。

# 6. 什么是事务？什么是锁？

答：

事务： 就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。
锁：在所有的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

# 7. 什么叫视图？游标是什么？

答：

视图： 是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增、改、查、操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
游标： 是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

# 8. 为管理业务培训信息，建立3个表

表结构：

S(S#, SN, SD, SA)： S#, SN, SD, SA分别代表学号，学员姓名，所属单位，学员年龄
C(C#, CN)： C#, CN分别代表课程编号，课程名称
SC(S#, C#, G)： S#, C#, G分别代表学号，所选的课程编号，学习成绩

（1）使用标准SQL嵌套语句查询选修课程名称为'税收基础'的学员学号和姓名？

SELECT s#, sn 
FROM s 
WHERE s# IN (
    SELECT s# 
    FROM sc, c 
    WHERE c.c# = sc.c# AND cn = '税收基础'
);

（2）使用标准SQL嵌套语句查询选修课程编号为'C2'的学员姓名和所属单位？

SELECT sn, sd 
FROM s, sc 
WHERE s.s# = sc.s# AND sc.c# = 'c2';

（3）使用标准SQL嵌套语句查询不选修课程编号为'C5'的学员姓名和所属单位？

SELECT sn, sd 
FROM s 
WHERE s# NOT IN (
    SELECT s# 
    FROM sc 
    WHERE c# = 'c5'
);

（4）查询选修了课程的学员人数

SELECT 学员人数 = COUNT(DISTINCT s#) 
FROM sc;

（5）查询选修课程超过5门的学员学号和所属单位？

SELECT sn, sd 
FROM s 
WHERE s# IN (
    SELECT s# 
    FROM sc 
    GROUP BY s# 
    HAVING COUNT(DISTINCT c#) > 5
);

# 9. SQL Server 2000中使用的数据库对象

答：表格、视图、用户定义的函数，以及存储过程；触发器等。

# 10. NULL是什么意思？

答： NULL(空)这个值表示UNKNOWN(未知)：它不表示""(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。

# 11. 什么是索引？SQL Server 2000里有什么类型的索引？

答：简单地说，索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式：聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个（或哪些）字段，这些字段都会按顺序被保存在表格。由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。这个行标识符是一个指向磁盘上数据的指针。它允许每个表格有多个非聚集索引。

# 12. 什么是主键？什么是外键？

答：

主键： 是表格里的（一个或多个）字段，只用来定义表格里的行；主键里的值总是唯一的。
外键： 是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格（尽管可能是同一个表格）里的一系列相连的字段。那么这些相连的字段就是外键。

# 13. 什么是触发器？SQL Server 2000有什么不同类型的触发器？

答：触发器是一种专用类型的存储过程，它被捆绑到SQL Server 2000的表格或者视图上。在SQL Server 2000里，有INSTEAD-OF和AFTER两种触发器。

INSTEAD-OF触发器： 是替代数据操控语言(DML)语句对表格执行语句的存储过程。
AFTER触发器： 要在DML语句在数据库里使用之后才执行。

# 14. 如何确保一个带有名为F1d1字段的TableB表格里只具有F1d1字段里的那些值，而这些值同时在名为TableA的表格的F1d1字段里？

答：使用外键限制。外键限制用来维护引用的完整性。它被用来确保表格里的字段只保存有已经在不同的（或者相同的）表格里的另一个字段里定义了的值。这个字段就是候选键（通常是另外一个表格的主键）。

# 15. 对一个投入使用的在线事务处理表格有过多索引需要有什么样的性能考虑？

答：对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。

# 16. 你可以用什么来确保表格里的字段只接受特定范围里的值？

答： Check限制，它在数据库表格里被定义，用来限制输入该列的值。

# 17. 什么是相关子查询？如何使用这些查询？

答：相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。

# 18. 列举几种表连接方式

答：

等连接（内连接）
非等连接
自连接
外连接（左、右、全）
Hash join / Merge join / Nest loop (cluster join) / Index join

左外关联（LEFT OUTER JOIN）：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

右外关联（RIGHT OUTER JOIN）：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

全外关联（FULL OUTER JOIN）：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON (e.department_id = d.department_id);

连接类型对比表：

连接类型	定义	示例
内连接	只连接匹配的行	`select A.c1, B.c2 from A join B on A.c3 = B.c3;`
左外连接	包含左边表的全部行以及右边表中全部匹配的行	`select A.c1, B.c2 from A left join B on A.c3 = B.c3;`
右外连接	包含右边表的全部行以及左边表中全部匹配的行	`select A.c1, B.c2 from A right join B on A.c3 = B.c3;`
全外连接	包含左、右两个表的全部行	`select A.c1, B.c2 from A full join B on A.c3 = B.c3;`
Theta连接	使用等值以外的条件来匹配左、右两个表中的行	`select A.c1, B.c2 from A join B on A.c3 != B.c3;`
交叉连接	生成笛卡尔积	`select A.c1, B.c2 from A, B;`

# 19. 不借助第三方工具，怎样查看SQL的执行计划

（1）使用Explain Plan，查询PLAN_TABLE：

EXPLAIN PLAN
SET STATEMENT_ID='QUERY1'
FOR
SELECT * FROM a WHERE aa=1;

SELECT operation, options, object_name, object_type, ID, parent_id
FROM plan_table
WHERE STATEMENT_ID = 'QUERY1'
ORDER BY ID;

（2）SQLPLUS中的SET TRACE：

SET AUTOTRACE ON;

# 20. 如何使用CBO，CBO与RULE的区别

区别：

RBO： 根据规则选择最佳执行路径来运行查询。
CBO： 根据表统计找到最低成本的访问数据的方法确定执行计划。

使用CBO需要注意：

需要经常对表进行ANALYZE命令进行分析统计；
需要稳定执行计划；
需要使用提示(Hint)。

使用RULE需要注意：

选择最有效率的表名顺序
优化SQL的写法

# 21. 如何定位重要（消耗资源多）的SQL

-- 使用CPU多的用户session
SELECT a.SID, spid, status, SUBSTR(a.program, 1, 40) prog, a.terminal, 
       a.SQL_TEXT, osuser, VALUE / 60 / 100 VALUE
FROM v$session a, v$process b, v$sesstat c
WHERE c.statistic# = 12 AND c.SID = a.SID AND a.paddr = b.addr
ORDER BY VALUE DESC;

-- 查看消耗资源多的SQL
SELECT sql_text 
FROM v$sql 
WHERE disk_reads > 1000 
   OR (executions > 0 AND buffer_gets/executions > 30000);

# 22. 如何跟踪某个session的SQL

利用TRACE跟踪：

ALTER SESSION SET SQLTRACE ON;
COLUMN SQL format a200;

SELECT machine, sql_text SQL
FROM v$sqltext a, v$session b
WHERE address = sql_address
  AND machine = '&A'
ORDER BY hash_value, piece;

# 23. SQL调整最关注的是什么

检查系统的I/O问题：

sar -d 能检查整个系统的iostat（IO statistics）
查看该SQL的response time（db block gets/consistent gets/physical reads/sorts (disk)）

# 24. 说说你对索引的认识（索引的结构、对DML影响、对查询影响、为什么提高查询性能）

答：索引有B-TREE、BIT、CLUSTER等类型。ORACLE使用了一个复杂的自平衡B-tree结构。

对DML影响： 插入、删除、修改时，同时会进行索引的修改，在性能上有一定的影响。
查询影响： 有索引且查询条件能使用索引时，数据库会先读取索引，根据索引内容和查询条件，查询出ROWID，再根据ROWID取出需要的数据。由于索引内容通常比全表内容要少很多，因此通过先读索引，能减少I/O，提高查询性能。

# 25. 使用索引查询一定能提高查询的性能吗？为什么

答：通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。
这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5次的磁盘I/O。
因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

适用情况：

基于一个范围的检索，一般查询返回结果集小于表中记录数的30%宜采用；
基于非唯一性索引的检索。

# 26. 绑定变量是什么？绑定变量有什么优缺点？

答：绑定变量是指在SQL语句中使用变量，改变变量的值来改变SQL语句的执行结果。

优点： 使用绑定变量，可以减少SQL语句的解析，能减少数据库引擎消耗在SQL语句解析上的资源。提高了编程效率和可靠性。减少访问数据库的次数，就能实际上减少ORACLE的工作量。

缺点： 经常需要使用动态SQL的写法，由于参数的不同，可能SQL的执行效率不同；不能使用histogram，SQL优化比较困难。

# 27. 如何稳定（固定）执行计划

答：

可以在SQL语句中指定执行计划。使用HINTS；
query_rewrite_enabled = true
star_transformation_enabled = true
optimizer_features_enable = 9.2.0
创建并使用stored outline

# 28. 和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么

答：

8i： sort_area_size / sort_area_retained_size 决定了排序所需要的内存，如果排序操作不能在sort_area_size中完成，就会用到temp表空间。
9i： 如果workarea_size_policy=auto时，排序在pga内进行，通常pga_aggregate_target的1/20可以用来进行disk sort；如果workarea_size_policy=manual时，排序需要的内存由sort_area_size决定。

临时表空间作用： 在进行排序操作时，如果排序的内容太多，内存里不能全部放下，则需要进行外部排序，此时需要利用临时表空间来存放排序的中间结果。

# 29. 存在表T(a, b, c, d)，要根据字段c排序后取第21-30条记录显示，请给出SQL

解法一（使用ROWNUM）：

SELECT *
FROM (
    SELECT ROWNUM AS row_num, tmp_tab.*
    FROM (
        SELECT a, b, c, d 
        FROM T 
        ORDER BY c
    ) tmp_tab
    WHERE ROWNUM <= 30
)
WHERE row_num >= 20
ORDER BY row_num;

解法二（使用分析函数）：

SELECT *
FROM (
    SELECT c.*, ROWNUM AS rn 
    FROM (
        SELECT * 
        FROM t 
        ORDER BY c DESC
    ) c
)
WHERE rn BETWEEN 21 AND 30;

# 30. 如何查看SQL的执行计划

（同第19题）

# 四、数据库基本概念类

# 1. Pctused和pctfree表示什么含义有什么作用？

答： pctused与pctfree控制数据块是否出现在freelist中。

pctfree： 控制数据块中保留用于update的空间，当数据块中的free space小于pctfree设置的空间时，该数据块从freelist中去掉。
pctused： 当块由于dml操作free space大于pct_used设置的空间时，该数据库块将被添加在freelist链表中。

# 2. 简单描述tablespace/segment/extent/block之间的关系

答：

tablespace（表空间）： 一个数据库划分为一个或多个逻辑单位，该逻辑单位成为表空间；每一个表空间可能包含一个或多个Segment。
Segments（段）： 指在tablespace中为特定逻辑存储结构分配的空间。每一个段是由一个或多个extent组成。包括数据段、索引段、回滚段和临时段。
Extents（区）： 一个extent由一系列连续的Oracle blocks组成。ORACLE为通过extent来给segment分配空间。
Data Blocks（数据块）： Oracle数据库最小的I/O存储单位，一个data block对应一个或多个分配给data file的操作系统块。

# 3. 描述tablespace和datafile之间的关系

答：一个表空间可包含一个或多个数据文件。表空间利用增加或扩展数据文件扩大表空间，表空间的大小为组成该表空间的数据文件大小的和。一个datafile只能属于一个表空间。

# 4. 本地管理表空间和字典管理表空间的特点，ASSM有什么特点

答：

本地管理表空间（LMT）： （9i默认）空闲块列表存储在表空间的数据文件头。特点：减少数据字典表的竞争，当分配和收缩空间时会产生回滚，不需要合并。
字典管理表空间（DMT）： （8i默认）空闲块列表存储在数据库中的字典表里。特点：片由数据字典管理，可能造成字典表的争用。
ASSM（自动段空间管理）： 它首次出现在Oracle920里，有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（free block）。

# 5. 回滚段的作用是什么？

答：回滚段用于保存数据修改前的映象，这些信息用于生成读一致性数据库信息、在数据库恢复和Rollback时使用。一个事务只能使用一个回滚段。

事务回滚： 当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。
事务恢复： 当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。
读一致性： 当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。

# 6. 日志的作用是什么？

答：日志文件（Log File）记录所有对数据库数据的修改，主要是保护数据库以防止故障，以及恢复数据时使用。

每一个数据库至少包含两个日志文件组。每个日志文件组至少包含两个日志文件成员。
日志文件组以循环方式进行写操作。
每一个日志文件成员对应一个物理文件。

重做日志文件： 含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复，一个数据库至少需要两个重做日志文件。

归档日志文件： 是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。

# 7. SGA主要有那些部分，主要作用是什么

答：系统全局区（SGA）是ORACLE为实例分配的一组共享缓冲存储区，用于存放数据库数据和控制信息，以实现对数据库数据的管理和操作。

SGA主要包括：

共享池(shared pool)： 用来存储最近执行的SQL语句和最近使用的数据字典的数据。
数据缓冲区(database buffer cache)： 用来存储最近从数据文件中读写过的数据。
重作日志缓冲区(redo log buffer)： 用来记录服务或后台进程对数据库的操作。

可选内存结构：

Java pool： 用来存储Java代码。
Large pool： 用来存储不与SQL直接相关的大型内存结构。备份、恢复使用。

# 8. Oracle系统进程主要有哪些，作用是什么

答：

数据写进程(DBWR)： 负责将更改的数据从数据库缓冲区高速缓存写入数据文件。
日志写进程(LGWR)： 将重做日志缓冲区中的更改写入在线重做日志文件。
系统监控(SMON)： 检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复。
进程监控(PMON)： 负责在一个Oracle进程失败时清理资源。
检查点进程(CKPT)： 负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时，更新控制文件和数据文件中的数据库状态信息。
归档进程(ARCH)： 在每次日志切换时把已满的日志组进行备份或归档。
恢复进程(RECO)： 保证分布式事务的一致性，在分布式事务中，要么同时commit，要么同时rollback。
作业调度器(CJQ)： 负责将调度与执行系统中已定义好的job，完成一些预定义的工作。

# 五、备份恢复类

# 1. 备份如何分类？

答：

逻辑备份： exp/imp 指定表的逻辑备份
物理备份：
- 热备份：alter tablespace begin/end backup;
- 冷备份：脱机备份（database shutdown）
- RMAN备份：full backup/incremental backup（累积/差异）

物理备份是最主要的备份方式。 用于保证数据库在最小的数据库丢失或没有数据丢失的情况下得到恢复。

冷物理备份： 提供了最简单和最直接的方法保护数据库因物理损坏丢失。建议在以下情况中使用：

对一个已经存在大量数据量的数据库，在晚间数据库可以关闭，此时应用冷物理备份。
对需对数据库服务器进行升级（如更换硬盘），此时需要备份数据库信息，并在新的硬盘中恢复这些数据信息，建议采用冷物理备份。

热物理备份： 主要是指备份过程在数据库打开并且用户可以使用的情况下进行。

逻辑备份（EXP/IMP）： 用于实现数据库对象的恢复。但不是基于时间点可完全恢复的备份策略。只能作为联机备份和脱机备份的一种补充。

# 2. 归档是什么含义

答：关于归档日志：Oracle要将填满的在线日志文件组归档时，则要建立归档日志（archived redo log）。

数据库可运行在两种不同方式下：

NOARCHIVELOG方式： 不能进行在线日志的归档。
ARCHIVELOG方式： 可实施在线日志的归档。

数据库只有运行在ARCHIVELOG模式下，并且能够进行自动归档，才可以进行联机备份。有了联机备份才有可能进行完全恢复。

# 3. 如果一个表在2004-08-04 10:30:00被drop，在有完善的归档和备份的情况下，如何恢复

答：

9i新增的FLASH BACK应该可以；
Logminer应该可以找出DML；
有完善的归档和备份，先归档当前数据，然后可以先恢复到删除的时间点之前，把DROP的表导出来，然后再恢复到最后归档时间。

恢复步骤：

startup mount;
alter database recover automatic until time '2004-08-04:10:30:00';
alter database open resetlogs;

# 4. RMAN是什么，有何特点

答： RMAN（Recovery Manager）是DBA的一个重要工具，用于备份、还原和恢复oracle数据库。

RMAN有三种不同的用户接口：

COMMAND LINE方式
GUI方式（集成在OEM中的备份管理器）
API方式（用于集成到第三方的备份软件中）

特点：

功能类似物理备份，但比物理备份强大N倍；
可以压缩空块；
可以在块水平上实现增量；
可以把备份的输出打包成备份集，也可以按固定大小分割备份集；
备份与恢复的过程可以自动管理；
可以使用脚本（存在Recovery catalog中）；
可以做坏块监测。

# 5. Standby的特点

答：备用数据库（standby database）：ORACLE推出的一种高可用性（HIGH AVAILABLE）数据库方案，在主节点与备用节点间通过日志同步来保证数据的同步，备用节点作为主节点的备份，可以实现快速切换与灾难性恢复。

9i中的三种数据保护模式：

MAXIMIZE PROTECTION： 最大数据保护与无数据分歧，LGWR将同时传送到备用节点，在主节点事务确认之前，备用节点也必须完全收到日志数据。如果网络不好，引起LGWR不能传送数据，将引起严重的性能问题，导致主节点DOWN机。
MAXIMIZE AVAILABILITY： 无数据丢失模式，允许数据分歧，允许异步传送。正常情况下运行在最大保护模式，在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式。
MAXIMIZE PERFORMANCE： 这种模式应当可以说是从8i继承过来的备用服务器模式，异步传送，无数据同步检查，可能丢失数据，但是能获得主节点的最大性能。9i在配置DATA GUARD的时候默认就是MAXIMIZE PERFORMANCE。

# 6. 对于一个要求恢复时间比较短的系统（数据库50G，每天归档5G），你如何设计备份策略

答：

数据库比较大逻辑备份没什么必要，每天归档5G，每周三/周六自动归档10G，每月RMAN归档全库。应该有standby。
rman/每月一号 level 0
每周末/周三 level 1
其它每天 level 2

# 六、随意发挥类

# 1. 对于一个存在系统性能的系统，说出你的诊断处理思路

答：

做statspack收集系统相关信息，了解系统大致情况/确定是否存在参数设置不合适的地方/查看top 5 event/查看top sql等；
查v$system_event/v$session_event/v$session_wait；
- 从v$system_event开始，确定需要什么资源（db file sequential read）等；
- 深入研究v$session_event，确定等待事件涉及的会话；
- 从v$session_wait确定详细的资源争用情况（p1-p3的值：file_id/block_id/blocks等）；
通过v$sql/v$sqltext/v$sqlarea表确定disk_reads、（buffer_gets/executions）值较大的SQL。

# 2. 列举几种诊断IO、CPU、性能状况的方法

答：

top, uptime, vmstat, iostat, statspack, sql_trace/tkprof
查v$system_event/v$session_event/v$session_wait
查v$sqlarea（disk_reads或buffer_gets/executions较大的SQL）
或者第三方的监视工具，TOAD就不错。

# 3. 对statspack有何认识

答： StatSpack是Oracle公司提供的一个收集数据库运行性能指标的软件包。可以做数据库健康检查报告。

该软件包从8i起，在9i、10g都有显著的增强；
辅助表由最初的25个增长到43个；
收集级别参数由原来的3个（0、5、10）增加到5个（0、5、6、7、10）；
通过分析收集的性能指标，数据库管理员可以详细地了解数据库目前的运行情况，对数据库实例、等待事件、SQL等进行优化调整；
利用statspack收集的snapshot，可以统计制作数据库的各种性能指标的统计趋势图表。

# 4. 如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响

答：

可以先表分析一下，然后测试创建索引前后对应用的性能影响；
需要考虑的是该索引列不经常更新，不是有很多重复值的情况时，在大表中使用索引特别有效；
创建的索引可以跟数据表分不同表空间存储；
在系统比较空闲时nologging选项（如果有dataguard则不可以使用nologging）；
大的sort_area_size或pga_aggregate_target较大。

# 5. 对raid10和raid5有何认识

答：

RAID 10（或称RAID 1+0）： 与RAID 0+1不同，它是用硬盘驱动器先组成RAID 1阵列，然后在RAID 1阵列之间再组成RAID 0阵列。
- 特点：具有良好的数据传输性能，比RAID 0+1具有更高的可靠性。实际容量为M×n/2，磁盘利用率为50%。需要至少4个硬盘驱动器构成，价格昂贵。
RAID 5： （文档中未详细描述，建议补充）通过分布式奇偶校验提供数据冗余，允许一个磁盘故障，磁盘利用率为(n-1)/n。

← Mysql连环问短视频平台常见SQL面试题→