Oracle SELECT命令的25个高效使用技巧

一、SELECT基础优化技巧

1. 只选择必要的列

不良实践：

SELECT * FROM employees;

优化方案：

SELECT employee_id, first_name, last_name, department_id 
FROM employees;

优势：减少网络传输量，降低I/O开销，提高查询效率

2. 使用WHERE子句精确过滤

基础用法：

SELECT * FROM employees WHERE salary > 5000;

高级技巧：

-- 使用函数索引支持的函数
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 日期范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') 
                     AND TO_DATE('2023-01-31', 'YYYY-MM-DD');

3. 合理使用索引列

有效使用索引：

-- 索引列单独在运算符左侧
SELECT * FROM employees WHERE department_id = 10;

-- 避免对索引列使用函数
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2023';

优化方案：

-- 改为范围查询
SELECT * FROM employees 
WHERE hire_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') 
                   AND TO_DATE('2023-12-31', 'YYYY-MM-DD');

二、高级查询技巧

4. 使用WITH子句(CTE)优化复杂查询

WITH dept_stats AS (
    SELECT department_id, AVG(salary) avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.last_name, e.salary, d.avg_salary
FROM employees e
JOIN dept_stats d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

优势：提高复杂查询的可读性和性能

5. 使用分析函数

SELECT 
    employee_id,
    last_name,
    salary,
    department_id,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
FROM employees;

6. 使用PIVOT和UNPIVOT

PIVOT示例：

SELECT *
FROM (
    SELECT department_id, job_id, salary
    FROM employees
)
PIVOT (
    AVG(salary) FOR job_id IN ('IT_PROG' as IT, 'SA_REP' as Sales, 'HR_REP' as HR)
)
ORDER BY department_id;

三、JOIN优化技巧

7. 选择正确的JOIN类型

8. JOIN条件优化

不良实践：

SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

优化方案：