关于ORACLE的GROUP BY基数的探索

最新推荐文章于 2024-09-09 18:05:38 发布

好难取名

最新推荐文章于 2024-09-09 18:05:38 发布

阅读量436

点赞数

CC 4.0 BY-SA版权

文章标签： ORACLE 基数

本文链接：https://blog.youkuaiyun.com/xg_87/article/details/53909716

本文介绍了一种用于Oracle数据库中SQL基数计算的方法，并通过具体示例展示了如何使用该公式来预测查询结果集大小，从而优化执行计划。此外，还详细分析了一个复杂的SQL查询及其执行计划。

oracle版本11.2.0.4.0 - 64bit

基数对于SQL的写法和优化都有重要的作用，其决定这执行计划的准确性。了解原理对mysql类的自己改造数据库系统也有借鉴作用。

公式：SELECT ROUND(1865227/16625105*371,0)*1340/sqrt(2)ascardinalityFROM DUAL;

结果：39795.9696451789

解释： 1865227 MID_SAP_REAL_DATA带条件全表扫描预计返回记录数（计算公式本次省略）

16625105 MID_SAP_REAL_DATA全表统计记录数

371 MID_SAP_REAL_DATA.TRANSACTION_DATE列唯一值个数

1340 MID_SAP_REAL_DATA.WW012列唯一值个数

sqrt(2) 经试验得出的固定参数

分析的sql：

SELECT/*+PARALLEL(1)*/

TP.TRANSACTION_DATE,

TP.WW012,

SUM(VV920_FGL_DR)AS VV920_FGL_DR,

SUM(VV100_FGL_DR)AS VV100_FGL_DR

FROM (

SELECT AA.*, V_KVR.REPORTCHDESC, B.CATEGORY_NAME

FROM MID_SAP_REAL_DATA AA,

V_KUNNR_VKORG_REPORTCHDESC V_KVR,

T_DIM_PRO_TCL_CN B

WHERE AA.KNDNR = V_KVR.KUNNR(+)

AND AA.VKORG = V_KVR.VKORG(+)

AND AA.ARTNR = B.PRODUCT_ID(+)

ANDNVL(AA.KNDNR,'0') NOTIN ('M1100816','PC1013')

AND AA.TRANSACTION_DATE >=

TRUNC(ADD_MONTHS(TO_DATE('20161212','yyyymmdd'), -1),

'MM')

) TP

WHERE TP.artnrisnull

OR TP.CATEGORY_NAME ='彩电'

GROUPBY TP.TRANSACTION_DATE,

TP.WW012

执行计划如下：

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

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

| 0 | SELECTSTATEMENT | | 39796 | 3621436 | 62251 | 00:12:28 |

| 1 | HASHGROUPBY | | 39796 | 3621436 | 62251 | 00:12:28 |

| * 2 | HASH JOINRIGHT OUTER | | 1675978 | 152513998 | 49211 | 00:09:51 |

| 3| VIEW | V_KUNNR_VKORG_REPORTCHDESC | 89652 | 1434432 | 1226 | 00:00:15 |

| * 4| HASH JOIN RIGHT OUTER | | 89652 | 5917032 | 1226 | 00:00:15 |

| * 5| VIEW | | 22 | 440| 4 | 00:00:01 |

| * 6| WINDOW SORT PUSHED RANK| | 22 | 264| 4 | 00:00:01 |

| 7| TABLE ACCESSFULL | UI_CHANNEL_MAPPING_FOR_REPORT| 22 | 264| 3 | 00:00:01 |

| 8| VIEW | | 89652 | 4123992 | 1221 | 00:00:15 |

| 9| HASHUNIQUE | | 89652 | 1703388 | 1221 | 00:00:15 |

| 10| TABLE ACCESSFULL |MID_SAP_ZSSD_KNA1 | 89652 | 1703388 | 687 | 00:00:09 |

| * 11 | FILTER | | | | | |

| * 12 | HASH JOIN RIGHT OUTER | | 1675978 | 125698350 | 40939 | 00:08:12 |

| 13| INDEX FAST FULLSCAN |INDEX_DPTC_PC | 10134 | 212814 | 14 |00:00:01 |

| 14| PARTITION RANGE ITERATOR| | 1865227 | 100722258 | 40908 | 00:08:11 |

| * 15| TABLE ACCESSFULL |MID_SAP_REAL_DATA | 1865227 | 100722258 | 40908 | 00:08:11 |

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

Predicate Information (identified byoperation id):

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

* 2 -access("AA"."KNDNR"="V_KVR"."KUNNR"(+)AND "AA"."VKORG"="V_KVR"."VKORG"(+))

* 4 -access("CTE_A"."KVGR2"="TMP_E"."SAPCHID"(+))

* 5 -filter("TMP_E"."ROW_NUMBER"(+)=1)

* 6 - filter(ROW_NUMBER() OVER (PARTITION BY "E"."SAPCHID" ORDER BYINTERNAL_FUNCTION("E"."UPDATE_DATE") DESC )<=1)

* 11 -filter("AA"."ARTNR" IS NULL OR "B"."CATEGORY_NAME"='彩电')

* 12 -access("AA"."ARTNR"="B"."PRODUCT_ID"(+))

* 15 -filter("AA"."TRANSACTION_DATE">=TO_DATE(' 2016-11-0100:00:00', 'syyyy-mm-dd hh24:mi:ss') ANDNVL("AA"."KNDNR",'0')<>'M1100816' ANDNVL("AA"."KNDNR",'0')<>'PC1013')