优化自己的sql

优化自己的sql

第一次写一个view，sql跑了10多秒，无法忍受。最后优化到0.5秒左右。这里讲一下plsql中优化冗长sql的步骤方法。

 

1.       F5调出sql的执行计划。

2.       观察，计划中对哪些表进行了全表检索(TABLE ACCESS FULL)，那些执行索引搜索(INDEX SCAN)的不用管。

3.       列出计划中全表检索的表，数一下每个表有多少数据：

Select count(1) from TABLE

4.       找出数据很多的表(一般万行以上)，这些表数据多，又被全表检索，所以一定很慢。我们的主要工作就是围绕这几张表进行啦。

5.       整理sql，将select子句中，同表的字段写在一起，这样方便观察，例如

Select  pr.po_release_id,

 pr.release_num AS release_num,

 pr.revision_num AS release_vision_num,

 

 poh.vendor_id AS vendor_id_c,

 poh.vendor_site_id AS vendor_site_id_c,

 poh.po_header_id,

 poh.segment1 AS order_number,

 poh.revision_num AS item_revision,

 poh.creation_date AS creation_date,

 poh.vendor_contact_id AS contact_id,

 mc.concatenated_segments,

 mc.concatenated_segments AS category_name  from  … mc ,poh,pr……

WHERE pll.po_line_id = pol.po_line_id

   AND pll.po_release_id = pr.po_release_id(+)

   AND pol.po_header_id = poh.po_header_id

   AND fsp.org_id = pol.org_id

   AND mc.category_id(+) = pol.category_id

   AND msi.inventory_item_id(+) = pol.item_id

     

   AND poh.org_id = hou.organization_id

     

   AND pll.line_location_id = hvc.line_location_id(+)

     

   AND fsp.inventory_organization_id = nvl(msi.organization_id, fsp.inventory_organization_id)

     

   AND ood.organization_id(+) = pll.ship_to_organization_id

   AND hla.LANGUAGE(+) = sys_context('USERENV', 'LANG')

   AND hla.location_id(+) = pll.ship_to_location_id

     

 

 

6.       找出步骤4找出的表，假设为mc，poh两张表。观察其在select子句和中出现的次数。较少出现的表，即我们对这张表只是利用到它的个别字段，为此在from子句中多关联一张表是很不划算的事，因为多关联了一张表，sql执行过程将大大减缓。我们对这种情况的处理方法是：专门写一个function，从表中取所用的字段值。

 

比如这里，我们看到mc表在select子句中只出现了两次（上面代码红色部分），我们为了这两个字段又关联了一张很庞大的表，实在很不划算。我们要专门为这两个字段写一段function，将where子句中与mc相关的限制条件作为参数传入function。如上述代码绿色部分，最后我们的function类似这样：

Function get_catogory_name(p_catogory_id number);

 

7.       现在可以讲from子句和where子句中，与mc表相关的语句全部删掉了~！上述代码可以写成这样：

Select  pr.po_release_id,

 pr.release_num AS release_num,

 pr.revision_num AS release_vision_num,

 

 poh.vendor_id AS vendor_id_c,

 poh.vendor_site_id AS vendor_site_id_c,

 poh.po_header_id,

 poh.segment1 AS order_number,

 poh.revision_num AS item_revision,

 poh.creation_date AS creation_date,

 poh.vendor_contact_id AS contact_id,

 get_catogory_name(pol.category_id)，

 get_catogory_name(pol.category_id) AS category_name  from …poh,pr…

WHERE pll.po_line_id = pol.po_line_id

   AND pll.po_release_id = pr.po_release_id(+)

   AND pol.po_header_id = poh.po_header_id

   AND fsp.org_id = pol.org_id

    AND msi.inventory_item_id(+) = pol.item_id

   AND poh.org_id = hou.organization_id

   AND pll.line_location_id = hvc.line_location_id(+)

   AND fsp.inventory_organization_id = nvl(msi.organization_id, fsp.inventory_organization_id)  

   AND ood.organization_id(+) = pll.ship_to_organization_id

   AND hla.LANGUAGE(+) = sys_context('USERENV', 'LANG')

   AND hla.location_id(+) = pll.ship_to_location_id

8.       现在我们为sql解决掉一张很大的并且无法使用索引检索的表，sql的执行速度将会得到比较明显的提升。

9.       对于select子句中出现次数较多的表，比如poh，虽然它也很大，执行计划中也无法用索引检索，但我们为它的每一个字段都写一个function是很麻烦的，这类表我们放在最后。如果速度还是无法忍受，也只好一个个写function了。要知道超过2秒的sql是客户无法忍受的~！

10.    还有一些其它的优化方法，在执行计划中，如果看到有全表检索的表，要设法让它使用索引检索，同一张表，全表检索和索引检索的速度会差很多倍。

11.    这种写function的优化方式，只适合于优化where子句中使用了外连接的字段（AND mc.category_id(+) = pol.category_id），function查不到对应数据时可以返回null，起到了模拟外连接的作用。而外连接一般都是减缓sql执行速度的主要因素。

 

刚上项目，以上很多是前辈教的方法，有些东西自己也没有领会的很深刻，可能另外还有优化sql执行速度的好方法~希望大家不吝赐教~O(∩_∩)O~

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22414008/viewspace-617577/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22414008/viewspace-617577/