ORACLE多表查询优化

一、FROM子句后面的表顺序有讲究，ORACLE在解析sql语句的时候对FROM子句后面的表名是从右往左解析的，是先扫描最右边的表，然后在扫描左边的表，然后用左边的表匹配数据，匹配成功后就合并。 所以，在对多表查询中，一定要把小表写在最右边。例如下面的两个语句：

--tableA：100w条记录  tableB：1w条记录 
--执行速度十秒
select count(*) from tableA, tableB;

--执行速度百秒甚至更高
select count(*) from tableB, tableA;

还有一种是三张表的查询，例如

select count(1) from tableA a,tableB b ,tableC c where a.id = b.id and a.id= c.id;

上面中tableA 为交叉表，根据oracle对From子句从右向左的扫描方式，应该把交叉表放在最末尾，然后才是最小表，所以上面的应该这样写:

--tableA a 交叉表,tabelB b 100w,tableC c 1w
select count(1) from tableB b ,tableC c ,tableA a where a.id=b.id and a.id=c.id;

二、ORACLE对where子句后面的条件过滤是自下向上，从右向左扫描的，所以和From子句一样一样的，把过滤条件排个序，按过滤数据的大小，自然就是最少数据的那个条件写在最下面，最右边，依次类推，例如:

--不可取性能低下 
select * from tableA a where a.id > 500 and a.lx = '2b' and a.id < (select count( 1 )from tableA where id = a.id) 

--性能高 
select * from tableA a where a.id < (select count( 1 ) from tableA where id = a.id) and a.id > 500 and a.lx = '2b' 

三、使用select的时候少用*，写上字段名，因为ORACLE的查询器会把*转换为表的全部列名，这个会浪费时间的，所以在大表中少用;

四、充分利用rowid ，可以用rowid来分页，删除查询重复记录，很强大的，给两个例子:

-- oracle查找重复记录 
select * from tableA a where a.rowid >= (select min(rowid) from tableB b where a.column = b.column) 

-- 分页 start = 10 limit = 10 
-- end 为 start + limit 
-- 1 .查询要排列的表a 
-- 2 .查询a表的rownum找出小于end的数据组成表b 
-- 3 .查询b表通过rownum找出大于start的数据完成
-- 简单的说先根据end值过滤数据，然后在根据start过滤数据 
select * from 
(
    select a.* , rownum rn from 
    (
       select * from uim_serv_file_data 
       order by ouid
    ) a where rownum <= 20 
) b where rn > 10 
order by ouid desc 

五、存储过程中需要注意的，多用commit了，既可以释放资源，但是要谨慎。          

六、减少对数据库表的查询，这个很重要，能减少就减少，因为在执行语句的时候oracle会做很多初始工作。          

七、少用in，多用exists来代替

--in的写法 
select * from tableA a where a.id in (select id form tableB b where b.id > 1 ) 
--exists 写法 
select * from tableA a where exists (select 1 from tableB b where a.id = b.id and b.id > 1 )

八、合理使用Json，使用合适的JOIN类型（INNER JOIN, LEFT JOIN, RIGHT JOIN）来减少不必要的数据处理。

--不推荐
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.emp_department = d.dept_id
WHERE d.dept_name = 'IT';

--推荐
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.emp_department = d.dept_id
WHERE d.dept_name = 'IT';

 九、分析执行计划

使用数据库提供的工具分析查询的执行计划，找出潜在的瓶颈。

EXPLAIN PLAN FOR
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

十、 分区表 ，对于大表，使用分区表可以显著提高查询性能。

假设有一个名为sales的表，按年份分区：

CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

查询特定分区的数据：

SELECT sale_id, amount
FROM sales
WHERE sale_date BETWEEN TO_DATE('2021-01-01', 'YYYY-MM-DD') AND TO_DATE('2021-12-31', 'YYYY-MM-DD');

十一、 使用缓存，利用数据库缓存机制，避免重复读取相同的数据。

在Oracle中，可以使用RESULT_CACHE提示：

SELECT /*+ RESULT_CACHE */ emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';