## 1. 数据模型设计
– **表结构设计：** 合理设计表结构，考虑数据类型、索引、约束等
– **关系设计：** 设计合理的表关系，确保数据完整性
– **分区设计：** 根据数据量和查询模式，设计合理的分区策略

## 2. SQL语句规划
– **查询语句：** 优化查询语句，提高查询性能
– **DML语句：** 合理使用DML语句，确保数据一致性
– **DDL语句：** 规划DDL语句的执行时机，避免影响GoldenGate同步

## 3. 数据转换规划
– **数据类型转换：** 规划源端和目标端的数据类型映射
– **业务逻辑转换：** 规划数据同步过程中的业务逻辑转换
– **数据过滤：** 规划需要过滤的数据和条件

## 4. 性能规划
– **索引设计：** 合理设计索引，提高查询性能
– **SQL优化：** 优化SQL语句，减少执行时间
– **批量操作：** 合理使用批量操作，提高数据处理效率

## 5. 安全性规划
– **权限管理：** 合理设置用户权限，确保数据安全
– **数据加密：** 考虑敏感数据的加密处理
– **审计日志：** 记录重要的SQL操作，便于审计

## 1. 查询操作
– **基本查询：**
“`sql
SELECT * FROM fgedu.employees;
SELECT employee_id, first_name, last_name FROM fgedu.employees WHERE department_id = 10;
“`

– **聚合查询：**
“`sql
SELECT department_id, COUNT(*) AS employee_count FROM fgedu.employees GROUP BY department_id;
SELECT AVG(salary) AS avg_salary FROM fgedu.employees WHERE department_id = 10;
“`

– **连接查询：**
“`sql
SELECT e.employee_id, e.first_name, d.department_name
FROM fgedu.employees e
JOIN fgedu.departments d ON e.department_id = d.department_id;
“`

## 2. 插入操作
– **单行插入：**
“`sql
INSERT INTO fgedu.employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
VALUES (1001, ‘John’, ‘Doe’, ‘john.doe@example.com’, SYSDATE, ‘IT_PROG’, 5000, 10);
“`

– **多行插入：**
“`sql
INSERT INTO fgedu.employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
VALUES
(1002, ‘Jane’, ‘Smith’, ‘jane.smith@example.com’, SYSDATE, ‘IT_PROG’, 5500, 10),
(1003, ‘Mike’, ‘Johnson’, ‘mike.johnson@example.com’, SYSDATE, ‘IT_PROG’, 6000, 10);
“`

– **从其他表插入：**
“`sql
INSERT INTO fgedu.employees_backup (employee_id, first_name, last_name, email)
SELECT employee_id, first_name, last_name, email FROM fgedu.employees WHERE department_id = 10;
“`

## 3. 更新操作
– **基本更新：**
“`sql
UPDATE fgedu.employees SET salary = salary * 1.1 WHERE department_id = 10;
“`

– **条件更新：**
“`sql
UPDATE fgedu.employees SET salary = 6000 WHERE employee_id = 1001 AND salary < 5500; ``` - **多表更新：** ```sql UPDATE fgedu.employees e SET e.salary = e.salary * 1.05 WHERE EXISTS (SELECT 1 FROM fgedu.departments d WHERE e.department_id = d.department_id AND d.department_name = 'IT'); ``` ## 4. 删除操作 - **基本删除：** ```sql DELETE FROM fgedu.employees WHERE employee_id = 1001; ``` - **条件删除：** ```sql DELETE FROM fgedu.employees WHERE hire_date < SYSDATE - 365; ``` - **截断表：** ```sql TRUNCATE TABLE fgedu.employees_backup; ```

## 1. 数据库配置SQL
– **启用归档模式：**
“`sql
ALTER DATABASE ARCHIVELOG;
“`

– **启用强制日志：**
“`sql
ALTER DATABASE FORCE LOGGING;
“`

– **启用补充日志：**
“`sql
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE fgedu.employees ADD SUPPLEMENTAL LOG GROUP emp_pk (employee_id) ALWAYS;
“`

## 2. 权限管理SQL
– **创建GoldenGate用户：**
“`sql
CREATE USER ggadmin IDENTIFIED BY ggadmin123;
“`

– **授予权限：**
“`sql
GRANT CONNECT, RESOURCE TO ggadmin;
GRANT SELECT ANY TABLE TO ggadmin;
GRANT INSERT, UPDATE, DELETE ON fgedu.* TO ggadmin;
GRANT EXECUTE ON dbms_logmnr TO ggadmin;
GRANT EXECUTE ON dbms_logmnr_d TO ggadmin;
GRANT SELECT ON v_$database TO ggadmin;
GRANT SELECT ON v_$log TO ggadmin;
GRANT SELECT ON v_$logfile TO ggadmin;
GRANT SELECT ON v_$archived_log TO ggadmin;
GRANT SELECT ON v_$transaction TO ggadmin;
GRANT SELECT ON v_$session TO ggadmin;
GRANT SELECT ON v_$instance TO ggadmin;
“`

## 3. 监控SQL
– **查询GoldenGate相关表：**
“`sql
SELECT * FROM ggadmin.gg_trail;
SELECT * FROM ggadmin.gg_process;
“`

– **查询数据库状态：**
“`sql
SELECT log_mode, force_logging FROM v$database;
SELECT supplemental_log_data_min FROM v$database;
“`

– **查询表补充日志状态：**
“`sql
SELECT owner, table_name, supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM dba_tab_modifications
WHERE table_name = ‘EMPLOYEES’;
“`

## 4. 数据验证SQL
– **比较源端和目标端数据：**
“`sql
— 源端
SELECT COUNT(*) FROM source.employees;
SELECT SUM(salary) FROM source.employees;

— 目标端
SELECT COUNT(*) FROM target.employees;
SELECT SUM(salary) FROM target.employees;
“`

– **检查数据一致性：**
“`sql
SELECT s.employee_id, s.first_name, s.last_name, s.salary,
t.employee_id, t.first_name, t.last_name, t.salary
FROM source.employees s
FULL OUTER JOIN target.employees t ON s.employee_id = t.employee_id
WHERE s.employee_id IS NULL OR t.employee_id IS NULL
OR s.first_name != t.first_name
OR s.last_name != t.last_name
OR s.salary != t.salary;
“`

## 1. GoldenGate数据转换
– **列映射：**
“`
MAP source.employees, TARGET target.employees,
COLMAP (
employee_id = employee_id,
first_name = first_name,
last_name = last_name,
email = email,
hire_date = hire_date,
salary = salary * 1.1 — salary increase
);
“`

– **过滤条件：**
“`
MAP source.employees, TARGET target.employees,
FILTER (department_id = 10);
“`

– **函数转换：**
“`
MAP source.employees, TARGET target.employees,
COLMAP (
employee_id = employee_id,
full_name = CONCAT(first_name, ‘ ‘, last_name),
hire_year = EXTRACT(YEAR FROM hire_date)
);
“`

## 2. SQL数据转换
– **数据类型转换：**
“`sql
— 字符串转日期
SELECT TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’) FROM dual;

— 日期转字符串
SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) FROM dual;

— 数字转字符串
SELECT TO_CHAR(12345, ‘99999’) FROM dual;

— 字符串转数字
SELECT TO_NUMBER(‘12345’) FROM dual;
“`

– **条件转换：**
“`sql
SELECT employee_id, first_name, last_name,
CASE
WHEN salary < 5000 THEN 'Low' WHEN salary >= 5000 AND salary < 10000 THEN 'Medium' ELSE 'High' END AS salary_level FROM fgedu.employees; ``` - **聚合转换：** ```sql SELECT department_id, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM fgedu.employees GROUP BY department_id; ``` ## 3. 数据处理 - **批量处理：** ```sql -- 批量更新 UPDATE /*+ BATCHED_UPDATES */ fgedu.employees SET salary = salary * 1.1 WHERE department_id = 10; -- 批量插入 INSERT /*+ APPEND */ INTO fgedu.employees_backup SELECT * FROM fgedu.employees; ``` - **并行处理：** ```sql -- 并行查询 SELECT /*+ PARALLEL(4) */ * FROM fgedu.employees; -- 并行更新 UPDATE /*+ PARALLEL(4) */ fgedu.employees SET salary = salary * 1.1; ``` - **分区处理：** ```sql -- 分区查询 SELECT * FROM fgedu.orders PARTITION (p202401); -- 分区维护 ALTER TABLE fgedu.orders ADD PARTITION p202403 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')); ```

## 1. 查询优化
– **使用索引：**
“`sql
— 创建索引
CREATE INDEX emp_dept_idx ON fgedu.employees(department_id);

— 使用索引的查询
SELECT * FROM fgedu.employees WHERE department_id = 10;
“`

– **避免全表扫描：**
“`sql
— 不好的查询（全表扫描）
SELECT * FROM fgedu.employees WHERE salary > 5000;

— 好的查询（使用索引）
CREATE INDEX emp_salary_idx ON fgedu.employees(salary);
SELECT * FROM fgedu.employees WHERE salary > 5000;
“`

– **使用绑定变量：**
“`sql
— 不好的查询（硬解析）
SELECT * FROM fgedu.employees WHERE employee_id = 1001;

— 好的查询（软解析）
SELECT * FROM fgedu.employees WHERE employee_id = :emp_id;
“`

## 2. DML优化
– **批量操作：**
“`sql
— 单行插入
INSERT INTO fgedu.employees VALUES (1001, ‘John’, ‘Doe’, …);

— 多行插入
INSERT INTO fgedu.employees VALUES
(1001, ‘John’, ‘Doe’, …),
(1002, ‘Jane’, ‘Smith’, …),
(1003, ‘Mike’, ‘Johnson’, …);
“`

– **使用FORALL：**
“`plsql
DECLARE
TYPE emp_tab IS TABLE OF fgedu.employees%ROWTYPE;
emp_records emp_tab;
BEGIN
— 填充数据
SELECT * BULK COLLECT INTO emp_records FROM fgedu.employees WHERE department_id = 10;

— 批量更新
FORALL i IN emp_records.FIRST..emp_records.LAST
UPDATE fgedu.employees SET salary = salary * 1.1 WHERE employee_id = emp_records(i).employee_id;

COMMIT;
END;
/
“`

– **使用MERGE语句：**
“`sql
MERGE INTO fgedu.employees e
USING (SELECT employee_id, first_name, last_name, salary FROM source_employees)
ON (e.employee_id = source_employees.employee_id)
WHEN MATCHED THEN
UPDATE SET e.first_name = source_employees.first_name,
e.last_name = source_employees.last_name,
e.salary = source_employees.salary
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name, salary)
VALUES (source_employees.employee_id, source_employees.first_name, source_employees.last_name, source_employees.salary);
“`

## 3. 执行计划分析
– **查看执行计划：**
“`sql
EXPLAIN PLAN FOR
SELECT * FROM fgedu.employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
“`

– **使用AUTOTRACE：**
“`sql
SET AUTOTRACE ON
SELECT * FROM fgedu.employees WHERE department_id = 10;
“`

– **识别性能瓶颈：**
“`sql
— 查看长时间运行的SQL
SELECT sql_id, sql_text, elapsed_time, cpu_time, disk_reads, buffer_gets
FROM v$sql
WHERE elapsed_time > 1000000
ORDER BY elapsed_time DESC;
“`

## 4. 数据库参数优化
– **调整PGA：**
“`sql
ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE=SPFILE;
“`

– **调整SGA：**
“`sql
ALTER SYSTEM SET sga_target = 4G SCOPE=SPFILE;
“`

– **调整共享池：**
“`sql
ALTER SYSTEM SET shared_pool_size = 1G SCOPE=SPFILE;
“`

## 案例：员工管理系统SQL开发

### 背景
– 客户：某企业
– 系统：Oracle Database 19c，GoldenGate 19c
– 需求：开发员工管理系统的SQL脚本，包括表结构创建、数据操作、查询优化等

### 实施步骤
1. **表结构设计：**
“`sql
— 创建部门表
CREATE TABLE fgedu.departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);

— 创建员工表
CREATE TABLE fgedu.employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) NOT NULL,
phone_number VARCHAR2(20),
hire_date DATE NOT NULL,
job_id VARCHAR2(10) NOT NULL,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4),
CONSTRAINT emp_email_uk UNIQUE (email),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES fgedu.departments(department_id)
);

— 创建索引
CREATE INDEX emp_dept_idx ON fgedu.employees(department_id);
CREATE INDEX emp_job_idx ON fgedu.employees(job_id);
CREATE INDEX emp_manager_idx ON fgedu.employees(manager_id);
“`

2. **数据插入：**
“`sql
— 插入部门数据
INSERT INTO fgedu.departments VALUES (10, ‘Administration’, 200, 1700);
INSERT INTO fgedu.departments VALUES (20, ‘Marketing’, 201, 1800);
INSERT INTO fgedu.departments VALUES (30, ‘Purchasing’, 114, 1700);

— 插入员工数据
INSERT INTO fgedu.employees VALUES (100, ‘Steven’, ‘King’, ‘steven.king@example.com’, ‘515.123.4567’, TO_DATE(‘2003-06-17’, ‘YYYY-MM-DD’), ‘AD_PRES’, 24000, NULL, NULL, 90);
INSERT INTO fgedu.employees VALUES (101, ‘Neena’, ‘Kochhar’, ‘neena.kochhar@example.com’, ‘515.123.4568’, TO_DATE(‘2005-09-21’, ‘YYYY-MM-DD’), ‘AD_VP’, 17000, NULL, 100, 90);
INSERT INTO fgedu.employees VALUES (102, ‘Lex’, ‘De Haan’, ‘lex.dehaan@example.com’, ‘515.123.4569’, TO_DATE(‘2001-01-13’, ‘YYYY-MM-DD’), ‘AD_VP’, 17000, NULL, 100, 90);
“`

3. **查询优化：**
“`sql
— 优化前的查询
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM fgedu.employees e, fgedu.departments d
WHERE e.department_id = d.department_id AND e.salary > 10000;

— 优化后的查询
SELECT /*+ USE_NL(e d) */ e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM fgedu.employees e
JOIN fgedu.departments d ON e.department_id = d.department_id
WHERE e.salary > 10000;

— 创建索引
CREATE INDEX emp_salary_idx ON fgedu.employees(salary);
“`

4. **数据转换：**
“`sql
— 数据类型转换
SELECT employee_id, first_name, last_name,
TO_CHAR(hire_date, ‘YYYY-MM-DD’) AS hire_date_str,
TO_CHAR(salary, ‘99999.99’) AS salary_str
FROM fgedu.employees;

— 条件转换
SELECT employee_id, first_name, last_name, salary,
CASE
WHEN salary < 5000 THEN 'Low' WHEN salary >= 5000 AND salary < 10000 THEN 'Medium' ELSE 'High' END AS salary_level FROM fgedu.employees; ``` 5. **性能监控：** ```sql -- 查看执行计划 EXPLAIN PLAN FOR SELECT * FROM fgedu.employees WHERE department_id = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -- 查看SQL执行统计 SET AUTOTRACE ON SELECT * FROM fgedu.employees WHERE department_id = 10; ``` ### 实施结果 - 成功创建员工管理系统的表结构 - 实现了数据的插入、查询、更新和删除操作 - 优化了SQL查询性能，提高了系统响应速度 - 实现了数据转换和处理功能 - 建立了性能监控机制，确保系统的稳定运行

## 案例：GoldenGate数据转换实现

### 背景
– 客户：某金融企业
– 系统：Oracle Database 19c，GoldenGate 19c
– 需求：在数据同步过程中进行数据转换，包括字段映射、数据过滤、业务逻辑转换等

### 实施步骤
1. **源端和目标端表结构：**
“`sql
— 源端表
CREATE TABLE source.customer (
customer_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
phone VARCHAR2(20),
address VARCHAR2(200),
status VARCHAR2(10),
create_date DATE
);

— 目标端表
CREATE TABLE target.customer (
customer_id NUMBER(10) PRIMARY KEY,
full_name VARCHAR2(100),
contact_info VARCHAR2(120),
address VARCHAR2(200),
status_code NUMBER(1),
create_date TIMESTAMP
);
“`

2. **GoldenGate配置：**
“`
— 配置Extract进程
EXTRACT ext1
USERID ggadmin, PASSWORD ggadmin123
EXTTRAIL /u01/app/goldengate/dirdat/et
TABLE source.customer;

— 配置Replicat进程
REPLICAT rep1
USERID ggadmin, PASSWORD ggadmin123
ASSUMETARGETDEFS
MAP source.customer, TARGET target.customer,
COLMAP (
customer_id = customer_id,
full_name = CONCAT(first_name, ‘ ‘, last_name),
contact_info = CONCAT(email, ‘ | ‘, phone),
address = address,
status_code = CASE status
WHEN ‘ACTIVE’ THEN 1
WHEN ‘INACTIVE’ THEN 0
ELSE -1
END,
create_date = CAST(create_date AS TIMESTAMP)
);
“`

3. **数据验证：**
“`sql
— 源端插入数据
INSERT INTO source.customer VALUES (1, ‘John’, ‘Doe’, ‘john.doe@example.com’, ‘123-456-7890’, ‘123 Main St’, ‘ACTIVE’, SYSDATE);
COMMIT;

— 目标端验证数据
SELECT * FROM target.customer;
“`

4. **性能优化：**
“`
— 优化Replicat进程
REPLICAT rep1
USERID ggadmin, PASSWORD ggadmin123
ASSUMETARGETDEFS
MAP source.customer, TARGET target.customer,
COLMAP (
customer_id = customer_id,
full_name = CONCAT(first_name, ‘ ‘, last_name),
contact_info = CONCAT(email, ‘ | ‘, phone),
address = address,
status_code = CASE status
WHEN ‘ACTIVE’ THEN 1
WHEN ‘INACTIVE’ THEN 0
ELSE -1
END,
create_date = CAST(create_date AS TIMESTAMP)
);
— 增加并行处理
PARALLEL 4;
“`

### 实施结果
– 成功实现了源端和目标端的数据同步
– 实现了字段映射、数据过滤、业务逻辑转换等功能
– 数据转换正确，满足业务需求
– 系统运行稳定，性能良好