一个sql的优化-白红宇

一个sql的优化

阅读量：2497 次

发布时间：2019-05-11

本文共 6682 字，大约阅读时间需要 22 分钟。

处理了一起sql优化

[code]

SELECT

FROM

(

SELECT

f.name AS variance_name,

a.*,

(a.last_count_order-a.count_order) AS rise_diff ,

b.name AS t_name,

b.parent_id AS t_parent_id,

b.forum_dir,

b.forum_id AS t_forum_id,

b.pub_url AS t_pub_url,

b.pub_dir ,

d.price AS area_price ,

NVL(e.min_price,0) AS min_price,

NVL(e.max_price,0) AS max_price

FROM

PDL_PRODUCT a,

PDL_PRODUCT_TYPE b ,

(

SELECT

FROM

PP_PRICE

WHERE

PRICE BETWEEN 501 AND 1000

AND AREA_ID = 100

)

eml_price e ,

pdl_product_variance f

WHERE

a.status! =0

AND a.review_status<>4

AND a.order_type <2

AND a.concept IS NULL

AND a.type_id =b.id

AND b.status =1

AND b.type =3

AND b.parent_id =20811

AND a.id =d.product_id

AND e.product_id(+) = a.id

AND a.variance_id =f.id(+)

ORDER BY

a.last_count_order ASC

)

WHERE

rownum <= 300;

[/code]对应的执行计划：

Plan hash value: 1304873736

-------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 300 | 624K| 6207 (1)| 00:01:15 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | VIEW | | 613 | 1275K| 6207 (1)| 00:01:15 |

|* 3 | SORT ORDER BY STOPKEY | | 613 | 373K| 6207 (1)| 00:01:15 |

|* 4 | HASH JOIN OUTER | | 613 | 373K| 6206 (1)| 00:01:15 |

| 5 | NESTED LOOPS OUTER | | 613 | 366K| 6148 (1)| 00:01:14 |

|* 6 | HASH JOIN | | 613 | 352K| 6132 (1)| 00:01:14 |

|* 7 | INDEX FAST FULL SCAN | IDX_PP_PRIC_PRD | 14923 | 174K| 2060 (3)| 00:00:25 |

|* 8 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |

| 9 | NESTED LOOPS | | 9967 | 5616K| 4071 (1)| 00:00:49 |

|* 10 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_TYPE | 225 | 21150 | 96 (2)| 00:00:02 |

| 11 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 12 | BITMAP AND | | | | | |

| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |

|* 14 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I2 | | | 1 (0)| 00:00:01 |

| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |

|* 16 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I1 | | | 26 (0)| 00:00:01 |

|* 17 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |

| 18 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |

|* 19 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |

| 20 | TABLE ACCESS FULL | EML_PRICE | 88782 | 1040K| 57 (4)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------------

可以看到cost 花了6207 是一个比较差的计划

可以看到大部分的性能都消耗在 hash join 上

修改后的执行计划：

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 12786 | 167 (1)| 00:00:03 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | VIEW | | 6 | 12786 | 167 (1)| 00:00:03 |

|* 3 | SORT ORDER BY STOPKEY | | 6 | 3678 | 167 (1)| 00:00:03 |

| 4 | NESTED LOOPS OUTER | | 6 | 3678 | 166 (0)| 00:00:02 |

| 5 | NESTED LOOPS OUTER | | 6 | 3606 | 164 (0)| 00:00:02 |

| 6 | NESTED LOOPS | | 6 | 3468 | 163 (0)| 00:00:02 |

| 7 | NESTED LOOPS | | 94 | 53204 | 46 (0)| 00:00:01 |

|* 8 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_TYPE | 2 | 166 | 11 (0)| 00:00:01 |

| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 10 | BITMAP AND | | | | | |

| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | |

|* 12 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I2 | 26 | | 1 (0)| 00:00:01 |

| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |

|* 14 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I1 | 26 | | 8 (0)| 00:00:01 |

|* 15 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |

|* 16 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |

|* 17 | TABLE ACCESS BY INDEX ROWID | PP_PRICE | 1 | 12 | 2 (0)| 00:00:01 |

|* 18 | INDEX UNIQUE SCAN | PP_PRICE_PK | 1 | | 1 (0)| 00:00:01 |

| 19 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |

|* 20 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |

| 21 | TABLE ACCESS BY INDEX ROWID | EML_PRICE | 1 | 12 | 1 (0)| 00:00:01 |

|* 22 | INDEX UNIQUE SCAN | PK_EML_PRICE | 1 | | 0 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------------

执行cost 从6200 降到了167

这里看到计划从hash join 变为了neted loop

这里还有一个点，当然针对目前这个sql 可能意义不太大，但是针对产品库意义就很大了

那就是sql 代码里大量的常量没有适用绑定变量，导致目前sql版本很多。

对于表关联，如果量表的数据量都比较大，hash join 不是一个好方案，应该尽量安排走neted loop

而如果其中一个表的数据量比较小，hash join 是优于neted loop

这里的计划里，都出现了bitmap convertion to rowid

这个计划是oracle 希望尽可能的利用索引，少回表的一个策略，一般是两个条件都存在索引，这种情况先，我们可以考虑建立组合索引。

bitmap convertion 一般认为不是很好的计划，（这个我不能确定对错，自己决定吧）

bitmap 这个可以考虑参数 _b_tree_bitmap_plans=false

下面列出加了组合索引的执行计划：

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 12786 | 169 (1)| 00:00:03 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | VIEW | | 6 | 12786 | 169 (1)| 00:00:03 |

|* 3 | SORT ORDER BY STOPKEY | | 6 | 3678 | 169 (1)| 00:00:03 |

| 4 | NESTED LOOPS OUTER | | 6 | 3678 | 168 (0)| 00:00:03 |

| 5 | NESTED LOOPS OUTER | | 6 | 3606 | 166 (0)| 00:00:02 |

| 6 | NESTED LOOPS | | 6 | 3468 | 165 (0)| 00:00:02 |

| 7 | NESTED LOOPS | | 94 | 53204 | 48 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_TYPE | 2 | 166 | 13 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN | IDX_PDL_PRD_TYP_STAT_PARID_TYP | 18 | | 1 (0)| 00:00:01 |

|* 10 | TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |

|* 11 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |

|* 12 | TABLE ACCESS BY INDEX ROWID | PP_PRICE | 1 | 12 | 2 (0)| 00:00:01 |

|* 13 | INDEX UNIQUE SCAN | PP_PRICE_PK | 1 | | 1 (0)| 00:00:01 |

| 14 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |

|* 15 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |

| 16 | TABLE ACCESS BY INDEX ROWID | EML_PRICE | 1 | 12 | 1 (0)| 00:00:01 |

|* 17 | INDEX UNIQUE SCAN | PK_EML_PRICE | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------------------

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

转载于:http://blog.itpub.net/133735/viewspace-703722/

你可能感兴趣的文章

Hive语句是如何转化成MapReduce任务的

查看>>

Hive创建table报错：Permission denied: user=lenovo, access=WRITE, inode="":suh:supergroup:rwxr-xr-x

查看>>

Hive执行job时return code 2排查

查看>>

hive常用函数及数据结构介绍

查看>>

Hive面试题干货（亲自跟着做了好几遍，会了的话对面试大有好处）

查看>>

力扣题解-230. 二叉搜索树中第K小的元素（递归方法，中序遍历解决）

查看>>

力扣题解-123. 买卖股票的最佳时机 III（动态规划）

查看>>

Django 源码阅读：服务启动(wsgi)

C++ 线程同步之临界区CRITICAL_SECTION