Oracle 表管理

表的创建和设计

表的创建

基本创建语法：

-- 创建基本表
CREATE TABLE employees (
  employee_id NUMBER(6) PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25) NOT NULL,
  email VARCHAR2(100) UNIQUE,
  phone_number VARCHAR2(20),
  hire_date DATE DEFAULT SYSDATE,
  job_id VARCHAR2(10),
  salary NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER(4)
);

-- 使用存储参数创建表
CREATE TABLE orders (
  order_id NUMBER(12) PRIMARY KEY,
  customer_id NUMBER(6),
  order_date DATE DEFAULT SYSDATE,
  status VARCHAR2(15),
  comments VARCHAR2(4000)
) PCTFREE 10 PCTUSED 40
  INITRANS 2 MAXTRANS 255
  TABLESPACE users
  STORAGE (
    INITIAL 64K
    NEXT 1M
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

表的设计原则

• 数据类型选择：选择合适的数据类型，避免过大或过小
• 约束设计：合理使用主键、外键、唯一约束、检查约束和非空约束
• 存储参数：根据表的使用场景设置合适的存储参数
• 表空间分配：将表分配到合适的表空间，考虑性能和管理需求
• 分区策略：对于大型表，考虑使用分区表提高性能和可管理性
• 命名规范：使用清晰、一致的命名规范，便于管理和维护

表的设计最佳实践

• 规范化与反规范化平衡：根据业务需求和性能要求，在规范化和反规范化之间取得平衡
• 避免过度设计：不要创建不必要的列或约束
• 考虑未来增长：设计时考虑数据量的增长，预留足够的空间和扩展性
• 测试设计方案：在生产环境部署前，在测试环境验证表设计的性能和可维护性
• 文档化设计：记录表的设计思路、字段含义和约束条件，便于后续维护

表结构管理

表结构修改

添加列：

-- 添加新列
ALTER TABLE employees ADD (
  middle_name VARCHAR2(20),
  preferred_name VARCHAR2(50)
);

-- 添加带默认值的列
ALTER TABLE employees ADD (
  active_flag VARCHAR2(1) DEFAULT 'Y' NOT NULL
);

修改列：

-- 修改列数据类型
ALTER TABLE employees MODIFY (
  phone_number VARCHAR2(30)
);

-- 修改列默认值
ALTER TABLE employees MODIFY (
  hire_date DATE DEFAULT SYSDATE NOT NULL
);

删除列：

-- 删除列
ALTER TABLE employees DROP COLUMN middle_name;

-- 标记列为unused（快速删除）
ALTER TABLE employees SET UNUSED COLUMN preferred_name;
-- 稍后清理unused列
ALTER TABLE employees DROP UNUSED COLUMNS;

约束管理

添加约束：

-- 添加主键约束
ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

-- 添加外键约束
ALTER TABLE employees ADD CONSTRAINT emp_dept_fk 
  FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT emp_email_uk UNIQUE (email);

-- 添加检查约束
ALTER TABLE employees ADD CONSTRAINT emp_salary_ck 
  CHECK (salary > 0);

禁用和启用约束：

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;

-- 启用约束
ALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk;

-- 启用约束并验证数据
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT emp_salary_ck;

删除约束：

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT emp_email_uk;

-- 级联删除约束
ALTER TABLE employees DROP PRIMARY KEY CASCADE;

索引管理

创建索引：

-- 创建B树索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

-- 创建唯一索引
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

-- 创建位图索引（适用于低基数列）
CREATE BITMAP INDEX emp_active_flag_idx ON employees(active_flag);

-- 创建函数索引
CREATE INDEX emp_hire_year_idx ON employees(EXTRACT(YEAR FROM hire_date));

重建索引：

-- 重建索引
ALTER INDEX emp_last_name_idx REBUILD;

-- 在线重建索引（不阻塞DML操作）
ALTER INDEX emp_last_name_idx REBUILD ONLINE;

-- 重建索引并修改存储参数
ALTER INDEX emp_last_name_idx REBUILD 
  TABLESPACE index_ts
  STORAGE (INITIAL 1M NEXT 1M);

删除索引：

-- 删除索引
DROP INDEX emp_last_name_idx;

表数据管理

数据插入

基本插入：

-- 插入单行数据
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1001, 'John', 'Doe', 'john.doe@example.com', SYSDATE, 'IT_PROG', 6000);

-- 插入多行数据
INSERT ALL
  INTO employees (employee_id, first_name, last_name, email, job_id, salary)
  VALUES (1002, 'Jane', 'Smith', 'jane.smith@example.com', 'HR_REP', 5000)
  INTO employees (employee_id, first_name, last_name, email, job_id, salary)
  VALUES (1003, 'Bob', 'Johnson', 'bob.johnson@example.com', 'SA_REP', 7000)
SELECT * FROM DUAL;

-- 从其他表插入数据
INSERT INTO employees_backup (employee_id, first_name, last_name, email)
SELECT employee_id, first_name, last_name, email
FROM employees
WHERE department_id = 10;

数据更新

基本更新：

-- 更新单行数据
UPDATE employees
SET salary = salary * 1.1,
    job_id = 'IT_MANAGER'
WHERE employee_id = 1001;

-- 更新多行数据
UPDATE employees
SET commission_pct = 0.1
WHERE job_id LIKE '%_REP';

-- 使用子查询更新
UPDATE employees e
SET salary = (
  SELECT AVG(salary) * 1.05
  FROM employees
  WHERE job_id = e.job_id
)
WHERE job_id = 'IT_PROG';

数据删除

基本删除：

-- 删除单行数据
DELETE FROM employees
WHERE employee_id = 1001;

-- 删除多行数据
DELETE FROM employees
WHERE department_id = 10;

-- 截断表（快速删除所有数据）
TRUNCATE TABLE employees_backup;

-- 截断分区表的特定分区
ALTER TABLE sales TRUNCATE PARTITION sales_q1_2023;

数据导入导出

使用数据泵导出：

# 导出指定表
expdp system/password@orcl tables=employees,departments 
  dumpfile=hr_tables.dmp 
  directory=DATA_PUMP_DIR;

# 导出表的特定数据
expdp system/password@orcl tables=employees 
  dumpfile=emp_active.dmp 
  directory=DATA_PUMP_DIR
  query="WHERE active_flag='Y'";

使用数据泵导入：

# 导入表
impdp system/password@orcl tables=employees 
  dumpfile=hr_tables.dmp 
  directory=DATA_PUMP_DIR;

# 导入表并重命名
impdp system/password@orcl tables=employees 
  dumpfile=hr_tables.dmp 
  directory=DATA_PUMP_DIR
  remap_table=employees:employees_new;

# 仅导入表结构
impdp system/password@orcl tables=employees 
  dumpfile=hr_tables.dmp 
  directory=DATA_PUMP_DIR
  content=metadata_only;

分区表管理

分区表类型

范围分区：

-- 创建范围分区表
CREATE TABLE sales (
  sale_id NUMBER(12) PRIMARY KEY,
  sale_date DATE NOT NULL,
  customer_id NUMBER(6),
  amount NUMBER(10,2),
  region VARCHAR2(20)
)
PARTITION BY RANGE (sale_date) (
  PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
  PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
  PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

列表分区：

-- 创建列表分区表
CREATE TABLE customers (
  customer_id NUMBER(6) PRIMARY KEY,
  customer_name VARCHAR2(50),
  region VARCHAR2(20),
  status VARCHAR2(10)
)
PARTITION BY LIST (region) (
  PARTITION region_asia VALUES ('ASIA', 'JAPAN', 'CHINA'),
  PARTITION region_europe VALUES ('EUROPE', 'FRANCE', 'GERMANY'),
  PARTITION region_americas VALUES ('AMERICAS', 'USA', 'CANADA'),
  PARTITION region_rest VALUES (DEFAULT)
);

哈希分区：

-- 创建哈希分区表
CREATE TABLE orders (
  order_id NUMBER(12) PRIMARY KEY,
  customer_id NUMBER(6),
  order_date DATE,
  total_amount NUMBER(10,2)
)
PARTITION BY HASH (order_id)
PARTITIONS 8;

复合分区：

-- 创建范围-哈希复合分区表
CREATE TABLE sales_detail (
  sale_id NUMBER(12),
  sale_date DATE,
  product_id NUMBER(6),
  quantity NUMBER(5),
  amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (product_id)
SUBPARTITIONS 4 (
  PARTITION p2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION p2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
  PARTITION p2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
  PARTITION p2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

分区管理

添加分区：

-- 为范围分区表添加新分区
ALTER TABLE sales ADD PARTITION sales_q1_2024 
VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

-- 为列表分区表添加新分区
ALTER TABLE customers ADD PARTITION region_africa 
VALUES ('AFRICA', 'SOUTH AFRICA', 'EGYPT');

删除分区：

-- 删除分区（同时删除数据）
ALTER TABLE sales DROP PARTITION sales_q1_2023;

-- 截断分区（保留分区结构，删除数据）
ALTER TABLE sales TRUNCATE PARTITION sales_q2_2023;

-- 合并分区
ALTER TABLE sales MERGE PARTITIONS sales_q3_2023, sales_q4_2023 
INTO PARTITION sales_h2_2023;

-- 拆分分区
ALTER TABLE sales SPLIT PARTITION sales_h2_2023 
AT (TO_DATE('2023-10-01', 'YYYY-MM-DD')) 
INTO (PARTITION sales_q3_2023, PARTITION sales_q4_2023);

交换分区：

-- 创建交换表
CREATE TABLE sales_q1_2024_temp (
  sale_id NUMBER(12) PRIMARY KEY,
  sale_date DATE NOT NULL,
  customer_id NUMBER(6),
  amount NUMBER(10,2),
  region VARCHAR2(20)
);

-- 加载数据到交换表
INSERT INTO sales_q1_2024_temp
SELECT * FROM sales_staging WHERE sale_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD');

-- 交换分区
ALTER TABLE sales EXCHANGE PARTITION sales_q1_2024 
WITH TABLE sales_q1_2024_temp 
WITHOUT VALIDATION;

临时表管理

临时表类型

会话级临时表：

-- 创建会话级临时表
CREATE GLOBAL TEMPORARY TABLE temp_employees (
  employee_id NUMBER(6),
  first_name VARCHAR2(20),
  last_name VARCHAR2(25),
  department_id NUMBER(4)
) ON COMMIT PRESERVE ROWS;

事务级临时表：

-- 创建事务级临时表
CREATE GLOBAL TEMPORARY TABLE temp_orders (
  order_id NUMBER(12),
  customer_id NUMBER(6),
  order_date DATE
) ON COMMIT DELETE ROWS;

临时表使用

会话级临时表使用：

-- 插入数据
INSERT INTO temp_employees
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10;

-- 提交事务（数据保留）
COMMIT;

-- 查询数据（仍可访问）
SELECT * FROM temp_employees;

-- 会话结束时数据自动清除

事务级临时表使用：

-- 插入数据
INSERT INTO temp_orders
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date = SYSDATE;

-- 提交事务（数据自动清除）
COMMIT;

-- 查询数据（已清除）
SELECT * FROM temp_orders;

临时表最佳实践

• 适用于中间结果：使用临时表存储复杂查询的中间结果
• 减少 redo 生成：临时表的数据变更不生成 redo 日志，提高性能
• 会话隔离：不同会话的临时表数据相互隔离，避免冲突
• 合理使用：不要过度使用临时表，避免内存和临时表空间过度消耗
• 索引策略：根据查询需求为临时表创建适当的索引
• 统计信息：临时表的统计信息不会自动收集，需要手动收集或使用动态采样

表的监控和维护

表空间使用监控

检查表空间使用情况：

-- 检查表空间使用情况
SELECT tablespace_name, 
       SUM(bytes)/1024/1024 AS total_mb,
       SUM(bytes - blocks * block_size/1024/1024) AS used_mb,
       SUM(blocks * block_size/1024/1024) AS free_mb,
       ROUND((SUM(bytes) - SUM(blocks * block_size/1024/1024))/SUM(bytes)*100, 2) AS used_percent
FROM dba_free_space
GROUP BY tablespace_name;

-- 检查表的大小
SELECT segment_name, 
       segment_type, 
       SUM(bytes)/1024/1024 AS size_mb
FROM dba_segments
WHERE owner = 'SCOTT' AND segment_type = 'TABLE'
GROUP BY segment_name, segment_type
ORDER BY size_mb DESC;

表的统计信息

收集表的统计信息：

-- 收集表的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  no_invalidate => FALSE
);

-- 收集模式下所有表的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname => 'SCOTT',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE
);

查看统计信息：

-- 查看表的统计信息
SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables
WHERE table_name = 'EMPLOYEES';

-- 查看列的统计信息
SELECT column_name, num_distinct, density, num_nulls
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';

表的碎片整理

检查表的碎片：

-- 检查表的碎片情况
SELECT table_name, 
       num_rows, 
       blocks, 
       empty_blocks, 
       avg_space, 
       chain_cnt
FROM user_tables
WHERE table_name = 'EMPLOYEES';

-- 检查表的行迁移和链接
SELECT table_name, chain_cnt
FROM user_tables
WHERE chain_cnt > 0
ORDER BY chain_cnt DESC;

整理表的碎片：

-- 使用ALTER TABLE MOVE整理碎片
ALTER TABLE employees MOVE TABLESPACE users;

-- 在线整理碎片（Oracle 12c+）
ALTER TABLE employees MOVE ONLINE TABLESPACE users;

-- 整理后重建索引
ALTER INDEX emp_pk REBUILD;
ALTER INDEX emp_last_name_idx REBUILD;

-- 使用SHRINK SPACE整理碎片（需要启用行移动）
ALTER TABLE employees ENABLE ROW MOVEMENT;
ALTER TABLE employees SHRINK SPACE COMPACT;
ALTER TABLE employees SHRINK SPACE;

表的性能优化

表访问优化

全表扫描优化：

• 对于小表，全表扫描可能比索引扫描更高效
• 合理设置 DB_FILE_MULTIBLOCK_READ_COUNT 参数
• 使用并行查询加速大表的全表扫描

索引访问优化：

• 为频繁查询的列创建索引
• 避免在索引列上使用函数
• 考虑使用复合索引覆盖查询
• 定期重建碎片化的索引

分区表访问优化：

• 使用分区剪枝减少需要扫描的数据量
• 为分区表选择合适的分区键
• 考虑使用本地分区索引提高性能

表设计优化

数据类型优化：

• 使用最小必要的数据类型
• 对于固定长度的列，使用 CHAR 类型
• 对于可变长度的列，使用 VARCHAR2 类型
• 避免使用 LONG 和 LONG RAW 类型，改用 CLOB 和 BLOB

存储参数优化：

• 为表设置合适的 PCTFREE 和 PCTUSED
• 对于频繁更新的表，设置较高的 PCTFREE
• 对于静态表，设置较低的 PCTFREE
• 使用自动段空间管理（ASSM）

分区策略优化：

• 对于时间序列数据，使用范围分区
• 对于离散值数据，使用列表分区
• 对于随机分布的数据，使用哈希分区
• 对于复杂查询模式，考虑使用复合分区

表维护优化

批量操作优化：

• 使用批量绑定减少上下文切换
• 对于大批量插入，考虑使用 DIRECT PATH INSERT
• 对于大批量更新，考虑使用 MERGE 语句
• 对于大批量删除，考虑使用 TRUNCATE 或分区交换

并行操作：

-- 并行插入
INSERT /*+ PARALLEL(employees, 4) */ INTO employees
SELECT /*+ PARALLEL(emp_staging, 4) */ *
FROM emp_staging;

-- 并行更新
UPDATE /*+ PARALLEL(employees, 4) */ employees
SET salary = salary * 1.05
WHERE department_id = 10;

-- 并行删除
DELETE /*+ PARALLEL(employees, 4) */ FROM employees
WHERE hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

-- 并行创建表
CREATE TABLE employees_backup PARALLEL 4 AS
SELECT * FROM employees;

版本差异考虑

Oracle 11g

• 支持基本的分区表功能
• 临时表功能相对简单
• 表压缩功能有限
• 统计信息收集功能相对基础

Oracle 12c

• 引入可插拔数据库（PDB）中的表管理
• 增强了分区表功能，支持间隔分区
• 引入临时表的增强功能
• 改进了统计信息收集机制
• 支持在线表重定义的增强功能

Oracle 19c

• 进一步优化了分区表性能
• 增强了表压缩功能
• 改进了统计信息自动收集
• 引入了更多自动化的表管理功能
• 支持区块链表（用于不可篡改数据）

Oracle 21c

• 引入了混合分区表
• 增强了临时表的功能
• 改进了表的在线操作性能
• 支持更多的表级安全特性
• 引入了自动表优化功能

常见问题（FAQ）

Q1: 如何选择合适的表分区策略？

A1: 选择表分区策略的考虑因素：

• 数据分布：根据数据的分布特性选择分区类型
• 查询模式：考虑常见查询的过滤条件
• 维护需求：考虑数据加载、删除和归档的需求
• 性能目标：根据性能要求选择合适的分区粒度
• 存储管理：考虑存储布局和管理的便利性

Q2: 临时表和普通表的区别是什么？

A2: 临时表和普通表的主要区别：

• 数据隔离：临时表的数据与会话或事务隔离
• 日志生成：临时表的操作不生成 redo 日志
• 存储位置：临时表的数据存储在临时表空间
• 生命周期：临时表的数据在会话结束或事务提交后自动清除
• 性能特性：临时表的操作通常比普通表更快

Q3: 如何处理表的行迁移和行链接？

A3: 处理行迁移和行链接的方法：

• 预防措施：设置合适的 PCTFREE 参数
• 检测方法：使用 ANALYZE TABLE 或查询 user_tables.chain_cnt
• 解决方法：使用 ALTER TABLE MOVE 或 SHRINK SPACE 整理碎片
• 后续处理：整理后重建相关索引
• 监控机制：定期检查行迁移和链接的情况

Q4: 如何优化大表的查询性能？

A4: 优化大表查询性能的方法：

• 分区策略：使用分区表减少扫描的数据量
• 索引设计：创建合适的索引，考虑覆盖索引
• 查询优化：编写高效的 SQL 语句，避免全表扫描
• 并行查询：对于大型查询使用并行执行
• 统计信息：确保统计信息的准确性
• 存储优化：使用表压缩减少存储开销

Q5: 表空间不足时如何处理？

A5: 表空间不足的处理方法：

• 添加数据文件：为表空间添加新的数据文件
• 扩展数据文件：增大现有数据文件的大小
• 启用自动扩展：设置数据文件自动扩展
• 表空间清理：删除或归档不需要的数据
• 分区策略：考虑使用分区表进行数据管理
• 存储重组织：将表移动到其他表空间

Q6: 如何安全地重命名表？

A6: 重命名表的步骤：

• 检查依赖对象：确认没有其他对象依赖该表
• 获取锁：确保表上没有活动的事务
• 执行重命名：使用 ALTER TABLE RENAME TO 语句
• 更新依赖对象：更新视图、存储过程等依赖对象
• 更新应用代码：修改引用该表的应用代码
• 测试功能：验证重命名后所有功能正常

Q7: 如何处理表的统计信息过期问题？

A7: 处理统计信息过期的方法：

• 自动收集：启用自动统计信息收集任务
• 手动收集：定期手动收集统计信息
• 增量收集：对于分区表使用增量统计信息收集
• 动态采样：对于临时表使用动态采样
• 统计信息锁定：对于稳定的表锁定统计信息
• 执行计划管理：使用 SQL 计划管理稳定执行计划

Q8: 如何监控表的增长趋势？

A8: 监控表增长趋势的方法：

• 定期收集：定期收集表的大小和行数信息
• 历史记录：将收集的数据存储在历史表中
• 趋势分析：分析数据增长的趋势和速率
• 预测模型：基于历史数据预测未来的增长
• 告警机制：设置表大小的告警阈值
• 自动报告：生成定期的表增长报告

Q9: 如何优化表的批量加载性能？

A9: 优化表批量加载性能的方法：

• 直接路径插入：使用 INSERT /*+ APPEND */ 提示
• 并行加载：使用并行插入提高性能
• 禁用约束：在加载期间临时禁用约束
• 禁用日志：使用 NOLOGGING 选项减少日志生成
• 使用外部表：对于大型数据集，使用外部表加载
• 批量绑定：使用 PL/SQL 批量绑定减少上下文切换

Q10: 如何处理分区表的历史数据？

A10: 处理分区表历史数据的方法：

• 分区交换：使用分区交换快速归档数据
• 分区删除：直接删除不再需要的分区
• 分区合并：将多个小分区合并为一个
• 分区压缩：对历史分区应用高级压缩
• 分区移动：将历史分区移动到低成本存储
• 生命周期管理：使用自动分区管理策略