高效的sql查询

海量数据查询总是会感觉很慢，特别当好几张海量表关联的时候更是如此。下面是收集的一些有效的tips。

1、使用index来使你的查询更快。

  a）尽量别使用不等操作符（!=或者<>）因为index可以告诉你表的某 个位置有什么，但是它没办法告诉你那里没有什么。另外： the index is used when optimizer_goal is FIRST_ROWS。所以下面的写法比较忌讳：

Select * from emp where empno != 0;

  b）不要使用函数。一旦使用了函数, 优化器就没办法使用index了除非是functional index。

Select * from emp where substr(ename, 1, 1) = ‘K’;

2、不要对不同类型的数据进行比较。比如整型和字符型的直接进行比较。

 

Select * from emp where empno = ‘7788’;

3、使用exists函数代替in函数

Select product_id, qty from product where product_id = 167 and 

item_no in (select item_no from items);   =====>

Select product_id, qty from product a where product_id = 167 and

exists (select ‘x’ from items b where b.item_no = a.item_no);

4、使用hint来指导优化器使用更好的执行计划。

5、如果能精确匹配就绝对不要使用模糊匹配，有一个总的原则，你给的条件越精确，所涉及的记录数越少，则查询越迅速。

比如下面的写法："COMCODE" like '3201%'就很慢，如果改成substr("COMCODE",1,4)='3201'则效率提升很多,而且效果是一样的。

6、Early elimination of candidate rows尽早消除候选行。

如果尽早消除了候选行，那么后续步骤需要的操作和资源都减少。假定下面的invoice_lines是一张海量表：

 

Select v.vendor_num, I.invoice_num, sum (l.amount) from vendors v, invoices I, 

invoice_lines l where

v.vendor_name = ‘ACME’ and l.vendor_num = v.vendor_num and I.vendor_num = 

l.vendor_num and I.invoice_num = l.invoice_num and

i.paid = ‘N’ group by v.vendor_num, I.invoice_num order by I.invoice_num;  

==========>

Select v.vendor_num, I.invoice_num, sum (l.amount) from vendors v, invoices I, 

invoice_lines l where

v.vendor_name = ‘ACME’ and I.vendor_num = v.vendor_num and I.paid = ‘N’ and 

l.vendor_num = I.vendor_num and l.invoice_num = I.invoice_num

Group by v.vendor_num = I.invoice_num order by I.invoice_num;

7、Minimize the number of throwaway rows尽量减少一次性行数。何为throwaway rows希望有人告知。

• Compare the number of rows from the two input row sources 

with the number of rows from the join operation. If both input 

row sources have more rows than the join operation has, you 

have identified a throwaway of rows.

• Example

Rows Operation

100 NESTED LOOPS

45 TABLE ACCESS (…) OF our_outer_table

4530 TABLE ACCESS (…) OF our_inner_table

• Select the table with less number of records as driving table